Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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;
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;
qvw here
Thanks!
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;