Is Not Null - Frustration!

[SonnyDelete]

Dear Forum,

I'm running around in circles trying apply a 'Is Not Null' clause in to the following...

Count({$<[Month-Year]=,[Day-Month]=,Date={">=$(=Min(Date))<=$(=Max(Date))"},[Field1]={"Late"}>} [Job Number])

Can anyone put me out of my misery and tell me why this isn't working...

Count({$<[Month-Year]=,[Day-Month]=,Date={">=$(=Min(Date))<=$(=Max(Date))"},[Field1]={"Late"},Len(Trim([Field2]))>0>} [Job Number])

Thanks.

[ToniKautto · Accepted solution]

The second expression has an invalid syntax. Set expression is used to define a selection in a specific state of your data. The syntax simple defines how to alter the selection before the aggregation is processed. 
Len(Trim([Field2]))>0 does not define a field selection, it is an aggregation and a comparison. What you need to do is find a way to select the records you are interested in. 

A simple approach is to generate a flag during data model load. In this case it will create a flag with -1 (true) or 0 (false). For example as;
Len(Trim([Field2]))>0 AS FlgField2HasContent

Then you can easily include the "non empty" values in your aggregation with;
Count({$<[Month-Year]=,[Day-Month]=,Date={">=$(=Min(Date))<=$(=Max(Date))"},[Field1]={"Late"},FlgField2HasContent={"-1"}>} [Job Number])

An other option is that 

[rogerpegler]

Tko's approach is the best one to give you the most flexibility.   

However a quick fix could be to have Field2={"*"} in your set analysis which will match anything and by implication not match nulls.

[SonnyDelete]

Thanks Tko, Rogerpegler,

I couldn’t work out why it still wouldn’t work. It suddenly dawned on me that I using the Len(Trim([Field2]))>0 expression on a column of data which was populated based on a left join, so I was only getting -1 values not 0 (it was the 0s I was interested in).

All I had to do was use the original table and now things are working as expected.

Thanks for your input.

Regards,
Sonny

[ToniKautto]

Just to re-emphasize, your current expression is not valid. If you get the rest you expect with this expression...

Count({$<[Month-Year]=,[Day-Month]=,Date={">=$(=Min(Date))<=$(=Max(Date))"},[Field1]={"Late"},Len(Trim([Field2]))>0>} [Job Number])

... then I expect that you get the same results with below expression. Essentially this could mean that you other applied selections in Date and Field1 indirectly limits the data to the "non null value" items .

Count({$<[Month-Year]=,[Day-Month]=,Date={">=$(=Min(Date))<=$(=Max(Date))"},[Field1]={"Late"}>} [Job Number])

[pradosh_thakur]

I Think you should go with @ToniKautto solution of creating a flag in the data model then use that in set analysis. An alternate solution would be

Count({$<[Month-Year]=,[Day-Month]=,Date={">=$(=Min(Date))<=$(=Max(Date))"},[Field1]={"Late"},[Field2]={"=len(trim([Field2]))>0"} >} [Job Number])

[ToniKautto]

I don't think that will work as a solution. The expectation would be that len() is evaluated on a row level, while the set expression is evaluated only once. Field2 would most likely resolve as null, making Len() always return 0.
Inline with https://community.qlik.com/t5/QlikView-Documents/Set-expression-and-dollar-expansion/ta-p/1484227

[jyotiwagde]

Try this

Count({$<[Month-Year]=,[Day-Month]=,Date={">=$(=Min(Date))<=$(=Max(Date))"},[Field1]={"Late"},[Field2]-={""}>} [Job Number])

or

If(not IsNull([Field2]),
Count({$<[Month-Year]=,[Day-Month]=,Date={">=$(=Min(Date))<=$(=Max(Date))"},[Field1]={"Late"}>} [Job Number]),0)

[SonnyDelete]

Thanks again for all your feedback. Tko’s suggestion of calculating a flag during the data load is giving me accurate counts, so I’m a satisfied customer!