topic Help! Nested Aggregation Sort in QlikView

Help! Nested Aggregation Sort

Anonymous — Fri, 02 Jun 2017 21:07:19 GMT

Hi,

I have an aggregation like this:

= concat( aggr(count(distinct ID_FIELD), TXT_FIELD),'|')

And I need the concatenation result to be in alphabetic order, so I tried this:

= concat( aggr(count(distinct ID_FIELD), (TXT_FIELD,(TEXT,A2Z))),'|')

Also I have tried something like this:

= concat( aggr(count(distinct ID_FIELD), TXT_FIELD),'|',TXT_FIELD)

And even this:

= concat( aggr(count(distinct ID_FIELD), (TXT_FIELD,(TEXT,A2Z))),'|',(TXT_FIELD,(TEXT,A2Z))

With no luck,can you please help me?

Thanks!

Re: Help! Nested Aggregation Sort

Sat, 03 Jun 2017 17:27:28 GMT

Hi Bruno,

I think your data would be something like this:

ID_FIELD|TXT_FIELD

11|abc

12|def

13|ghi

14|jkl

15|mno

14|jkl

13|ghi

14|jkl

15|mno

and I guess you would want ouput as:

ID_COUNT|TXT_FIELD

5|mno

4|jkl

3|ghi

2|def

1|abc

You need to have 2 expressions in your chart object:

1. For ID_COUNT

=MAX(AGGR(COUNT(ID_FIELD),TXT_FIELD)))

Sort this expression field in descending order by value.

2. For TXT_FIELD

=FIRSTSORTEDVALUE([TXT_FIELD],-AGGR(COUNT(ID_FIELD),TXT_FIELD)))

Let me know if this helps.

Re: Help! Nested Aggregation Sort

vinieme12 — Sat, 03 Jun 2017 20:42:31 GMT

Search for threads like Sortable AGGR

The sortable Aggr function is finally here!

Re: Help! Nested Aggregation Sort

Anonymous — Sun, 04 Jun 2017 20:33:15 GMT

Thanks for your answer, but I still can't find where my mistake is!

Re: Help! Nested Aggregation Sort

Anonymous — Sun, 04 Jun 2017 20:38:13 GMT

Thanks for your answer, but I really need it to be in a single expression!

Also I don't have any dimensions in this chart, I'm using it do create an URL to download an image from Google Chart!

What I ended up doing was:

=keepchar(concat( aggr(TXT_FIELD&'-'&count(distinct ID_FIELD), TXT_FIELD),'|'),'0123456789')

Ugly, I agree.. but works!

But I still don't understand why this won't work:

= concat( aggr(count(distinct ID_FIELD), (TXT_FIELD,(TEXT,A2Z))),'|')

Thanks!

Re: Help! Nested Aggregation Sort

swuehl — Sun, 04 Jun 2017 21:45:22 GMT

The Concat() function applies its own sort to the elements to be concatenated, that's why you can set a sort weight as third argument. If you don't set a sort weight, the elements will be sorted in alphabetical order, that's why your work around works like expected.