topic Counting null and specific values together in QlikView

Counting null and specific values together

jamesmf1982 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Counting null and specific values together

sunny_talwar — Wed, 09 May 2018 09:14:51 GMT

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)

Re: Counting null and specific values together

jamesmf1982 — Wed, 09 May 2018 10:06:24 GMT

Hi Sunny,

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

Re: Counting null and specific values together

sunny_talwar — Wed, 09 May 2018 11:13:11 GMT

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 ...;

Re: Counting null and specific values together

jamesmf1982 — Wed, 09 May 2018 11:26:24 GMT

Many thanks Sunny!

Re: Counting null and specific values together

sunny_talwar — Wed, 09 May 2018 11:27:12 GMT

No problem at all