Qlik Community

Connectivity & Data Prep

Discussion board where members can learn more about Qlik Sense Data Connectivity.

cancel
Showing results for 
Search instead for 
Did you mean: 
henryjhu
Contributor
Contributor

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

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)

Labels (2)
2 Replies
Prashant_Naik
Partner
Partner

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

henryjhu
Contributor
Contributor
Author

Thank you Prashant !.