Excel Sheets in load script

[didierodayo]

Hello,

I need some help with this scenario, the attached excel file has 2 sheets with the same columns . the sheet names are

city1_Sales    and    city2_Sales

I would like to read the content of both sheets in the same load script . How can this be achieved?

Thanks

[Anonymous]

Hi,

Try:

for a = 1 to 2

Data:
LOAD *
FROM
[Bookings.xlsx]
(ooxml, embedded labels, table is city$(a)_Sales);

NEXT a;

[didierodayo]

Hi Deran,

thanks for your reply . The only constant here is _Sales. some sheets could be City22_Sales   or town1_Sales.

Cheers

[rahulpawarb]

I agree with Deran!

[Anonymous]

Remove the loop and variable and try *_Sales on the file name

[Anonymous]

Try

Data:

LOAD *
FROM
[Bookings.xlsx]
(ooxml, embedded labels, table is *_Sales);

[Anonymous]

I dont think the wildcard will work for sheets.

See:

Best way to read excel sheet with many tab ?

[didierodayo]

Yes Deran is right the wildcard won't work for sheets..

[flaurntiu · Accepted solution]

Hello Didier,
There are several ways to do what you want. However it is also depending on how you would like to use/merge the data.
If you just want to have the contents of both sheets in one table and nothing aggregated, you will need to differentiate between the values from City 1 and 2.This can be done using a concatenate load statement that will append the values from the second city sheet (City2_Sales) to the values of the first city sheet (city1_Sales).
A key value should be generated to avoid the Sales_ID fields from the cities to be linked/mixed.

I have created an example that loads both data sets into one table and adds the key field. Just put it in the same folder as the excel sheet and reload the data. Then modify some data or add some data to the sheets and reload again.

Adding more cities with data now consists of copying the concatenate load part in the script and modifying the City id in two places + the name of the sheet to load data from. See the code snippet below, with the id's that must be changed in blue.

concatenate load

'0002' & '|' & Text(Sales_ID) AS CitySales_ID, //concatenate city number with sales_ID per city

    Location,

    Sale_Date,

    Sale_Volume,

    Sale_Customer,

    Sales_ID,

    '0002' AS City_ID

FROM

[.\Bookings.xlsx]

(ooxml, embedded labels, table is city2_Sales, filters(

ColXtr(1, RowCnd(CellValue, 1, StrCnd(null, not)), 0),

Remove(Row, RowCnd(CellValue, 1, StrCnd(null)))

));

You could even modify the load script to iterate through the sheets if you make a separate sheet (City_list) a list of sheet names with City number in the Excel workbook and load that first. Retrieve the names one by one from the list and put them in a variable. Create a For .. Next loop to loop through the values and concatenate load all cities.

Have fun Qlik-ing around!
Regards,

Ferry

[didierodayo]

Thanks a lot Ferry  this is the concept I will adopt. You saved my day.