lookup function in beastmode?

damen
damen Contributor
in Beast Mode

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

image.png

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!

Tagged:

Best Answers

  • DomoDork
    DomoDork Contributor
    Answer ✓

    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
    DavidChurchman Coach
    Answer ✓

    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:

    image.png

    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.

    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

Answers

  • DomoDork
    DomoDork Contributor
    Answer ✓

    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
    DavidChurchman Coach
    Answer ✓

    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:

    image.png

    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.

    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

Categories