Concatenate Duplicate Data Without Sorting

[tamilarasu]

Hi All,

I am new to this forum and new to Qlikview as well. I have come across a scenario which I am trying to solve for the past few days.

I want to concatenate the data based on the unique ID i.e if the unique ID is same, I need to concatenate the data without sorting.

Attached screenshot for better understanding.

I tried the above in load script and I got the result but the data gets automatically sorted after concatenation.

Test:

LOAD * INLINE [

    Unique ID, Data

    1, B

    1, A

    1, C

    2, D

    3, E

    3, F

    4, G

    5, H

    5, I

    6, J

];

Final:

LOAD 

    [Unique ID],

    Trim(Concat(Data,' ')) as FinalData

Resident Test

Group By [Unique ID];

I tried to store the above data as qvd file and checked whether the data has been sorted while uploading the data. But its same and I guess the sorting was done after concatenation.

Can anyone help me to sort out this problem.?

Thanks in advance,

Tamil

[anbu1984 · Accepted solution]

Try this

Test:

LOAD * INLINE [

    Unique ID, Data

    1, B

    1, A

    1, C

    2, D

    3, E

    3, F

    4, G

    5, H

    5, I

    6, J

];

Final:

LOAD

    [Unique ID],

    Trim(Concat(Data,' ',RecNo())) as FinalData

Resident Test

Group By [Unique ID];

[tamilarasu]

Wooww.. Thank you Anbu Chelian.  Working fine.. . Can you tel me what  exactly happened in the background.?

[jagan]

Hi,

Try this script

Test:

LOAD *,

RecNo() AS ID

INLINE [

    Unique ID, Data

    1, B

    1, A

    1, C

    2, D

    3, E

    3, F

    4, G

    5, H

    5, I

    6, J

];

Final:

LOAD

    [Unique ID],

    Trim(Concat(Data,' ', ID)) as FinalData

Resident Test

Group By [Unique ID];

Regards,

jagan.

[jagan]

Hi,

When you use Concat() it will the dimension values and then concatenate the values in the dimension, to overcome this we need to add the sort weight parameter to specify the sorting order, in above RecNo() gives the actual loading order of the rows.

Hope this helps you.

Regards,

Jagan.

[tamilarasu]

Thank you Jagan for the reply. I have marked the question as solved.

[anbu1984]

From QV help,

concat ([ distinct ] expression [, delimiter [, sort-weight]])

Returns the aggregated string concatenation of all values of expression iterated over a number of records as defined by a group by clause. Each value may be separated by the string found in delimiter. The order of concatenation may be determined by sort-weight. Sort-weight should return a numeric value where the lowest value will render the item to be sorted first. If the word distinct occurs before the expression, all duplicates will be disregarded.

RecNo() is used as sort-weight. So Concat() concatenates Data ordered by RecNo(), with lowest values first

Unique ID, Data, RecNo()

    1, B,1

    1, A,2

    1, C,3

[tamilarasu]

Thank you.. U guys are so fast.. Thank you once again for answering so quickly..