IF Statement and Set Analysis

[#1]

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.

[Miguel_Angel_Baeyens · Accepted solution]

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

[#3]

Thank you Miguel. I will try to the load script. I think that will be the way to go too. I can then use those flags with the other summary steps I need to perform.

Thank you for your quick response too! I really appreciate it.

Karen