Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
I was able to solve the problem using FieldValue() function and introducing Rowno() field in the load script.
Thanks,
Samir Prasadi.
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.
I was able to solve the problem using FieldValue() function and introducing Rowno() field in the load script.
Thanks,
Samir Prasadi.