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
 286 Workbench
 4 Cloud Amplifier
 5 Federated
 2.7K Transform
 91 SQL DataFlows
 579 Datasets
 2.1K Magic ETL
 3.5K Visualize
 2.4K Charting
 622 Beast Mode
 27 App Studio
 33 Variables
 611 Automate
 155 Apps
 422 APIs & Domo Developer
 32 Workflows
 2 DomoAI
 29 Predict
 12 Jupyter Workspaces
 17 R & Python Tiles
 373 Distribute
 104 Domo Everywhere
 266 Scheduled Reports
 3 Software Integrations
 100 Manage
 97 Governance & Security
 4 Domo University
 15 Product Releases
 Community Forums
 37 Getting Started
 28 Community Member Introductions
 91 Community Announcements
 4.8K Archive