topic Re: "QUALIFY ROW NUMBER() OVER (PARTITION BY Country Order by Date DESC)=1" in QlikView

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

kalyandg — Thu, 19 Sep 2019 04:34:28 GMT

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

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

Arthur_Fong — Thu, 19 Sep 2019 04:40:51 GMT

Try this:

Load

autonumber(ROWNO(),Country) as NumCount,

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

From XXX.Sales

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

kalyandg — Thu, 19 Sep 2019 04:49:29 GMT

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

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

Arthur_Fong — Thu, 19 Sep 2019 04:55:13 GMT

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

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

kalyandg — Thu, 19 Sep 2019 06:30:37 GMT

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

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

Luffy — Mon, 12 Sep 2022 18:55:03 GMT

Hi kalyandg,

did you get the answer to the above question?

if yes pls tell me