Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Team,
The attached excel file contains multiple sheets.
1.eSol
2.Data for Field1
3.Data for Field2
4.Data for Field3
5.Data for Field4
My Requirement is my script should load only the sheet names starting with Data for Field
IF i use the below script , it also loads the esol sheet
Script :
FOR EACH file in FileList('lib://int/int.xlsx');
//In order to get the file information from SQLtables command making use of the ODBC connection format
LIB CONNECT TO 'Excel';
tables:
SQLtables;
DISCONNECT;
FOR i = 0 to NoOfRows('tables')-1
//LET sheetName = purgeChar(purgeChar(peek('Data for Field', i, 'tables'), chr(39)), chr(36));
LET sheetName= if(left( purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36)),8)= 'Data for',
purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36)),Null());
tables:
SQLtables;
DISCONNECT;
FOR i = 0 to NoOfRows('tables')-1
LET sheetName= if(left( purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36)),8)= 'Data for',
purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36)),Null());
Table:
Load ID,NAME
From $(file)
(ooxml, embedded labels, table is [$(sheetName)]);
Next i;
Next
Note : The attached image shows that the eSol tab is also been loaded
Regards,
Senthil
Hi Senthil,
the simplest way is when you drop the unwanted table at the end of script,
I don't know continue next or similer command in QlikSense.
Or you can write an if-then-else block based on the sheetname.
G.
I think you are complicating it with a loop.
You can use the * next to the file name. Do like this.:
Table:
Load ID,NAME
From $(file)
(ooxml, embedded labels, table is [Data for Field*]);
This would then load all sheets which start with 'Data for Field'