topic Pulling data from multiple tabs in Excel and load each tab into a separate table in Qlik Sense in Connectivity & Data Prep

Pulling data from multiple tabs in Excel and load each tab into a separate table in Qlik Sense

henryjhu — Mon, 13 Dec 2021 18:06:40 GMT

Hi,

I am using Qlik Sense Enterprise version. So there is no need to pull data into QVF via ODBC.
My data file is an Excel file with multiple tabs. I want to pull the data from each tab into a separate table.
I tried the code below. It is able to pull data. But, it concatenated the data from all tabs and put the result into one table. For some reason that I do not know, but it always load the data from both tabs into the same table named Non_LCD.
Does any know why my code is not working the way it should be?

Here is the Code:

Unqualify *;
set vSheetArray = 'Non_LCD','LCD';
set i =0;
FOR each vSheet in $(vSheetArray)
i=i+1;
if i=1 then
[Non_LCD]:
Load *
From [lib://AttachedFiles/Bridger.xlsx](ooxml, embedded labels, table is [$(vSheet)]);
ENDIF;
if i=2 then
[LCD]:
Load *
From [lib://AttachedFiles/Bridger.xlsx](ooxml, embedded labels, table is [$(vSheet)]);
ENDIF;
NEXT;

Here is the Loading Log:

Started loading data
Non_LCD << Non_LCD Lines fetched: 998
Non_LCD << LCD Lines fetched:
1,010 Creating search index Search index creation completed successfully
App saved
Finished successfully
0 forced error(s)
0 synthetic key(s)

Re: Pulling data from multiple tabs in Excel and load each tab into a separate table in Qlik Sense

Prashant_Naik — Sun, 16 Feb 2020 17:09:35 GMT

Hi,

This might help you

Unqualify *;
set vSheetArray = 'Non_LCD','LCD';
set i =0;
FOR each vSheet in $(vSheetArray)
i=i+1;
if i=1 then

Qualify *;

NoConcatenate
[Non_LCD]:
Load *
From [lib://AttachedFiles/Bridger.xlsx](ooxml, embedded labels, table is [$(vSheet)]);
ENDIF;
if i=2 then

Qualify *;

NoConcatenate
[LCD]:
Load *
From [lib://AttachedFiles/Bridger.xlsx](ooxml, embedded labels, table is [$(vSheet)]);
ENDIF;
NEXT;

To avoid the synthetic key i have used Qualify statement.

You can use Noconcatenate which will avoid the qlik behavioral concat of same fields.

Regards,

Prashant

Re: Pulling data from multiple tabs in Excel and load each tab into a separate table in Qlik Sense

henryjhu — Sun, 16 Feb 2020 18:33:46 GMT

Thank you Prashant !.