lookup function in beastmode?

damen

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

DomoDork

Hi @damen,

Since beast modes are realtime evaluations of data, lookups aren't really something a beast mode would support. Instead I would use a MagicETL to do your 'lookup' logic and output that 2nd mortgage amount as an additional column on your dataset.

DavidChurchman

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
when Loan Number < Other Loan Numberthen concat(Loan Number,'-', Other Loan Number)
else concat(Other Loan Number, '-', Loan Number)
END

OTHER PRINCIPAL BALANCE:

case
when Concat number is not null
then avg(avg(Balance) fixed(by Concat number))*2 - Balance
end

EXPLANATION:

• 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.

DomoDork

Hi @damen,

Since beast modes are realtime evaluations of data, lookups aren't really something a beast mode would support. Instead I would use a MagicETL to do your 'lookup' logic and output that 2nd mortgage amount as an additional column on your dataset.

DavidChurchman

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
when Loan Number < Other Loan Numberthen concat(Loan Number,'-', Other Loan Number)
else concat(Other Loan Number, '-', Loan Number)
END

OTHER PRINCIPAL BALANCE:

case
when Concat number is not null
then avg(avg(Balance) fixed(by Concat number))*2 - Balance
end

EXPLANATION:

• 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.