lookup function in beastmode?
Hi all,
I have a table that has these columns:
loan number
other loan number
first principle balance
hi type description
2nd mortgage amount is the field we are trying to create
The logic is such:
when hi type description
= '1st mortgage' and other loan number
is > 1 then i want to look up the first principle balance
of the other loan number
matching it to its corresponding loan number
here is an example
on the top row, the first principle balance is 24,573.66 and the created column is 2nd mortgage amount
which in this case is a formula. the amount there shows 543.8
That 543.8 came from the first principle balance
row (that is luckily right) below.
Essentially I am using the other loan number
(0009667860) and finding it in the loan number
and pulling the first principle balance
of that line to populate the 2nd mortgage amount
Can someone help me with a beastmode? I would rather not have to run this through a mysql or an etl but if those are the only ways to do it, i can.
Is a beastmode possible?
Thanks
If this helps, feel free to agree, accept or awesome it!
Best Answers
-
I would agree this would be better to with Magic ETL, and give you more options for bringing in additional "other loan" columns.
That said, here's a possible BeastMode approach that addresses this specific scenario:
RESULT:
CONCAT NUMBER:
case
whenLoan Number
<Other Loan Number
then concat(Loan Number
,'-',Other Loan Number
)
else concat(Other Loan Number
, '-',Loan Number
)
ENDOTHER PRINCIPAL BALANCE:
case
whenConcat number
is not null
then avg(avg(Balance
) fixed(byConcat number
))*2 -Balance
endEXPLANATION:
- I simplified the Loan Numbers in your example.
- I created a new ID that concats the pairs of loan numbers into a single ID for that pair. I check for which is greater to make sure they concat in the same way for both halves of the pair.
- I found the fixed average for the principal for that pair. For example, 1 - 2 would be (24573.66 + 543.80)/2
- Did some basic algebra to solve for the other balance: *2 - Balance
ASSUMPTIONS:
- Loan numbers react consistently to < and > symbols, allowing for a consistent CONCAT
- Loan numbers come in pairs, not in longer chains.
Please 💡/💖/👍/😊 this post if you read it and found it helpful.
Please accept the answer if it solved your problem.
0
Answers
-
I would agree this would be better to with Magic ETL, and give you more options for bringing in additional "other loan" columns.
That said, here's a possible BeastMode approach that addresses this specific scenario:
RESULT:
CONCAT NUMBER:
case
whenLoan Number
<Other Loan Number
then concat(Loan Number
,'-',Other Loan Number
)
else concat(Other Loan Number
, '-',Loan Number
)
ENDOTHER PRINCIPAL BALANCE:
case
whenConcat number
is not null
then avg(avg(Balance
) fixed(byConcat number
))*2 -Balance
endEXPLANATION:
- I simplified the Loan Numbers in your example.
- I created a new ID that concats the pairs of loan numbers into a single ID for that pair. I check for which is greater to make sure they concat in the same way for both halves of the pair.
- I found the fixed average for the principal for that pair. For example, 1 - 2 would be (24573.66 + 543.80)/2
- Did some basic algebra to solve for the other balance: *2 - Balance
ASSUMPTIONS:
- Loan numbers react consistently to < and > symbols, allowing for a consistent CONCAT
- Loan numbers come in pairs, not in longer chains.
Please 💡/💖/👍/😊 this post if you read it and found it helpful.
Please accept the answer if it solved your problem.
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 297 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 616 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 54 App Studio
- 40 Variables
- 678 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 46 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive