Forum Discussion

JosephGalvin's avatar
JosephGalvin
Data Storage
05-27-2022

"Where" Function

Can the "where" function be used in a lookup formula to limit the rows looked at to only result in values that are not null or only a specific value or partial value?  I am attempting to use a lookup formula, where the value being used to look up appears more than once in the table.  I am expecting to capture the resulting value only if the resulting value is not null.  Thank you.

Code-first Analytics

3 Replies

Replies have been turned off for this discussion
  • Ophir_Buchman's avatar
    Ophir_Buchman
    Data Integration
    06-02-2022

    Hi JosephGalvin -

    Just making sure you are aware that the lookup function is not a "Left Join" - If multiple rows appear in the resultset - It will only return the first value.

    In this case, I recommend either:

    • If you want all matching results - Use a custom table and a left join
    • If you want one matching result - Add an additional column in the lookup table that would contain the contents of the lookup column only if the lookup result column isn't NULL and use it in the lookup formula
    • Another (very non-optimal solution) - Use a SQL statement in your custom column (using a WHERE statement
  • JosephGalvin's avatar
    JosephGalvin
    Data Storage
    06-02-2022

    Thank you.  Is there reference instructions on how to do this in sisense training?  I have no IT coding background (accountant).  Thank you.

  • Ophir_Buchman's avatar
    Ophir_Buchman
    Data Integration
    06-04-2022

    Hi JosephGalvin 

    You can find relevant content in our Sisense University website (under the "Data Modeler" training module.

     

    You can alternatively contact your CSM to allow a short consultancy session with a technical account manager. 

    Thanks, Ophir