Merge rows

[#1]

The data:

ID, SEQ, NAME
1,1,A
1,2,A
1,3,A
1,4,A
1,5,A
2,1,B
2,2,B
2,3,B
2,4,B

Is it possible to merge the rows with same ID together following the sequence column? The result should be something like this:

ID, MergedName
1, A1A2A3A4A5
2, B1B2B3B4

[pover · Accepted solution]

Here's a possible solution.

Karl

[#3]

hi,

you can use the following code

LOAD ID, CONCAT(NAME&SEQ) as combined group by ID;

LOAD * INLINE [

ID, SEQ, NAME

1,1,A

1,2,A

1,3,A

1,4,A

1,5,A

2,1,B

2,2,B

2,3,B

2,4,B

];

thanks

[#4]

hi,

you can use the following code

LOAD ID, CONCAT(NAME&SEQ) as combined group by ID;

LOAD * INLINE [

ID, SEQ, NAME

1,1,A

1,2,A

1,3,A

1,4,A

1,5,A

2,1,B

2,2,B

2,3,B

2,4,B

];

i have used a precedent load(ie the first load statement in the above code) and concatenated SEQ and NAME by grouping them by ID.

thanks

[#5]

Hi Karl,

Sorry that I posted the wrong sample data... it should something like this:

<pre>ID, SEQ, NAME
1,1,AA
1,2,AB
1,3,AC
1,4,AD
1,5,AE
2,4,BD
2,3,BC
2,2,BB
2,1,BA

expected result:

ID, NAME
1, AAABACADAE
2, BABBBCBD

the sequence number shouldn't be inside the Name... sorry for the mistake.

[#6]

tauqeer,

Tried this method, but for some reason, it is not working correctly for some data (not concatenated in the sequence) I hope there is a method where I can force it to concatenate following the given sequence.

[#7]

Hi Khim,

The Solution given by Tauqueer is correct. It works fine in my system. for your new requirement or set of data, what you have to add is in the precedent load statement just add this code instead of the one which tauqueer gave.

Load ID, concat(name) group by ID;

load * from table.xls;

The code which Tauqueer gave was corect for the old set of data you gave and hope this works for your new set. Hope this helps you..

Thanks Joseph.......

[#8]

hi khim,

try the following code,it should work as per ur requirement.

tab1:

LOAD * INLINE [

ID, SEQ, NAME

1,1,AA

1,2,AB

1,3,AC

1,4,AD

1,5,AE

2,4,BD

2,3,BC

2,2,BB

2,1,BA

] ;

tab2:

noconcatenate load * resident tab1 order by ID,SEQ;

drop table tab1;

tab3:

LOAD ID,concat(NAME) as combined resident tab2 group by ID;

thanks

[pover]

Good solution, tauqueer.

[#10]

Thanks a lot Karl.

But i am still a novice compared to you and the other big guns in qlikview