topic Re: Excel Sheets in load script in QlikView

Excel Sheets in load script

didierodayo — Thu, 23 Mar 2017 08:18:12 GMT

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

Re: Excel Sheets in load script

Anonymous — Thu, 23 Mar 2017 08:32:02 GMT

Hi,

Try:

for a = 1 to 2

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

NEXT a;

Re: Excel Sheets in load script

didierodayo — Thu, 23 Mar 2017 08:49:31 GMT

Hi Deran,

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

Cheers

Re: Excel Sheets in load script

rahulpawarb — Thu, 23 Mar 2017 08:50:20 GMT

I agree with Deran!

Re: Excel Sheets in load script

Anonymous — Thu, 23 Mar 2017 08:53:10 GMT

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

Re: Excel Sheets in load script

Anonymous — Thu, 23 Mar 2017 09:07:15 GMT

Try

Data:

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

Re: Excel Sheets in load script

Anonymous — Thu, 23 Mar 2017 09:13:26 GMT

I dont think the wildcard will work for sheets.

See:

Best way to read excel sheet with many tab ?

Re: Excel Sheets in load script

didierodayo — Thu, 23 Mar 2017 09:22:21 GMT

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

Re: Excel Sheets in load script

flaurntiu — Thu, 23 Mar 2017 10:03:15 GMT

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

Re: Excel Sheets in load script

didierodayo — Thu, 23 Mar 2017 10:24:00 GMT

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

Re: Excel Sheets in load script

rahulpawarb — Thu, 23 Mar 2017 10:37:35 GMT

Hello Didier,

I have created sample application as per your requirement. Please refer the same in attachment.

P.S.: I have created excel macro to populate the sheet names in first sheet. Once your add new sheet to the report then run the macro to get the latest sheet names.

Regards!

Rahul