Concat a field with a sort on expression

[jchoucq]

Hello everybody,

is it possible, in the concat function, to use an expression in the sort-weight parameter ?

concat( Distinct MyField, '|') works perfectly.

the string is sorted in an alphabetical order.

I would like to sort my field on an expression like Sum(Measure) ....

Regards,

Jo

[swuehl]

Hi jchoup,

acc. to the manual, concat takes a third parameter for sort (should evaluate to numeric value, sort order is ascending.

So, without trying, a

concat (Distinct MyField, '|', sum(measure) )

might already do the trick.

Regards,

Stefan

[jchoucq]

Hi Stefan,

Thank you four your answer.

this exactly what i've done, but it doesn't work.

It seems that the third paramater (sort-weight) must be a field and not an expression.

with the FirstSortedValue Function, i can get a field after sorting on an expression, but only the first one ...

Regards,

Jo

[swuehl]

Hi Jo,

I noticed a thread answer by Johannes (http://community.qlik.com/message/75544#75544) where the help file stated

The function argument expressions must not contain aggregation functions,
unless these inner aggregations contain the total qualifier. 
For more advanced nested aggregations, please use the aggr function in 
combination with calculated dimensions.

Though I couldn't find that passage in my help to topic concat .

And indeed, sum( total Amount) as sort weight is a valid expression, but of course does not give a useful sort order.

So I tried with something like

=concat( Distinct Salesman, ',' , -aggr( sum( Amount), Salesman))

which looks promising, but results in first an alphabetical ordered list of Salesman, then sum(amount) ordered list in one concatted string.

Strange, have to think about for another while.

For the moment, I added a fix with a right statement to get only the list of Salesman ordered by sum(amount).

This seems to work, though it looks a bit ugly.

Find attached my sample.

Regards,

Stefan

[vgutkovsky]

For future reference, adding "nodistinct" to the inner aggr fixes the duplication problem (although I'm not sure why).

Regards,

Vlad

[#6]

Thanks for this tip!

It was driving me crazy the duplication values even when I used the distinct command!

[#7 · Accepted solution]

Yes  you can sort. Please find the attached sample application

[coxanagi]

That's the right answer. Thanks

[chenpeng]

you are right , but I want the sort order  is descending. how can i repsenct it!