topic Re: Concat Group By OrderBy in QlikView

Concat Group By OrderBy

Mon, 29 Sep 2014 15:56:53 GMT

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.

Re: Concat Group By OrderBy

JonnyPoole — Mon, 29 Sep 2014 16:08:37 GMT

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;

Re: Concat Group By OrderBy

antoniotiman — Mon, 29 Sep 2014 16:14:54 GMT

Try

Concat(Destination,',',Sort_Order)

Regards

Re: Concat Group By OrderBy

MarcoWedel — Mon, 29 Sep 2014 16:15:18 GMT

LOAD Travel_ID,

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

FROM

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

Group By Travel_ID;

Re: Re: Concat Group By OrderBy

MarcoWedel — Mon, 29 Sep 2014 16:17:01 GMT

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