topic Re: Use of Aggr function and FirstSortedValue in QlikView

Use of Aggr function and FirstSortedValue

Anonymous — Thu, 03 Dec 2015 13:40:16 GMT

Hi,

For this data example:

SaleID, SalesPerson

1 , Peter

1 , Ulrich

2 , Jens

2 , Casper

I want to show only one row for each SaleID in a straight table, so I use the aggr function.

Aggr(Count(SaleID), SaleID)

But how do I get a certain row based on the sorting of SalesPerson (From Z-A)?

I'm thinking, I need to use the FirstSortedValue function. I have tried the following, but it does not seem to sort by salesperson.

FirstSortedValue(SalesPerson, Aggr(Count(SaleID), SaleID))

Best, Michael

Re: Use of Aggr function and FirstSortedValue

sunny_talwar — Thu, 03 Dec 2015 14:45:49 GMT

What is your expected output?

Re: Use of Aggr function and FirstSortedValue

Anonymous — Thu, 03 Dec 2015 14:48:00 GMT

Hi,

If it's sorted by SalesPerson from Z-A, then:

SaleID, SalesPerson

1 , Ulrich

2 , Jens

Re: Use of Aggr function and FirstSortedValue

sunny_talwar — Thu, 03 Dec 2015 17:13:33 GMT

Try MaxString() function:

=MaxString(SalesPerson)

Re: Use of Aggr function and FirstSortedValue

Anonymous — Fri, 04 Dec 2015 09:35:52 GMT

Hi Sunny and thanks for your answer.

It does give me the desired output, but when I add more dimensions to the table it says that there is an error in the expression?

Is it not possible to use the aggr function when you want to solve this kind of problem?

In SQL you would use:

RowNumber() over (partition by SaleID order by SalesPerson).

Can I do something similar in Qlikview?

Best, Michael

Re: Use of Aggr function and FirstSortedValue

sunny_talwar — Fri, 04 Dec 2015 16:59:12 GMT

Not sure what you have added, but something can be done without aggregate, I would try to do it that way, just because aggregate slows down the processing of the charts. Would you be able to update the above to show your situation with the new dimension?

Re: Use of Aggr function and FirstSortedValue

Anonymous — Wed, 09 Dec 2015 20:45:33 GMT

Hmmm.. I just added several more dimensions to the table, but regardless of wich dimensions I added, then suddenly there was an error in the expression

The problem is in general to show just one row pr. saleID based on the sorting of some dimension in the datamodel. This could be SalesPerson but it could also be VAR2 for instance, which may be an integer and not a string,

SaleID, VAR1, VAR2, VAR3, VAR4, VAR5,..................., SalesPerson

1 , Peter

1 , Ulrich

2 , Jens

2 , Casper

Is there a way to do this in the expression on the table?