Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Good evening,colleagues!
I have simple example to solve my real problem.
In firtst load source i have three fields:
WareHouse,
Nomenclature
Date
Second:
Date
I need that every date from second soruce will be connected with every Nomenclature and every WareHouse (of cource, without dublicated dates, if some of them match in first source.
My necessary table:
WareHouse | Nomenclature | Date | Volume |
1 | 33 | 01.01.2015 | 10 |
1 | 33 | 04.01.2015 | |
1 | 33 | 05.01.2015 | |
1 | 45 | 02.01.2015 | 20 |
1 | 45 | 04.01.2015 | |
1 | 45 | 05.01.2015 | |
1 | 46 | 03.01.2015 | 21 |
1 | 46 | 04.01.2015 | |
1 | 46 | 05.01.2015 |
Try this:
Main:
LOAD WareHouse,
Nomenclature,
Date,
Volume
FROM
[Test.xlsx]
(ooxml, embedded labels, table is Source1);
Join(Main)
LOAD Date as Date1
FROM
[Test.xlsx]
(ooxml, embedded labels, table is Source2);
Final:
LOAD WareHouse,
Nomenclature,
Date1 as Date
Resident Main;
Concatenate(Final)
LOAD WareHouse,
Nomenclature,
Date,
Volume
Resident Main;
DROP Table Main;
Try this:
Main:
LOAD WareHouse,
Nomenclature,
Date,
Volume
FROM
[Test.xlsx]
(ooxml, embedded labels, table is Source1);
Join(Main)
LOAD Date as Date1
FROM
[Test.xlsx]
(ooxml, embedded labels, table is Source2);
Final:
LOAD WareHouse,
Nomenclature,
Date1 as Date
Resident Main;
Concatenate(Final)
LOAD WareHouse,
Nomenclature,
Date,
Volume
Resident Main;
DROP Table Main;
Main:
LOAD
WareHouse,
Nomenclature,
Date,
Volume
FROM
[Test.xlsx]
(ooxml, embedded labels, table is Source1)
Where len(Trim(WareHouse))>0
;
Tmp:
NoConcatenate load Distinct
WareHouse,
Nomenclature
Resident Main;
join (Tmp)
LOAD
Date
FROM Test.xlsx
(ooxml, embedded labels, table is Source2);
Concatenate (Main) LOAD * Resident Tmp;
DROP Table Tmp;
Thanks, dear!!
I see some other result in your model:
Your excel was pulling in more than 3 rows from source1 tab. try with the attached excel
Thanks!!)
Thanks, Sunny)
Not a problem