Data Modelling

Fri, 02 Nov 2012 14:37:45 GMT

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.

Re: Data Modelling

Mon, 05 Nov 2012 07:25:58 GMT

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.

Re: Data Modelling

Mon, 05 Nov 2012 11:59:22 GMT

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

Thanks,

Samir Prasadi.

topic Re: Data Modelling in QlikView

Data Modelling

Re: Data Modelling

Re: Data Modelling