Difference between counts in same table

[quilldew]

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.

[tamilarasu · Accepted solution]

Hi Daemon,

Maybe this?

Left join(Throughput)

Load DischargeDate as RefDate,

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

Resident Temp

group by DischargeDate;

[quilldew]

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?

[tamilarasu]

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;

[tamilarasu]

Have a good day Daemon!!

[quilldew]

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