Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
kalyandg
Partner
Partner

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

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

4 Replies
Arthur_Fong
Partner
Partner

Try this:

Load

autonumber(ROWNO(),Country) as NumCount,

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

From XXX.Sales

 

kalyandg
Partner
Partner
Author

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

CountrySalesPersonCustomerDivisionQtyAmountDateExtractionDate
ININ054IN_TN01000IN_D011001000031/08/20194/9/2019
ININ054IN_TN01000IN_D011001000031/08/20195/9/2019
Arthur_Fong
Partner
Partner

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
Partner
Partner
Author

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