Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
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
Valued Contributor

Re: Join a table for each GROUP BY

TEMP:

LOAD DISTINCT Keypart

RESIDENT MonthlyStockFinal;

JOIN (TEMP)

LOAD Month

RESIDENT AllMonths;

JOIN (MonthlyStockFinal)

LOAD KeyPart, Month

RESIDENT TEMP;

DROP TABLE TEMP;

3 Replies
MVP & Luminary
MVP & Luminary

Re: Join a table for each GROUP BY

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
Valued Contributor

Re: Join a table for each GROUP BY

TEMP:

LOAD DISTINCT Keypart

RESIDENT MonthlyStockFinal;

JOIN (TEMP)

LOAD Month

RESIDENT AllMonths;

JOIN (MonthlyStockFinal)

LOAD KeyPart, Month

RESIDENT TEMP;

DROP TABLE TEMP;

Not applicable

Re: Join a table for each GROUP BY

This worked for me, thank you very much!