3 Replies Latest reply: Mar 28, 2018 10:22 AM by Sasidhar Parupudi RSS

    How do I filter for a null/empty field in an expression? IsNull doesn't behave as I'd expect.

    Tim Smith

      Hi All,


      I have two tables:


           'Invoice Number',

           'Product Key'

      FROM qvd




      [Product Table]:


           'Product Key',


      FROM qvd


      If there are some Products Key values in Invoice that do not have corresponding Product Key rows in Product Table, then Class will display as '-' in a table with fields Invoice Number, and Class.


      My end goal is to produce a report that shows me a list of all Product Keys that exist in Invoice, but not in Product Table. I've been trying to go about it with a table that contains Product Key, and Class, and creating a column that is =If(IsNull(Class), 1, 0) expecting a 1 to show if there is no row in Product Table matching the Product Key, however it evaluates to '-' if Class doesn't exist, and 0 if it does, so I'm unable to filter by this value at all.


      Is there a way for me to do this? Or, alternatively, a way for me to set a default value on Class in the data load, so that if Class doesn't exist, displays as "Unmapped" or something?


      Cheers, Tim