2 Replies Latest reply: Nov 5, 2012 6:59 AM by Samir Prasadi RSS

    Data Modelling

      Hi All,

       

      I need help in converting following SQL query into QlikView -

       

      SELECT

          X.PRICE, X.PRODUCT, X.LOCATION

      FROM PRODUCT_PRICE X,

          (

              SELECT

                  MAX(LAST_DATE)      AS MAX_LAST_DATE,

                  MAX(UPDATE_DATE) AS MAX_UPDATE_DATE,

                  LOCATION,

                  PRODUCT

               FROM PRODUCT_PRICE

               WHERE LAST_DATE <= '<user entered date>'

               GROUP BY LOCATION, PRODUCT

           ) MAX_DATE

      WHERE

      (

          LAST_DATE =    MAX_LAST_DATE

          AND X.LOCATION = MAX_DATE.LOCATION

          AND X.PRODUCT  = MAX_DATE.PRODUCT

          AND X.UPDATE_DATE = MAX_DATE.MAX_UPDATE_DATE

      )   

       

      The logic in query is - the inner query is computing the Max last date and Max update date based on Location and Product. Once I get the Max last date and Max update I get the PRICE information for those respective products and locations in outer select query.

       

      In QlikView I am able to find the Max last date and max update date, however I am not able to get the price information which is achieved in outer query.

       

      Please let me know how to get solution for this situation.

       

      Thanks,

      Samir Prasadi.