Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Solved
Hi Microwin.
Check the attachment and let me know,
Table1:
LOAD Unit,
Multiplier
FROM
Book1.xlsx
(ooxml, embedded labels, table is Sheet1);
Left join
Table2:
LOAD Month,
Amount
FROM
Book2.xlsx
(ooxml, embedded labels, table is Sheet1);
NoConcatenate
Final:
Load *,
Multiplier* Amount as Operation
Resident Table1 where len(Unit);
Drop table Table1;
Hi Microwin.
Check the attachment and let me know,
Table1:
LOAD Unit,
Multiplier
FROM
Book1.xlsx
(ooxml, embedded labels, table is Sheet1);
Left join
Table2:
LOAD Month,
Amount
FROM
Book2.xlsx
(ooxml, embedded labels, table is Sheet1);
NoConcatenate
Final:
Load *,
Multiplier* Amount as Operation
Resident Table1 where len(Unit);
Drop table Table1;
Load
...
...
Multiplier*Amount as New ,
resident Table1;
The script from TamilArasuis correct, except that the join should be a default (full outer) join, because you have requested a cartesian product. Remove the 'left' keyword.
you need to perform Cross join between two tables to generate the each possible combination
Data:
LOAD * Inline [
Month, Amount
Jan, 101
Feb, 78
Mar, 68
Apr, 42
May, 43
Jun, 68
Jul, 118
Aug, 150
Sep, 132
Oct, 112
Nov, 122
Dec, 26 ];
join
LOAD * Inline [
Unit, Multiplier
415, 17
418, 25
423, 11 ];
Final:
noconcatenate
LOAD *,
Multiplier*Amount as Operation
Resident Data;
DROP Table Data;
Thanks Jonathan