Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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
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
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
Thanks!