0 Replies Latest reply: Dec 13, 2012 5:00 AM by Nicolas MARTIN RSS

    Wrong result with a condition in my expression

    Nicolas MARTIN

      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:

      2012-12-13_105018.png

       

       

      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;
      

       

       

      2012-12-13_105536.png

       

       

      But once I've done that, my chart doesn't work anymore!

       

      2012-12-13_105655.png

       

      The month "jan 2012" works fine, but all the others months are ignored.

       

      But this works if my field "Group" is collapsed:

      2012-12-13_105914.png

       

      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
      ];