Import multiple sheets from an excel File with CrossTable fucntion in Qlik Sense Script

[MarioS]

Dear Qlikers, 

I want to Export multiple Sheets from an excel file and Crosstable it has the same number of column by dates are different below is a screenshot of the data:

Team:
CrossTable(Period,Data,3)

Team, Unit and Team member field will stay the same however the dates fields will change according to the current month 

Below are the requirements:

1- Import multiple sheets (maybe using a loop if feasible)

2- Crosstab the table while I am importing the exvcel sheet :

CrossTable(Period,Data,3)

[chrismarlow]

Hi,

There is quite a long thread on the first part of this (scanning Excel file for multiple sheets) here;

https://community.qlik.com/t5/Qlik-Sense-App-Development/how-to-connect-to-Excel-with-ODBC/td-p/1567...

You might want to run through parts of that as depending on how this is going to be used you might get something working on a development set up but then hit a wall in a production environment.

I am going to try to set it up for myself, as it is quite an interesting problem, so will post back at some point.

Cheers,

Chris.

[chrismarlow · Accepted solution]

Hi,

So depending on what access you have to your environment something similar to the below might work, where Community is a folder connection to the folder your file is in & MultipleTabs is an ODBC connection to the Excel file set up through Windows ODBC per the thread (I only seemed to have .xls driver ... 🤔).

The crosstable on the initial load of the first sheet seemed to need working around hence the load, crosstable from resident, then drop (not sure if had something else wrong). For the loop it seems to work out the subsequent loads should be concatenated, which is nice, but would be quite fragile to changes in inputs (although the whole thing would be, given Excel as a source).

Cheers,

Chris.

LIB CONNECT TO MultipleTabs;

TABLES:
LOAD *;
SQLtables;
DISCONNECT;

Let vTable = Peek('TABLE_NAME', 0, 'TABLES');

data_temp:
LOAD *
FROM [lib://Community/MultipleTabs.xls]
(biff, embedded labels, table is '$(vTable)');

DATA:
CrossTable(Period,Data,3)
LOAD * RESIDENT data_temp;

drop table data_temp;

For i = 1 To NoOfRows('TABLES')-1
    Let vTable = Peek('TABLE_NAME', i, 'TABLES');

    CrossTable(Period,Data,3)
    LOAD *
    FROM [lib://Community/MultipleTabs.xls]
    (biff, embedded labels, table is '$(vTable)');

Next

DROP TABLES TABLES;