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

[AntonioB]

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;

[Anil_Babu_Samineni]

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

[rwunderlich · Accepted solution]

It would look like:

Final:
NoConcatenate 
LOAD * Resident Wbooks;

Concatenate (Final)
LOAD * Resident Bbooks;

Drop Tables Wbooks, Bbooks;

-Rob