2 Replies Latest reply: Dec 30, 2010 1:18 PM by klniedenfuer RSS

    IF Statement and Set Analysis

      Hi there -

      I've many posts about IF statements and am trying to understand the Set Analysis.

      What I'm trying to do is:

      Only count records where TOBACCO is not missing (its a numeric field) AND ENCTYPE is not missing (character field)

      My first stab at it was:

      IF(TOBACCO <> . AND ENCTYP <> '',COUNT(TOBACCO)) - but this give me nothing but errors.

      I know this is not a lot of information to go on, but it's a pretty straight forward thing.

      Any help is appreciated!

      Thanks.

        • IF Statement and Set Analysis
          Miguel Angel Baeyens de Arce

          Hello,

          Since missing and null values may not exist (there's not an actual "NULL" value), I'd try the following

           

          Count({1 - < TOBACCO = P(), ENCTYP = P() >} PRODUCTID)


          P() means all possible values for that field, and it may require some different syntax depending on the results. But what I'm trying to count here is all values in field PRODUCTID (that the bold "1") except for those that have "something" in fields TOBACCO and ENCTYP.

          Another option is creating a flag in the script, which is probably a better idea and will return more accurate results. For example

           

          Products:LOAD PRODUCTID, TOBACCO, ENCTYP, IF(LEN(TOBACCO), 0, 1) AS TOBACCO_FLAG, IF(LEN(ENCTYP), 0, 1) AS ENCTYP_FLAG;SQL SELECT * FROM Products;


          Then the expression will look like

           

          Count({< ENCTYP_FLAG = {0}, TOBACCO_FLAG = {0} >} PRODUCTID)


          Hope this helps