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