Incoporate Several Tables with similar name

[#1]

We have a legacy System36 environment/application.

To get data, Qlik makes and ODBC connection to the IBM environment and reads the files as if they were a table.

On our AS400, we have a process that automatically creates End-of-Year SalesMaster files/tables with names of SLS20xxE

I want to automatically loop through these tables and incorporate them so that we do not need to modify the script at Year End.

Pseudo-code

For each Table in IBM720.TUMACDATA
     Match the pattern 'SLS*E'
     SQL SELECT * WHERE CONBX=1 FROM SLS*E
     Concatenate and Store

From the below script you can see that I am choosing the data and letting it concatenate and then saving as a QVD.

SalesMaster:    /**** Current Fiscal Year - no changes Necessary ***/ 
SQL SELECT *
FROM IBM720.QS36F.HL340P1
WHERE CONBX=1;
/*****  Must add completed FY to this list at Year End   ***********/
Sales2007:
SQL SELECT *
FROM IBM720.TUMACDATA.SLS2007E
WHERE CONBX=1;

Sales2008:
SQL SELECT *
FROM IBM720.TUMACDATA.SLS2008E
WHERE CONBX=1;

Sales2009: 
SQL SELECT *
FROM IBM720.TUMACDATA.SLS2009E
WHERE CONBX=1;

Sales2010:
SQL SELECT *
FROM IBM720.TUMACDATA.SLS2010E
WHERE CONBX=1;

Sales2011:
SQL SELECT *
FROM IBM720.TUMACDATA.SLS2011E
WHERE CONBX=1;

Sales2012:
SQL SELECT *
FROM IBM720.TUMACDATA.SLS2012E
WHERE CONBX=1;

Sales2013:
SQL SELECT *
FROM IBM720.TUMACDATA.SLS2013E
WHERE CONBX=1;

Sales2014:
SQL SELECT *
FROM IBM720.TUMACDATA.SLS2014E
WHERE CONBX=1;
Sales2015:
SQL SELECT *
FROM IBM720.TUMACDATA.SLS2015E
WHERE CONBX=1;
Sales2016:
SQL SELECT *
FROM IBM720.TUMACDATA.SLS2016E
WHERE CONBX=1;
Store SalesMaster into \\tlc-qlik\Qlik\data\Extracts\Sales\SalesMaster.qvd;

I want to do something similar to this.  I realize my data form Qlik's point-of-view is a table so the File functions do not work.

FOR each File IN filelist 'IBM720.TUMACDATA.SLS*E'
fileTable:
LOAD FileName('$(File)') as Filename,
     FileDir() as FileDir
     FROM '$(File)';
next File
TMP:
LOAD Filename RESIDENT fileTable ORDER BY Filename desc;
 
        LET mySourceFile = peek('Filename',0,'TMP');
 
       DROP TABLE TMP;
 
data:
SQL SELECT *
FROM [IBM720.TUMACDATA.$(mySourceFile)]

[#2 · Accepted solution]

I solved it this way.  

I found where I can get a file listing/index of the files in a library on the AS400.

I extract the list and then filter it to the relevant files before looping through them to incorporate and concatenate into one big file table in QV.

*************************************************************************************/
SalesMaster:    /**** Current Fiscal Year - no changes Necessary ***/ 
SQL SELECT *
FROM IBM720.QS36F.HL340P1
WHERE CONBX=1;
//Builds list of Sales Files
SalesFiles:
SELECT * FROM IBM720.qsys2.systables 
WHERE TABLE_SCHEMA = 'TUMACDATA';
store SalesFiles into \\tlc-qlik\Qlik\data\extracts\SalesFiles.qvd;
SUB LoadTableNames
SalesTables:
LOAD 
 TABLE_NAME as TableNames 
Resident SalesFiles
WHERE wildmatch(TABLE_NAME,'SLS*E');
END SUB;
Call LoadTableNames;
//Loads Each Preceding year of Sales Files - Automatically created at Year-end by AS400.  QV will automatically import SLS2*E files so nothing needs to be done at Year-end.
Sub LoadTableData
For i = 0 To NoOfRows('SalesTables')-1;
LET vMyTableName = Peek('TableNames', $(i), 'SalesTables');
$(vMyTableName):
SQL SELECT * FROM IBM720.TUMACDATA.$(vMyTableName);
Next i
End Sub;
// ==========================================================================================//
Call LoadTableData;
Store SalesMaster into ....