Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
jamesmf1982
Contributor III
Contributor III

Counting null and specific values together

Hi,

I am putting together an expression at the moment which the number of visits in a table where a field (in this case VisitVariationReason) is set to either null or not 'Entry Error' between a date range.

I have the following:

count( {<VisitStartDate={">=$(=$(v_date_min))<=$(=date($(v_date_min)+28))"}> } VisitCounter)

Which counts all the visits fine (including null and non null VisitVariationReason entries)

NB: VisitStartDate is a date field and v_date_min is the selection from calendar.


However when I add the following

count( {<VisitStartDate={">=$(=$(v_date_min))<=$(=date($(v_date_min)+28))"}, VisitVariationReason -= {'Entry Error'}> } VisitCounter)


It returns any reason with a variation reason that doesn't equal Entry Error but doesn't return any null entries.


How would I put something together to return both? If it's easier to do it as an if statement I am happy to do that.


Thanks


James

1 Solution

Accepted Solutions
sunny_talwar

May be create a flag in the script

If(VisitVariationReason <> 'Entry Error', 1, 0) as Flag


and then use this

Count( {<VisitStartDate={">=$(=$(v_date_min))<=$(=date($(v_date_min)+28))"}, Flag = {'1'}>} VisitCounter)

View solution in original post

5 Replies
sunny_talwar

May be create a flag in the script

If(VisitVariationReason <> 'Entry Error', 1, 0) as Flag


and then use this

Count( {<VisitStartDate={">=$(=$(v_date_min))<=$(=date($(v_date_min)+28))"}, Flag = {'1'}>} VisitCounter)

jamesmf1982
Contributor III
Contributor III
Author

Hi Sunny,

Thanks will try this. When adding it in do I need to use a LOAD function before the if statement?

sunny_talwar

Whereever you have the field VisitVariationReason, just add this to the same load

TableName:

LOAD VisitVariationReason,

     If(VisitVariationReason <> 'Entry Error', 1, 0) as Flag,

     OtherFields

FROM ...;

jamesmf1982
Contributor III
Contributor III
Author

Many thanks Sunny!

sunny_talwar

No problem at all