Next time, you may want to include what you WANT the output to be. It's hard to tell from your description exactly what you're looking for. I'm going to give it a shot anyway.
The following load script:
Data:
LOAD WareHouse AS WareHouse1,
PartNum AS PartNum1,
Qty AS Qty1,
Price AS Price1,
Loc AS Loc1,
Wrrnty AS Wrrnty1,
RowNo() AS Row
FROM [example_data.xlsx] (ooxml, embedded labels, table is [sheet 1]);
LEFT JOIN (Data)
LOAD WareHouse AS WareHouse2,
PartNum AS PartNum2,
Qty AS Qty2,
Price AS Price2,
Loc AS Loc2,
Wrrnty AS Wrrnty2,
RowNo() AS Row
FROM [example_data.xlsx] (ooxml, embedded labels, table is [sheet 2]);
LEFT JOIN (Data)
LOAD Row,
IF(LEN(WareHouse1)>0, WareHouse1, WareHouse2) AS WareHouse,
IF(LEN(PartNum1)>0, PartNum1, PartNum2) AS PartNum,
IF(LEN(Qty1)>0, Qty1, Qty2) AS Qty,
IF(LEN(Price1)>0, Price1, Price2) AS Price,
IF(LEN(Loc1)>0, Loc1, Loc2) AS Loc,
IF(LEN(Wrrnty1)>0, Wrrnty1, Wrrnty2) AS Wrrnty
RESIDENT Data;
DROP FIELDS WareHouse1, WareHouse2, PartNum1, PartNum2, Qty1, Qty2, Price1, Price2, Loc1, Loc2, Wrrnty1, Wrrnty2, Row FROM Data;
Will return a data table that looks like this:
| WareHouse | PartNum | Qty | Price | Loc | Wrrnty |
|---|
| a | 1868 | 3 | 25 | 1 | y |
| a | 2121 | 8 | 7 | 1 | y |
| b | 1296 | 1 | 150 | 2 | n |
| c | 5496 | 10 | 10 | 3 | y |
| e | 6319 | 7 | 2 | 7 | n |
If this is not what you're looking for, please provide the output that you want.
