Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Join a table for each GROUP BY

Hello,

I have the following code for displaying stock results which displays the result of the latest stock mutation of that month. This looks like this:

[MonthlyStockTEMP]:

LOAD

          [TempMonth]                                                  as [Month],

          [KeyPart],

          FirstSortedValue([PartStockEnd], -[Date])           as [Voorraad]

Resident [FinalStock]

Group By [TempMonth], [KeyPart];

[MonthlyStockFinal]:

NOCONCATENATE LOAD

          [KeyPart],

          [Month],

          [Voorraad]

Resident [MonthlyStockTEMP];

DROP TABLE [MonthlyStockTEMP];

This results in the following table:

KeyPartMonthVoorraad
ExamplePart101-02-201321
ExamplePart101-03-201314
ExamplePart101-04-201312
ExamplePart101-05-201377
ExamplePart101-07-201370
ExamplePart201-02-201333
ExamplePart201-03-201327
ExamplePart201-04-201325
ExamplePart201-07-201320

Cause some of the months are empty, I want to add them manually. I have the following table with months. (first days, just like in the stock table)

[AllMonths]:

01-01-2013
01-02-2013
01-03-2013
01-04-2013
01-05-2013
01-06-2013
01-07-2013
01-08-2013

I want to merge those 2 tables so the keyparts with no value in that specific month at least have an empty month.

How can i do this for each keypart? So it will look like this:

KeyPartMonthVoorraad
ExamplePart101-01-2013-
ExamplePart101-02-201321
ExamplePart101-03-201314
ExamplePart101-04-201312
ExamplePart101-05-201377
ExamplePart101-06-2013-
ExamplePart101-07-201370
ExamplePart201-01-2013-
ExamplePart201-02-201333
ExamplePart201-03-201327
ExamplePart201-04-201325
ExamplePart201-05-2013-
ExamplePart201-06-2013-
ExamplePart201-07-201320
1 Solution

Accepted Solutions
luciancotea
Specialist
Specialist

TEMP:

LOAD DISTINCT Keypart

RESIDENT MonthlyStockFinal;

JOIN (TEMP)

LOAD Month

RESIDENT AllMonths;

JOIN (MonthlyStockFinal)

LOAD KeyPart, Month

RESIDENT TEMP;

DROP TABLE TEMP;

View solution in original post

3 Replies
Gysbert_Wassenaar

After creating MonthlyStockFinal do a right join with the AllMonths table.

[MonthlyStockFinal]:
NOCONCATENATE LOAD
          [KeyPart],
          [Month],
          [Voorraad]
Resident [MonthlyStockTEMP];
DROP TABLE [MonthlyStockTEMP];

right join(MonthlyStockFinal)

LOAD * from AllMonths; //or maybe: resident AllMonths


talk is cheap, supply exceeds demand
luciancotea
Specialist
Specialist

TEMP:

LOAD DISTINCT Keypart

RESIDENT MonthlyStockFinal;

JOIN (TEMP)

LOAD Month

RESIDENT AllMonths;

JOIN (MonthlyStockFinal)

LOAD KeyPart, Month

RESIDENT TEMP;

DROP TABLE TEMP;

Not applicable
Author

This worked for me, thank you very much!