Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
Highlighted
quilldew
Contributor

Difference between counts in same table

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.

1 Solution

Accepted Solutions
Highlighted

Re: Difference between counts in same table

Hi Daemon,

Maybe this?

Left join(Throughput)

Load DischargeDate as RefDate,

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

Resident Temp

group by DischargeDate;

View solution in original post

5 Replies
Highlighted

Re: Difference between counts in same table

Hi Daemon,

Maybe this?

Left join(Throughput)

Load DischargeDate as RefDate,

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

Resident Temp

group by DischargeDate;

View solution in original post

Highlighted
quilldew
Contributor

Re: Difference between counts in same table

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?

Highlighted

Re: Difference between counts in same table

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;

Highlighted

Re: Difference between counts in same table

Have a good day Daemon!!

Highlighted
quilldew
Contributor

Re: Difference between counts in same table

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