Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

robert2012
New Contributor III

Count if 0

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

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Count if 0

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;


talk is cheap, supply exceeds demand
6 Replies
MVP & Luminary
MVP & Luminary

Re: Count if 0

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;


talk is cheap, supply exceeds demand
Digvijay_Singh
Honored Contributor III

Re: Count if 0

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

Re: Count if 0

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

MVP & Luminary
MVP & Luminary

Re: Count if 0

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.


talk is cheap, supply exceeds demand

Re: Count if 0

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

MVP & Luminary
MVP & Luminary

Re: Count if 0

Yes, you're absolutely right. Thanks!


talk is cheap, supply exceeds demand