"QUALIFY ROW NUMBER() OVER (PARTITION BY Country Order by Date DESC)=1"

[kalyandg]

HI,

I have a requirement to be done in Qlikview, which should yield the same result as same as Teradata Query

Select Country, SalesPerson, Customer, Division, Qty, Amount, Date, EXTRACTION_DATE From XXX.Sales

QUALIFY ROW_NUMBER() OVER (
PARTITION BY Country
ORDER BY EXTRACTION_DATE DESC) = 1;

How to write this query in Qlikview?

But i am not planning to write this in extraction script, after extracted from Database and Stored into QVD, i will load the QVD and filter out.

PLease help.

Thanks in Advance,

Kalyan

[Arthur_Fong]

Try this:

Load

autonumber(ROWNO(),Country) as NumCount,

Country, SalesPerson, Customer, Division, Qty, Amount, Date, EXTRACTION_DATE

From XXX.Sales

[kalyandg]

HI,

Thanks for your reply.

Let's say I have records like this , as per the query i mentioned, i should get only one row, where ExtractionDate is in descending

Country	SalesPerson	Customer	Division	Qty	Amount	Date	ExtractionDate
IN	IN054	IN_TN01000	IN_D01	100	10000	31/08/2019	4/9/2019
IN	IN054	IN_TN01000	IN_D01	100	10000	31/08/2019	5/9/2019

[Arthur_Fong]

What about this:

Load

autonumber(ROWNO(),Country) as NumCount,

Country, SalesPerson, Customer, Division, Qty, Amount, Date, min(EXTRACTION_DATE) as EXTRACTION_DATE

GROUP BY

Country, SalesPerson, Customer, Division, Qty, Amount;

From XXX.Sales

[kalyandg]

Hi, 

Min(Extraction Date) will return only the first value, but according to my scenario, i have two different records for same Country, Customer, SalesPerson and Division, in that i need to take out the max - Extraction date

[Luffy]

Hi kalyandg,

did you get the answer to the above question?

if yes pls tell me