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
    Tim Smith Mar 27, 2018 8:18 PM

      Hi All,

       

      I have two tables:
      [Invoice]:

      Load

           'Invoice Number',

           'Product Key'

      FROM qvd

       

      and

       

      [Product Table]:

      Load

           'Product Key',

           'Class'

      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