Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

sum in script with multiple excel sheets (tabs)

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


1 Solution

Accepted Solutions
Not applicable
Author

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

View solution in original post

3 Replies
Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

Thanks!