Join a table for each GROUP BY

[#1]

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:

KeyPart	Month	Voorraad
ExamplePart1	01-02-2013	21
ExamplePart1	01-03-2013	14
ExamplePart1	01-04-2013	12
ExamplePart1	01-05-2013	77
ExamplePart1	01-07-2013	70
ExamplePart2	01-02-2013	33
ExamplePart2	01-03-2013	27
ExamplePart2	01-04-2013	25
ExamplePart2	01-07-2013	20

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:

KeyPart	Month	Voorraad
ExamplePart1	01-01-2013	-
ExamplePart1	01-02-2013	21
ExamplePart1	01-03-2013	14
ExamplePart1	01-04-2013	12
ExamplePart1	01-05-2013	77
ExamplePart1	01-06-2013	-
ExamplePart1	01-07-2013	70
ExamplePart2	01-01-2013	-
ExamplePart2	01-02-2013	33
ExamplePart2	01-03-2013	27
ExamplePart2	01-04-2013	25
ExamplePart2	01-05-2013	-
ExamplePart2	01-06-2013	-
ExamplePart2	01-07-2013	20

[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

[luciancotea · Accepted solution]

TEMP:

LOAD DISTINCT Keypart

RESIDENT MonthlyStockFinal;

JOIN (TEMP)

LOAD Month

RESIDENT AllMonths;

JOIN (MonthlyStockFinal)

LOAD KeyPart, Month

RESIDENT TEMP;

DROP TABLE TEMP;

[#4]

This worked for me, thank you very much!