Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have a strange behavior on QlikView.
I have a pivot table where I have to use "if" condition to make the link between the dimension and the expression.
Let's say I have the following data:
Data:
LOAD year(Date) AS Year,
month(Date) AS Month,
*;
LOAD * INLINE [
Date, Group, Product, Sales
01/01/2012, Drink, Water, 1
02/01/2012, Drink, Soda, 2
03/01/2012, Food, Bread, 3
04/01/2012, Food, Meat, 4
05/02/2012, Drink, Water, 10
06/02/2012, Drink, Soda, 20
07/02/2012, Food, Bread, 30
08/02/2012, Food, Meat, 40
09/03/2012, Drink, Water, 100
10/03/2012, Drink, Soda, 200
11/03/2012, Food, Bread, 300
12/03/2012, Food, Meat, 400
13/04/2012, Drink, Water, 1000
14/04/2012, Drink, Soda, 2000
15/04/2012, Food, Bread, 3000
16/04/2012, Food, Meat, 4000
];
And an island with years and months:
DimYear:
LOAD * INLINE [
DimYear
2012
];
DimMonth:
LOAD month(makedate(2000, RowNo())) AS DimMonth
AUTOGENERATE 12;
I use as Dimension:
- Group
- Product
- "=date(makedate(DimYear, DimMonth), 'MMM YYYY')"
and as expression:
sum(if(DimYear = Year and DimMonth = Month, Sales))
This works like a charm:
Now, I have a constraint: I want the "Product" list box to be sorted with best products first, and other products after, based on the following table:
TableProduct:
LOAD * INLINE [
ProductName, TopProduct
Bread, 1
Water, 1
Soda, 0
Meat, 0
Other Product never sold, 0
];
(note that I have more products in this table that in the Sales table)
So, I've created a new table at the begining of my script:
ProductLoadOrder:
LOAD DISTINCT ProductName AS Product
RESIDENT TableProduct
ORDER BY TopProduct DESC, ProductName ASC
;
DROP TABLE TableProduct;
But once I've done that, my chart doesn't work anymore!
The month "jan 2012" works fine, but all the others months are ignored.
But this works if my field "Group" is collapsed:
Why?!?
I've found a workaround: load the Product first in the Data table, but I don't find this elegant.
Data:
LOAD DISTINCT ProductName AS Product
RESIDENT TableProduct
ORDER BY TopProduct DESC, ProductName ASC
;
DROP TABLE TableProduct;
CONCATENATE (Data)
LOAD year(Date) AS Year,
month(Date) AS Month,
*;
LOAD * INLINE [
Date, Group, Product, Sales
01/01/2012, Drink, Water, 1
02/01/2012, Drink, Soda, 2
03/01/2012, Food, Bread, 3
04/01/2012, Food, Meat, 4
05/02/2012, Drink, Water, 10
06/02/2012, Drink, Soda, 20
07/02/2012, Food, Bread, 30
08/02/2012, Food, Meat, 40
09/03/2012, Drink, Water, 100
10/03/2012, Drink, Soda, 200
11/03/2012, Food, Bread, 300
12/03/2012, Food, Meat, 400
13/04/2012, Drink, Water, 1000
14/04/2012, Drink, Soda, 2000
15/04/2012, Food, Bread, 3000
16/04/2012, Food, Meat, 4000
];