topic Re: Join all dates to table in QlikView

Join all dates to table

Anonymous — Sat, 21 Jan 2017 12:21:04 GMT

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!

Re: Join all dates to table

girirajsinh — Sat, 21 Jan 2017 12:54:30 GMT

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;

Re: Join all dates to table

girirajsinh — Sat, 21 Jan 2017 12:55:58 GMT

qvw here

Re: Join all dates to table

Anonymous — Sat, 21 Jan 2017 13:56:55 GMT

Thanks!

Re: Join all dates to table

vinieme12 — Sat, 21 Jan 2017 14:23:01 GMT

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;