Use of Aggr function and FirstSortedValue

[Anonymous]

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

[sunny_talwar]

What is your expected output?

[Anonymous]

Hi,

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

SaleID, SalesPerson

1         ,  Ulrich

2         ,  Jens

[sunny_talwar]

Try MaxString() function:

=MaxString(SalesPerson)

[Anonymous]

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

[sunny_talwar]

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?

[Anonymous]

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?