Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

jchoucq
Contributor II

Concat a field with a sort on expression

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

Tags (1)
1 Solution

Accepted Solutions
Not applicable

Re: Concat a field with a sort on expression

Yes  you can sort. Please find the attached sample application

7 Replies
MVP
MVP

Concat a field with a sort on expression

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
Contributor II

Re: Concat a field with a sort on expression

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

MVP
MVP

Re: Concat a field with a sort on expression

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
Honored Contributor II

Re: Concat a field with a sort on expression

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

Regards,

Vlad

Not applicable

Re: Concat a field with a sort on expression

Thanks for this tip!

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

Not applicable

Re: Concat a field with a sort on expression

Yes  you can sort. Please find the attached sample application

coxanagi
New Contributor

Re: Concat a field with a sort on expression

That's the right answer. Thanks