2 Replies Latest reply: Oct 11, 2013 9:52 AM by Bas van Maren RSS

    IsNull in Set Analysis

    Gerhard Laubscher

      Hi,

       

      I've been looking around at similiar posts but can't figure this out.

       

      I have a file that is a "current snapshot" of each account, called the Account Extract (text file).

       

      I am loading one Account Extract that is current, and one that is a month old, and I do it like this:

       

      current_extract:

      Load

      [Account No],

      [Club Status]

       

      FROM

      [text file];

       

      old_extract:

      LOAD

      [Account No],

      [Club Status] as [Club Status_OLD]

       

      FROM

      [old text file];

       

       

      Now I want to create an expression in a chart that counts the accounts that have a current Club Status of ACTIVE, but that do not exist in the old table (i.e. newly opened accounts that have club membership).

       

      I've tried using IsNull but cannot get this to work...

       

      Regards,

       

      Gerhard

        • Re: IsNull in Set Analysis
          Gysbert Wassenaar

          try {<[Club Status]-={'*'}>} in your set analysis expression. Actually, substituting null/missing values with real selectable values is a better solution. See the NullDisplay and NullValue variables in the help file.

            • Re: IsNull in Set Analysis

              Hi All,

              I also have a question on this subject

               

              the first part of my formula works, With the second part I'm trying to select the oppposite set and sum another column in that case.

               

              for the second part I also tried

               

              Sum(
              {<
               
              Ontvangst_Ontvangstdatum -= {'*'}
              >}
              Inkoopfactuurregel_Aantal
              ))

               

              But that did not work either, does anyone understand what I'm doing wrong?

               

              Sum(
              {<
               
              Ontvangst_Ontvangstdatum = {'*'}
              >}
              Ontvangstregel_Aantal_ontvangen

              )
              )
              +
              (
              Sum(
              {<
               
              Ontvangst_Ontvangstdatum = {''}
              >}
              Inkoopfactuurregel_Aantal
              ))

               

              Thanks for helping