Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

jansen28
New Contributor III

How to remove duplicates in FirstSortedValue?

Hi, I need to find which [Sales Person] has performed well in a country? Here is my input table

CountryStateCitySales Personsale
ArgentinaBuenos AiresAyacuchoPamela1200
AustraliaNew South WalesAvalonAnke3600
AustraliaNew South WalesLindfiledJacob3600
AustriaViennaInner CityCorina3600
AustriaViennaMid CityCorine5000

My output in qlikview:

Country Largest Sales By SaleRep FirstSortedValue(SalesPerson,-aggr(sum(Sale),Country,SalesPerson))
5000 Mike
Argentia1200Jan
Australia3600
Austria5000Mike

So there is null value in my 2nd row. Hence how do I remove duplicate or how should I bring my top Performer with repeated value?

Any help appreciated?

2 Replies
Not applicable

Re: How to remove duplicates in FirstSortedValue?

As far as I know, first sorted value returns a blank/null when more than value has the same weight. So here two names could end up having the same sales and hence it is not able to return a value. You can pick one by changing the formula to FirstSortedValue(distinct SalesPerson,-aggr(sum(Sale),Country,SalesPerson))

jansen28
New Contributor III

Re: How to remove duplicates in FirstSortedValue?

Thanks for the reply ChanderSmiley Happy

Community Browser