topic Re: Difference between counts in same table in QlikView

Difference between counts in same table

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

Hi,

I'm including some sample code to outline my problem.

[Temp]:

LOAD * INLINE [

ReceivedDate, DischargeDate, Vet, Reason

Dec 2016, Mar 2017, 0, New

Mar 2017, Apr 2017, 1, New

Dec 2016, May 2017, 1, Safe

Dec 2016, May 2017, 1, New

Mar 2017, Apr 2017, 1, New

Dec 2016, Mar 2017, 1, Safe

Apr 2017, June 2017, 1, Safe

Jan 2017, Mar 2017, 0, New

Dec 2016, Apr 2017, 1, New

Apr 2017, May 2017, 1, New

Jan 2017, Mar 2017, 0, New

Mar 2017, Apr 2017, 1, New

Dec 2016, May 2017, 1, Safe

Dec 2016, May 2017, 1, New

Mar 2017, Apr 2017, 1, New

Jan 2017, Mar 2017, 1, Safe

Apr 2017, June 2017, 1, Safe

Jan 2017, Mar 2017, 0, New

Dec 2016, Apr 2017, 1, Admin Error

Apr 2017, May 2017, 0, New

];

[Throughput]:

Load ReceivedDate as RefDate,

count(Vet) as RecAmount

Resident Temp

group by ReceivedDate;

left join(Throughput)

Load DischargeDate as RefDate,

count(Vet) as DisAmount

Resident Temp

group by DischargeDate;

Now this script loads fine if I create a Straight Table using RefDate as a dimension and RecAmount - DisAmount as the expression, it gives me the correct outputs (-2 for both Apr and Mar 2017)

what I would like to do is discount the 'Vet' where the Reason = 'Admin Error'. I can't use set analysis because it's in the load script and using code:

Left join(Throughput)

Load DischargeDate as RefDate,

if(Reason=-'Admin Error',count(Vet)) as DisAmount

Resident Temp

group by Reason, DischargeDate;

gives me the wrong results. It should show -1 for Apr 2017, instead it shows nothing at all for any months. I'm pretty sure it's the group by I'm messing up.

This is a very simple version of the data i'm using, but it shows the problem I am having. I basically want to perform set analysis on an aggregate function in my load script in a roundabout way.

Thanks.

Re: Difference between counts in same table

tamilarasu — Tue, 23 May 2017 14:03:36 GMT

Hi Daemon,

Maybe this?

Left join(Throughput)

Load DischargeDate as RefDate,

Count(if(Reason <> 'Admin Error',Vet)) as DisAmount

Resident Temp

group by DischargeDate;

Re: Difference between counts in same table

quilldew — Tue, 23 May 2017 14:22:05 GMT

OMG it works,

Thank you so much, what a simple answer.

It seems backwards though, wouldn't other languages nest the count within the if?

Re: Difference between counts in same table

tamilarasu — Tue, 23 May 2017 14:33:34 GMT

I guess this should give the same value as earlier.

Left join(Throughput)

Load DischargeDate as RefDate,

Count(Vet) as DisAmount

Resident Temp Where Reason <> 'Admin Error'

group by DischargeDate;

Re: Difference between counts in same table

tamilarasu — Tue, 23 May 2017 14:47:23 GMT

Have a good day Daemon!!

Re: Difference between counts in same table

quilldew — Wed, 24 May 2017 10:38:09 GMT

Thank you. I tried your second suggestion as well and that also works. Easier to remember that one too. Many Thanks for your help.