topic Re: Load tables separately, merge to one table, then drop two separate tables in App Development

Load tables separately, merge to one table, then drop two separate tables

AntonioB — Fri, 30 Nov 2018 03:37:07 GMT

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;

Re: Load tables separately, merge to one table, then drop two separate tables

Anil_Babu_Samineni — Fri, 30 Nov 2018 04:26:03 GMT

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

Re: Load tables separately, merge to one table, then drop two separate tables

rwunderlich — Fri, 30 Nov 2018 06:22:33 GMT

It would look like:

Final:
NoConcatenate
LOAD * Resident Wbooks;

Concatenate (Final)
LOAD * Resident Bbooks;

Drop Tables Wbooks, Bbooks;

-Rob