topic Concat a field with a sort on expression in QlikView

Concat a field with a sort on expression

jchoucq — Wed, 13 Jul 2011 17:34:57 GMT

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,

Concat a field with a sort on expression

swuehl — Wed, 13 Jul 2011 20:57:48 GMT

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

Re: Concat a field with a sort on expression

jchoucq — Thu, 14 Jul 2011 06:48:49 GMT

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,

Re: Concat a field with a sort on expression

swuehl — Thu, 14 Jul 2011 10:37:03 GMT

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

Re: Concat a field with a sort on expression

vgutkovsky — Tue, 27 Aug 2013 16:01:59 GMT

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

Regards,

Vlad

Re: Concat a field with a sort on expression

Wed, 12 Mar 2014 15:25:07 GMT

Thanks for this tip!

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

Re: Concat a field with a sort on expression

Mon, 13 Apr 2015 22:16:22 GMT

Yes you can sort. Please find the attached sample application

Re: Concat a field with a sort on expression

coxanagi — Thu, 18 Aug 2016 16:50:24 GMT

That's the right answer. Thanks

Re: Concat a field with a sort on expression

chenpeng — Thu, 22 Nov 2018 07:24:27 GMT

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