topic sum in script with multiple excel sheets (tabs) in QlikView

sum in script with multiple excel sheets (tabs)

Wed, 19 May 2010 08:26:12 GMT

I want to do a sum in script where multiple excel sheets (jan,feb,mrt,apr etc) are loaded autimatically

(see http://community.qlik.com/blogs/qlikviewblogs/archive/2008/09/29/loading-multiple-excel-sheets.aspx).

Problem I have is that the sum is accumulated for all sheets while I want a sepperated sum for each sheet. I think the cause is the for next loop but don't know how to solve this problem:

If the sum(sales) for each month is 5

I get:

	sum (Sales)
jan (sheet1)	5
feb (sheet2)	10
mrt (sheet3)	15

I want:

	sum (Sales)
jan (sheet1)	5
feb (sheet2)	5
mrt (sheet3)	5

sum in script with multiple excel sheets (tabs)

Wed, 19 May 2010 09:08:38 GMT

I think that you have to operate in 3 steps :

- First you load your sales in a Temporary table

- Second you calculate your sum per month using SUM() and GROUP BY functions

- Finally you drop the temporary table

Here is an example :

_______________________________________

Temp:

LOAD Month(Date) as Month,

Sales

FROM

test.xlsx

(ooxml, embedded labels, table is Jan);

LOAD Month(Date) as Month,

Sales

FROM

test.xlsx

(ooxml, embedded labels, table is Feb);

LOAD Month(Date) as Month,

Sales

FROM

test.xlsx

(ooxml, embedded labels, table is Mar);

Sales:

LOAD Month, SUM(Sales)

RESIDENT Temp

GROUP BY Month;

DROP TABLE Temp;

____________________________________________

hope this helps

Olivier

sum in script with multiple excel sheets (tabs)

Wed, 19 May 2010 09:17:16 GMT

Hi,

Once you connect excel to Qlikview via ODBC or OLEDB you`ll get TABLE_NAME as field. this must be your sheet name.

You can use this field in group by clause and sum up your sales. you`ll get the desired result.

-Peterson

sum in script with multiple excel sheets (tabs)

Wed, 19 May 2010 10:26:24 GMT

Thanks!