I think the best way in your case is to use the following:
TableName(n) as TableName
The only problem is that Qlik requires a 'n' to be defined "as number of the specified table". The first table has number 0. This number should be based on number of tables currently in memory (based on the sequence of the load, I believe)...
I did not have chance to test it, but you should be able to drop each table after each loading statement (since you can save it into QVD anyway), so in this case the 'n' could always be 0 (since only one table will be loaded at a time).
Hopefully, this solution will work for you.
P.S. When applicable, please mark this answer Correct or Helpful.
I am surprised that TableName(i) call does not work inside the load statement.
But your approach is correct too.
You can do a data load first, get the Table name after that and do a JOIN with Resident load to add your new field after that... Couple more steps, but it should not take much more time...
This script loads data from all the sheets in excel and variable 'sheetName' creates a fields with all the sheet names.
ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];
SQLtables; // Loads all sheet names in the Excel file.
Where Not Match(Upper(TABLE_TYPE), 'TABLE');
DROP Table SheetNames;
FOR index = 0 to NoOfRows('SystemTable')-1 // Loops for each sheet in the Excel file.
LET sheetName = PurgeChar(PurgeChar(Peek('TABLE_NAME', index, 'SystemTable'), Chr(39)), Chr(36));
Load * ,
FileName() as File_Name,
'$(sheetName)' as Sheet_Name
(biff, embedded labels, table is [$(sheetName)$]);
DROP TABLE SystemTable;
Hope this helps.
I am having similar issue. When trying to use TableName fx, only first sheet is populated. I checked table name on the load and do not see any values. Almost seems like there is an issue with the format. I see you guys are using ODBC to connect to excel. Do you think that connecting directly to *.xlsx may be causing this issue?