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.5K Product Ideas
 1.5K Ideas Exchange
 1.4K Connect
 1.1K Connectors
 283 Workbench
 4 Cloud Amplifier
 4 Federated
 2.7K Transform
 90 SQL DataFlows
 566 Datasets
 2K Magic ETL
 3.4K Visualize
 2.3K Charting
 595 Beast Mode
 13 App Studio
 28 Variables
 593 Automate
 147 Apps
 417 APIs & Domo Developer
 28 Workflows
 1 DomoAI
 28 Predict
 12 Jupyter Workspaces
 16 R & Python Tiles
 362 Distribute
 99 Domo Everywhere
 261 Scheduled Reports
 2 Software Integrations
 96 Manage
 93 Governance & Security
 14 Product Releases
 Community Forums
 37 Getting Started
 28 Community Member Introductions
 90 Community Announcements
 4.8K Archive