Discussion board where members can get started with QlikView.
I'm including some sample code to outline my problem.
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
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, 1, Safe
Dec 2016, Apr 2017, 1, Admin Error
Apr 2017, May 2017, 0, New
Load ReceivedDate as RefDate,
count(Vet) as RecAmount
group by ReceivedDate;
Load DischargeDate as RefDate,
count(Vet) as DisAmount
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:
if(Reason=-'Admin Error',count(Vet)) as DisAmount
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.
Count(if(Reason <> 'Admin Error',Vet)) as DisAmount
View solution in original post
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?
I guess this should give the same value as earlier.
Count(Vet) as DisAmount
Resident Temp Where Reason <> 'Admin Error'
Have a good day Daemon!!
Thank you. I tried your second suggestion as well and that also works. Easier to remember that one too. Many Thanks for your help.