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.6K Product Ideas
 1.6K Ideas Exchange
 1.5K Connect
 1.2K Connectors
 288 Workbench
 4 Cloud Amplifier
 8 Federated
 2.8K Transform
 94 SQL DataFlows
 595 Datasets
 2.1K Magic ETL
 3.6K Visualize
 2.4K Charting
 672 Beast Mode
 42 App Studio
 34 Variables
 645 Automate
 165 Apps
 434 APIs & Domo Developer
 42 Workflows
 4 DomoAI
 31 Predict
 12 Jupyter Workspaces
 19 R & Python Tiles
 378 Distribute
 107 Domo Everywhere
 266 Scheduled Reports
 5 Software Integrations
 111 Manage
 108 Governance & Security
 8 Domo University
 23 Product Releases
 Community Forums
 39 Getting Started
 29 Community Member Introductions
 95 Community Announcements
 4.8K Archive