Assign field values to a variable using excel -Load Script Qlik sense

Santharubban28 — Mon, 22 May 2023 13:55:10 GMT

I am trying to load an Excel sheet that contains all the column names for a table in Qlik sense. I am able to let the column names in a variable use the peek function. The columnname variable is considered a string, and the same string value is loaded for all rows in that table.

Code:

FOR EACH file in FileList('$(vExcelPath)');

Temp_Sheets:
LOAD
"Sheet Names" as SheetNames
FROM ['$(vExcelPath)']
(ooxml, embedded labels, table is Sheet1);

FOR i = 0 to NoOfRows('Temp_Sheets')-1

LET sheetName = purgeChar(purgeChar(peek('SheetNames', i, 'Temp_Sheets'), chr(39)), chr(36));

// Getting all columnnames from the excel
Columnames:
NoConcatenate
LOAD
// "Column Name" as ColumnNames
Concat("Column Name",',') as ColumnNames
FROM ['$(vExcelPath)']
(ooxml, embedded labels, table is '$(sheetName)')
;

LET vColumnNames = peek('ColumnNames', i, 'Columnames'); //all columnames in this variable

Trace '$(vColumnNames)';
// Loading table
$(sheetName):
NoConcatenate
LOAD '$(vColumnNames)';

Load *

FROM '$(QVDPath)$(sheetName)'(qvd);

Next i;
Next file;

@sunny_talwar @adamdavi3s @jonathandienst @Miguel_Angel_Baeyens @rwunderlich @swuehl

topic Assign field values to a variable using excel -Load Script Qlik sense in App Development

Assign field values to a variable using excel -Load Script Qlik sense