Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
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