topic Re: Concatenate Duplicate Data Without Sorting in QlikView

Concatenate Duplicate Data Without Sorting

tamilarasu — Mon, 17 Nov 2014 05:33:33 GMT

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

Re: Concatenate Duplicate Data Without Sorting

anbu1984 — Mon, 17 Nov 2014 05:38:33 GMT

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];

Re: Concatenate Duplicate Data Without Sorting

tamilarasu — Mon, 17 Nov 2014 05:47:23 GMT

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

Re: Concatenate Duplicate Data Without Sorting

jagan — Mon, 17 Nov 2014 05:51:43 GMT

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.

Re: Concatenate Duplicate Data Without Sorting

jagan — Mon, 17 Nov 2014 05:53:50 GMT

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.

Re: Concatenate Duplicate Data Without Sorting

tamilarasu — Mon, 17 Nov 2014 05:58:59 GMT

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

Re: Concatenate Duplicate Data Without Sorting

anbu1984 — Mon, 17 Nov 2014 06:00:13 GMT

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

Re: Concatenate Duplicate Data Without Sorting

tamilarasu — Mon, 17 Nov 2014 06:05:35 GMT

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