Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Good day!
i have test model, with one table:
Main:
LOAD [Distributor _id],
Date,
Volume
FROM
(ooxml, embedded labels, table is Sheet1);
Distributor _id | Date | Volume |
1 | 10.12.2015 | 233 |
1 | 11.12.2015 | 234 |
1 | 12.12.2015 | 235 |
1 | 13.12.2015 | 236 |
1 | 14.12.2015 | 237 |
1 | 15.12.2015 | 238 |
1 | 16.12.2015 | 239 |
1 | 17.12.2015 | 240 |
1 | 18.12.2015 | 241 |
1 | 19.12.2015 | 242 |
1 | 20.12.2015 | 243 |
1 | 21.12.2015 | 244 |
1 | 22.12.2015 | 245 |
2 | 10.12.2015 | 233 |
2 | 11.12.2015 | 234 |
2 | 12.12.2015 | 235 |
2 | 19.12.2015 | 242 |
2 | 20.12.2015 | 243 |
2 | 21.12.2015 | 244 |
2 | 22.12.2015 | 245 |
For this table i join dates from other source:
outer Join(Main)
load
Date
FROM
(ooxml, embedded labels, table is Sheet2);
Result table:
Distributor _id | Date | Volume |
1 | 10.12.2015 | 233 |
1 | 11.12.2015 | 234 |
1 | 12.12.2015 | 235 |
1 | 13.12.2015 | 236 |
1 | 14.12.2015 | 237 |
1 | 15.12.2015 | 238 |
1 | 16.12.2015 | 239 |
1 | 17.12.2015 | 240 |
1 | 18.12.2015 | 241 |
1 | 19.12.2015 | 242 |
1 | 20.12.2015 | 243 |
1 | 21.12.2015 | 244 |
1 | 22.12.2015 | 245 |
2 | 10.12.2015 | 233 |
2 | 11.12.2015 | 234 |
2 | 12.12.2015 | 235 |
2 | 19.12.2015 | 242 |
2 | 20.12.2015 | 243 |
2 | 21.12.2015 | 244 |
2 | 22.12.2015 | 245 |
10.11.2015 | ||
11.11.2015 | ||
12.11.2015 |
But i need to connect Dates from second source for every Distributor _id.
Necessary table:
Distributor _id | Date | Volume |
1 | 10.11.2015 | |
1 | 11.11.2015 | |
1 | 12.11.2015 | |
1 | 10.12.2015 | 233 |
1 | 11.12.2015 | 234 |
1 | 12.12.2015 | 235 |
1 | 13.12.2015 | 236 |
1 | 14.12.2015 | 237 |
1 | 15.12.2015 | 238 |
1 | 16.12.2015 | 239 |
1 | 17.12.2015 | 240 |
1 | 18.12.2015 | 241 |
1 | 19.12.2015 | 242 |
1 | 20.12.2015 | 243 |
1 | 21.12.2015 | 244 |
1 | 22.12.2015 | 245 |
2 | 10.11.2015 | |
2 | 11.11.2015 | |
2 | 12.11.2015 | |
2 | 10.12.2015 | 233 |
2 | 11.12.2015 | 234 |
2 | 12.12.2015 | 235 |
2 | 19.12.2015 | 242 |
2 | 20.12.2015 | 243 |
2 | 21.12.2015 | 244 |
2 | 22.12.2015 | 245 |
Please, help!
Thanks.
Main:
LOAD [Distributor _id],
Date,
Volume
FROM
[Test.xlsx]
(ooxml, embedded labels, table is Sheet1);
Temp:
Load Distinct [Distributor _id] Resident Main
Where Not IsNull([Distributor _id]) or Len(Trim([Distributor _id])) <> 0;
Join
Load
Date
FROM
[Test.xlsx]
(ooxml, embedded labels, table is Sheet2)
Where Len(Trim(Date)) <> 0 or Not IsNull(Date);
Concatenate(Main) Load * Resident Temp;
Drop Table Temp;