cancel
Showing results for 
Search instead for 
Did you mean: 

"Where" Function

JosephGalvin
7 - Data Storage
7 - Data Storage

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.

3 REPLIES 3

Ophir_Buchman
12 - Data Integration
12 - Data Integration

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
7 - Data Storage
7 - Data Storage

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
12 - Data Integration
12 - Data Integration

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