topic Re: Issue with sorting while using concat function with distinct qualifier in QlikView

Issue with sorting while using concat function with distinct qualifier

Tue, 07 Apr 2015 15:32:41 GMT

Hi Experts,

I have three fields Item, Code and Date. I would like to concatenate distinct Code values grouped by Item and sorted by Date field. But unfortunately, the code values are sorted alphabetically. If i try sort-weight in concat function using Date field, code values are duplicated.

I tried the same in back end, still same issue. Please find the attached QVW for reference.

Thanks in advance.

Re: Issue with sorting while using concat function with distinct qualifier

sunny_talwar — Tue, 07 Apr 2015 15:45:13 GMT

This seems to work:

=aggr(concat(distinct Code,'-->',Aggr(Date, Item)),Item)

Best,

Sunny

Re: Issue with sorting while using concat function with distinct qualifier

sunny_talwar — Tue, 07 Apr 2015 15:47:20 GMT

My bad, it doesn't work.

Re: Issue with sorting while using concat function with distinct qualifier

sunny_talwar — Tue, 07 Apr 2015 15:57:13 GMT

PFA the application.May be this will be helpful.

Best,

Sunny

Re: Issue with sorting while using concat function with distinct qualifier

Tue, 07 Apr 2015 17:08:34 GMT

Thanks Sunny for the replies. That is really helpful. But still i have one issue. Sometimes, same code appears after few codes then it has to duplicate. For example for item 3, MER repeats after few codes then it should be MER-->ABC-->FHJ-->NUD-->HJD-->MER

3, 09/02/2015, MER

3, 01/02/2015, MER

3, 10/02/2015, ABC

3, 11/02/2015, FHJ

3, 13/02/2015, NUD

3, 15/02/2015, HJD

3, 15/02/2015, MER

Please let me know if you have any idea.

Re: Issue with sorting while using concat function with distinct qualifier

sunny_talwar — Tue, 07 Apr 2015 17:25:58 GMT

PFA

Re: Issue with sorting while using concat function with distinct qualifier

Wed, 08 Apr 2015 07:14:03 GMT

Thanks Sunny. This logic works. But I still wonder why concat function with sort-weight did not work.

Re: Issue with sorting while using concat function with distinct qualifier

Wed, 08 Apr 2015 07:33:56 GMT

Hi Devaki,

the sort-weight did work, just not as you expected, you have multiple date values in the date field per item you a using in the concat, so each item is repeated for each possible weighting value. If you wanted to use sort-weight you would need to ensure the field you use is distinct per item. Something like the attached should work.

hope that helps

Joe

Re: Issue with sorting while using concat function with distinct qualifier

Wed, 08 Apr 2015 09:48:45 GMT

Got it. I tried to implement same logic using aggr function on UI as below:

aggr(concat(distinct Code,'-->',aggr(date(Max(Date),'DD-MM-YYYY'),Item,Code)),Item)

aggr(date(Max(Date),'DD-MM-YYYY'),Item,Code) ----> Gives the same output as you did in the scripting. I thought it will work but still it did not work.

Sorry to bother you on this. Just trying to understand the functionality of concat and aggr functions.

Re: Issue with sorting while using concat function with distinct qualifier

Wed, 08 Apr 2015 10:43:07 GMT

Hi Devaki,

use
aggr(concat(distinct Code,'-->',aggr(NODISTINCT date(Max(Date),'DD-MM-YYYY'),Item,Code)),Item)

that should work for you without the script change. I'm just more of a fan of doing as much in the script as I can that's all

you need the nodistinct in there, as 'MER' has the same max date and you want the aggr to return the same value for each (which without the nodistinct it won't do) and then the concat will give you a distinct end result as they both have same code and sort weight.

If you want to see what I mean by this

plug

concat(distinct Code & aggr( date(Max(Date),'DD-MM-YYYY'),Item,Code),'-->',aggr( date(Max(Date),'DD-MM-YYYY'),Item,Code))

into a text box and you'll see the result of the sort in the concat too and can see the problem visually then.

Maybe have a read of QlikView Technical Brief - AGGR too

Hope that helps

Joe

Re: Issue with sorting while using concat function with distinct qualifier

Wed, 08 Apr 2015 11:11:19 GMT

Interesting. Thank you Joe for sharing this knowledge.

Re: Issue with sorting while using concat function with distinct qualifier

Wed, 08 Apr 2015 11:17:21 GMT

no worries, glad to help

Re: Issue with sorting while using concat function with distinct qualifier

ngetal_93 — Tue, 05 May 2015 08:02:42 GMT

Hello Devaki,

You were saying earlier "For example for item 3, MER repeats after few codes then it should be MER-->ABC-->FHJ-->NUD-->HJD-->MER"

I would like to obtain the same result but I don't see it in the solutions found. Have you been able to do it ?

Thanks in advance,

Matthieu Junier