Solved: Join all dates to table - Qlik Community

Anonymous · ‎2017-01-21

Colleagues, good day!

I have one simple table (Main) with such fields:

ID	Product	Flag	Date	Sales
1	A	1	01.12.2016	43
1	A	1	03.12.2016	54
1	A	1	04.12.2016	3
1	B	1	05.12.2016	4
1	B	1	06.12.2016	5
2	A	1	01.12.2016	43
2	A	1	03.12.2016	54
2	A	1	04.12.2016	3
2	B	1	05.12.2016	4
2	B	1	06.12.2016	5

Also i have directory with calendar, which contain Date field:

Date
01.12.2016
02.12.2016
03.12.2016
04.12.2016
05.12.2016
06.12.2016
07.12.2016

I need to join all dates from directory to every ID, Product, and Flag, in result my result table must have such view:

ID	Product	Flag	Date	Sales
1	A	1	01.12.2016	43
1	A	1	02.12.2016	-
1	A	1	03.12.2016	54
1	A	1	04.12.2016	3
1	A	1	05.12.2016	-
1	A	1	06.12.2016	-
1	A	1	07.12.2016	-
1	B	1	01.12.2016	-
1	B	1	02.12.2016	-
1	B	1	03.12.2016	-
1	B	1	04.12.2016	-
1	B	1	05.12.2016	4
1	B	1	06.12.2016	5
1	B	1	07.12.2016	-
2	A	1	01.12.2016	43
2	A	1	02.12.2016	-
2	A	1	03.12.2016	54
2	A	1	04.12.2016	3
2	A	1	05.12.2016	-
2	A	1	06.12.2016	-
2	A	1	07.12.2016	-
2	B	1	01.12.2016	-
2	B	1	02.12.2016	-
2	B	1	03.12.2016	-
2	B	1	04.12.2016	-
2	B	1	05.12.2016	4
2	B	1	06.12.2016	5

In attachment test files.

many thanks for help!

girirajsinh · ‎2017-01-21

LOAD ID,

     Product,

     Flag,

   // Date as D_Key,

    // Sales,

     'C' as C_Key

FROM

Dates.xlsx

(ooxml, embedded labels, table is Main);

join

LOAD Date as D_Key,

'C' as C_Key

FROM

Dates.xlsx

(ooxml, embedded labels, table is Dates);

Main:

LOAD ID,

     Product,

     Flag,

     D_Key

Resident subtable;

Left Join

LOAD ID,

     Product,

     Flag,

     Date as D_Key,

     Sales



FROM

Dates.xlsx

(ooxml, embedded labels, table is Main);

Drop Table subtable;

View solution in original post

girirajsinh · ‎2017-01-21

LOAD ID,

     Product,

     Flag,

   // Date as D_Key,

    // Sales,

     'C' as C_Key

FROM

Dates.xlsx

(ooxml, embedded labels, table is Main);

join

LOAD Date as D_Key,

'C' as C_Key

FROM

Dates.xlsx

(ooxml, embedded labels, table is Dates);

Main:

LOAD ID,

     Product,

     Flag,

     D_Key

Resident subtable;

Left Join

LOAD ID,

     Product,

     Flag,

     Date as D_Key,

     Sales



FROM

Dates.xlsx

(ooxml, embedded labels, table is Main);

Drop Table subtable;

girirajsinh · ‎2017-01-21

qvw here

Anonymous · ‎2017-01-21

Thanks!

vinieme12 · ‎2017-01-21

just one thing that you will need to correct is that the join in the Main table is done basis only on the Date which is not reliable you might want to create a composite key like; ID&Product&Date as KEY instead of only joining on the date

ALL_Products:

Load distinct Product,

ID,

Flag

FROM

Main;

join

ALL_DATES:

Load Date

FROM

Dates;

NoConcatenate

FACT:

Load *,ID&Product&Date as KEY

RESIDENT ALL_Products;

Drop Table ALL_Products;

Left Join (FACT)

LOAD ID&Product&Date as KEY,

Sales

FROM

Main;

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.