Discussion Board for collaboration related to QlikView App Development.
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 |
TEMP:
LOAD DISTINCT Keypart
RESIDENT MonthlyStockFinal
;
JOIN (TEMP)
LOAD Month
RESIDENT AllMonths;
JOIN (MonthlyStockFinal
)
LOAD KeyPart, Month
RESIDENT TEMP;
DROP TABLE TEMP;
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
TEMP:
LOAD DISTINCT Keypart
RESIDENT MonthlyStockFinal
;
JOIN (TEMP)
LOAD Month
RESIDENT AllMonths;
JOIN (MonthlyStockFinal
)
LOAD KeyPart, Month
RESIDENT TEMP;
DROP TABLE TEMP;
This worked for me, thank you very much!