Concat Group By OrderBy

[#1]

Hello,

I have the following table:

Travel_ID	Destination	Sort_Order
1	Rome	1
1	Milan	2
1	Rome	3
2	Chicago	4
2	Chicago	6
2	Detroit	5

I would like to obtain something like this:

Travel_ID	Path
1	Rome, Milan, Rome
2	Chicago, 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.

[JonnyPoole]

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

        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 · Accepted solution]

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:

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