Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Issue with sorting while using concat function with distinct qualifier

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.

1 Solution

Accepted Solutions
12 Replies
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

Not applicable
Author

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

PFA

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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.

Not applicable
Author

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