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

Concat Group By OrderBy

Hello,

I have the following table:

Travel_IDDestinationSort_Order
1Rome1
1Milan2
1Rome3
2Chicago4
2Chicago6
2Detroit5

I would like to obtain something like this:

Travel_IDPath
1Rome, Milan, Rome
2Chicago, Detroit, Chicago

where the destinations in the 'Path' columns are sorted by 'Sort_order'

The following code doesn't keep the right order:

          Journey:

LOAD * INLINE [
    Travel_ID, Destination, Sort_Order
    1, Rome, 1
    1, Milan, 2
    1, Rome, 3
    2, Chicago, 4
    2, Chicago, 6
    2, Detroit, 5
]
;

Flights:
LOAD
Travel_ID,
Concat(Destination,', ') as Path
Resident Journey
Group by Travel_ID
Order By Sort_Order;

Can you help me?

Thanks in advance.

1 Solution

Accepted Solutions
antoniotiman
Master III
Master III

Try

Concat(Destination,',',Sort_Order)

Regards

View solution in original post

4 Replies
JonnyPoole
Employee
Employee

Interesting question.

in my testing, the field passed to concat() is auto-sorted regardless of ORDER BY.  So i so pre-concatenated the sort number and then purge the numeric characters afterwards

Capture.PNG.png

        Journey:

LOAD * INLINE [

    Travel_ID, Destination, Sort_Order

    1, Rome, 1

    1, Milan, 2

    1, Rome, 3

    2, Chicago, 4

    2, Chicago, 6

    2, Detroit, 5

];

Flights:

LOAD

Travel_ID,

purgechar(Concat(  Sort_Order&Destination,', '),'0123456789') as Path,

Concat(  Sort_Order,', ') as PathSort

Resident Journey

Group by Travel_ID;

drop table temp;

antoniotiman
Master III
Master III

Try

Concat(Destination,',',Sort_Order)

Regards

MarcoWedel

LOAD Travel_ID,

     Concat(Destination, ', ', Sort_Order) as Path

FROM

(html, codepage is 1252, embedded labels, table is @1)

Group By Travel_ID;

MarcoWedel

Hi,

one solution:

QlikCommunity_Thread_135430_Pic1.JPG.jpg

LOAD Travel_ID,

    Concat(Destination, ', ', Sort_Order) as Path

FROM [http://community.qlik.com/thread/135430] (html, codepage is 1252, embedded labels, table is @1)

Group By Travel_ID;

hope this helps

regards

Marco