topic Re: Count if 0 in QlikView

Count if 0

Anonymous — Wed, 21 Sep 2016 09:32:11 GMT

Hi all,

i have the following expression:

Datenload:

LOAD

Alter2,

count(Proben_ID) as Anzahl_Warteschlange

Resident Datenload_Datum Group by Alter2;

How can I get also counts with 0?

Thank you for your help!

Robert

Re: Count if 0

Gysbert_Wassenaar — Wed, 21 Sep 2016 09:37:24 GMT

Temp:

LOAD

Alter2,

count(Proben_ID) as Anzahl_Warteschlange

Resident

Datenload_Datum

Group by

Alter2

;

RIGHT JOIN (Temp)

LOAD FieldValue('Alter2', RecNo()) as Alter2 Autogenerate FieldValueCount('Alter2');

Result:

LOAD Alter2, Alt(Anzahl_Warteschlange,0) as Anzahl_Warteschlange Resident Temp;

DROP Table Temp;

Re: Count if 0

Digvijay_Singh — Wed, 21 Sep 2016 09:39:02 GMT

What do you mean by counts with 0, can you further elaborate or pl share sample with output expected.

Re: Count if 0

sunny_talwar — Wed, 21 Sep 2016 10:39:38 GMT

Gysbert why are we doing a Right Join here? I was trying to understand the code, but not sure I understand the Right Join

Re: Count if 0

Gysbert_Wassenaar — Wed, 21 Sep 2016 10:45:23 GMT

I've edited the code a bit. It was nonsense as posted. It doesn't make sense to do a right join unless there are Alter2 values in another table that wasn't mentioned in the original post. My edited code assumes this is the case. If that assumption is false then all the zero counts are already in the originally posted Datenload table.

Re: Count if 0

MarcoWedel — Thu, 22 Sep 2016 22:05:43 GMT

Hi gwassenaar,

very nice, never saw this way to add foreign field values to a table.

Do you think an 'Alter2' field alias is necessary in the right join load?

RIGHT JOIN (Temp)

LOAD FieldValue('Alter2', RecNo()) as Alter2 Autogenerate FieldValueCount('Alter2');

thanks

regards

Marco

Re: Count if 0

Gysbert_Wassenaar — Fri, 23 Sep 2016 05:24:42 GMT

Yes, you're absolutely right. Thanks!