Data Modelling

[#1]

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.

[#2]

Hi,

The problem here is not related to date, it is with getting PRICE information after I get the Max Last Date and Max Update Date.

I hope my problem statement is clear.

Thanks,

Samir Prasadi.

[#3 · Accepted solution]

I was able to solve the problem using FieldValue() function and introducing Rowno() field in the load script.

Thanks,

Samir Prasadi.