3 Replies Latest reply: May 22, 2018 1:30 PM by Akshaya Aditya RSS

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

    Lalit Kumar

      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

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

          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.