Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

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
Highlighted
antoniotiman
Honored Contributor III

Re: Concat Group By OrderBy

Try

Concat(Destination,',',Sort_Order)

Regards

View solution in original post

4 Replies
Employee
Employee

Re: Concat Group By OrderBy

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;

Highlighted
antoniotiman
Honored Contributor III

Re: Concat Group By OrderBy

Try

Concat(Destination,',',Sort_Order)

Regards

View solution in original post

Re: Concat Group By OrderBy

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

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