Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

lalitkgehlot89
New Contributor III

How to pick the data From multiple sheet dynamically in Qliksense.

Hi,

Hi,

In Attached sample data file I want to pick all the data from all sheet of the excel.

But my no of sheet is not defined. It could be increased in future. but data format will be the same on all sheet.

Please let me know how to pic all the data with sheet name as column in data.

Thanks,

Lalit Kumar

3 Replies
adityaakshaya
Contributor III

Re: How to pick the data From multiple sheet dynamically in Qliksense.

Hi Lalit,

Please follow the below steps to pick data from all sheets at one go.

Step 1: Create a Connection and select the folder where your file reside and name it as "Data"

Step 1: Create a ODBC connection with same Excel file.

Step 2 : Count the number of Tables(Sheets) by use below Script

Tables:

SQLTABLES;

let vNoOfTables=NoOfRows('Tables');

Step 3: Run the Loop to pull data from all the sheets by using code below

For i = 0 to $(vNoOfTables)-1

Let vSheetName=subfield(peek('TABLE_NAME', i,'Tables'),'$',1);

  $(vSheetName):

LOAD *
FROM
[Lib://Data/Sample Data File.xlsx]
(ooxml, embedded labels, table is '$(vSheetName)')

;
  NEXT i

Regards,

Akshaya

PS - if you find this response as Correct and helpful, Please mark it as Correct and Helpful.

lalitkgehlot89
New Contributor III

Re: How to pick the data From multiple sheet dynamically in Qliksense.

Hi Akshaya,

Its working fine. As you mention in Step1. Suppose I have N numbers of excel file with N numbers of sheet . In that case I'll have to make connection with all the excels files. or Is there any option to pick the data from all files including all sheet at a single time.

adityaakshaya
Contributor III

Re: How to pick the data From multiple sheet dynamically in Qliksense.

Hi Lalit,

While working with Qliksense, if you can change Legacy Mode to 0, you can do the looping and achieve it but I will not suggest that as Managing Paths at QMC level become a challenge.

If you have fixed number of files, then I will suggest you to create ODBC connection and do as we discussed above or if you have fixed number of sheets and N number of Files, then you loop to find the number of files and within it run a another loop to fetch data from different sheets.

Regards,

Akshaya