Count if 0

[Anonymous]

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

[Gysbert_Wassenaar · Accepted solution]

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;

[Digvijay_Singh]

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

[sunny_talwar]

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

[Gysbert_Wassenaar]

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.

[MarcoWedel]

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

[Gysbert_Wassenaar]

Yes, you're absolutely right. Thanks!