Hi Baarathi - I see you have a few replies from our awesome community. Let me know if you need further assistance. In short - this is most likely because Qlik Sense needs to be in Legacy mode - (Disable Standard Mode) - so it can use the legacy file paths.
Check out this resource by our man amz ! - Great article Arturo
When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others.
Please take a look at this article in which I tried to explain how to handle such a case:
Sometimes, we can face a situation where our source is an excel file containing multiple sheets (with the same columns) we want to concatenate into one final table in Qlik.
So in order to do so, we'll have to manually loop the sheets... BOOOOORIIIING !
Another way is to create a generic script that would do this dynamically for us while we're happily laying back in the chair enjoying the show, right?
ps: To use this in Qlik Sense, you'll have to disable the Legacy Mode in order to be able to enter a full path (without being forced to work with Lib statements only):
to disable leagcy mode:
in desktop :
- Open Settings.ini in a text editor.
- Change StandardReload=1 to StandardReload=0.
- Save the file and start Qlik Sense Desktop.
So without further due, here's a script you can use :
//First of all, specify your file path:
FOR EACH file in FileList('C:\Users\OmarBenSalem\Desktop\Data\Data.xls');
//In order to get the file information from SQLtables command making use of the ODBC connection format
ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];
FOR i = 0 to NoOfRows('tables')-1
LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36));
(biff, embedded labels, table is [$(sheetName)$]);
will result into :