How can I write a custom Kusto function that will perform a lookup for me?

Question

I have a series of tables in Kusto with securities trade data that we're constantly querying. We often like to look through to the underlying investment if we're trading a derivative. How can I write a function that will always return the correct underlying? We have a "SecMaster" table with aa column: Code that will always be a 1:1 with the object we're looking up the underlying for. There's another column "recordclass" that will drive the column that we're pulling in. So:

If RecordClass == "SwapInvestment" then return "InvestmentIn"
If RecordClass in ("Warrant","Option","Future") then return "UnderlyingInvestment".

Here's what I've tried so far with no luck:

.create-or-alter function Risk_Ticker(SecId: string )
{
    let MyTable =
    database('test').ETLN_SecMaster
    |where FileDateTime == toscalar(database('test').ETLN_SecMaster| summarize max(FileDateTime))
    | extend MappedUnderlying = case(RecordClass == "SwapInvestment",InvestmentIn,
                                        RecordClass in ("Warrant","Option","Future"),UnderlyingInv,
                                        Code
                                    )
    |project MappedUnderlying;
print toscalar(MyTable) 
}

Answer 1

I tried your query and the result depends on the type of your values in the case condition.

This is my sample data:

The above code worked for me without any error when I gave the values of same type in case condition.

Output without toscalar();

Output of the function:

Here, toscalar() gives the first row of the data.

How can I write a custom Kusto function that will perform a lookup for me?

If you remove the toscalar() and add MyTable, it will give a lookup result.