Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
SonnyDelete
Contributor
Contributor

Is Not Null - Frustration!

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.

1 Solution

Accepted Solutions
ToniKautto
Employee
Employee

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 

 

View solution in original post

8 Replies
ToniKautto
Employee
Employee

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
Creator II
Creator II

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
Contributor
Contributor
Author

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
Employee
Employee

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
Master II
Master II

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])

Learning never stops.
ToniKautto
Employee
Employee

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
Partner - Contributor
Partner - Contributor

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
Contributor
Contributor
Author

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!