Issue with sorting while using concat function with distinct qualifier

[#1]

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.

[sunny_talwar]

This seems to work:

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

Best,

Sunny

[sunny_talwar]

My bad, it doesn't work.

[sunny_talwar]

PFA the application.May be this will be helpful.

Best,

Sunny

[#5]

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.

[sunny_talwar · Accepted solution]

PFA

[#7]

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

[#8]

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

[#9]

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.

[#10]

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