Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I want to load table ‘Wbooks’ and table ‘Bbooks’ separately, then load the data from both tables into a single table in the Script Editor, and afterwards drop the separate tables.
I was not able to figure out the part where I load the data from the separate tables into a single table from within the Script Editor. This is my script, can you show me how?
Wbooks:
NoConcatenate
LOAD
"Title" AS "WB Title",
"Business" AS "WB Business"
IF(Len(Level = 13), 'Discarded'
FROM [lib://Books/WBBooks.xlsx]
(ooxml, embedded labels, table is [WBBooks])
Where [Date] < MakeDate(2011,8,1) ;
drop table Wbooks;
Bbooks:
NoConcatenate
LOAD
"Title" AS "BN Title",
"Business" AS "BN Business"
IF(Len(Level = 31), 'Discarded'
FROM [lib://Books/BNBooks.xlsx]
(ooxml, embedded labels, table is [BNBooks])
Where [Date] > MakeDate(2004,6,30) ;
drop table Bbooks;
It would look like:
Final:
NoConcatenate
LOAD * Resident Wbooks;
Concatenate (Final)
LOAD * Resident Bbooks;
Drop Tables Wbooks, Bbooks;
-Rob
Try this?
Wbooks:
LOAD
"Title" AS "WB Title",
"Business" AS "WB Business"
IF(Len(Level) = 13, 'Discarded') as Flag
FROM [lib://Books/WBBooks.xlsx]
(ooxml, embedded labels, table is [WBBooks])
Where [Date] < MakeDate(2011,8,1);
Bbooks:
LOAD
"Title" AS "BN Title",
"Business" AS "BN Business"
IF(Len(Level) = 31, 'Discarded') as Flag
FROM [lib://Books/BNBooks.xlsx]
(ooxml, embedded labels, table is [BNBooks])
Where [Date] > MakeDate(2004,6,30);
LET vStart = 1;
LET vEnd = 3;
Single_file:
LOAD '' AS Single_file.Field1,
'' AS Single_file.Field2
AutoGenerate(0);
FOR index = vStart TO vEnd
Concatenate(Single_file)
Load *
FROM [lib://Books/*.xlsx]
(ooxml, embedded labels, header is 10 lines, table is [$(index)]);
NEXT index
It would look like:
Final:
NoConcatenate
LOAD * Resident Wbooks;
Concatenate (Final)
LOAD * Resident Bbooks;
Drop Tables Wbooks, Bbooks;
-Rob