Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
What do you mean by counts with 0, can you further elaborate or pl share sample with output expected.
Gysbert why are we doing a Right Join here? I was trying to understand the code, but not sure I understand the Right Join
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.
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
Yes, you're absolutely right. Thanks!