topic Merge rows in QlikView

Merge rows

Wed, 12 May 2010 03:09:59 GMT

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

Merge rows

pover — Wed, 12 May 2010 04:18:31 GMT

Here's a possible solution.

Karl

Merge rows

Wed, 12 May 2010 06:54:51 GMT

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

Merge rows

Wed, 12 May 2010 06:59:42 GMT

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

Merge rows

Wed, 12 May 2010 08:55:00 GMT

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.

Merge rows

Wed, 12 May 2010 09:00:19 GMT

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.

Merge rows

Wed, 12 May 2010 09:42:40 GMT

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.......

Merge rows

Wed, 12 May 2010 10:37:49 GMT

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

Merge rows

pover — Wed, 12 May 2010 15:38:49 GMT

Good solution, tauqueer.

Merge rows

Wed, 12 May 2010 18:20:12 GMT

Thanks a lot Karl.

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

Merge rows

johnw — Thu, 13 May 2010 01:00:09 GMT

The concat() function already supports giving it a sequence, so there's no need for additional steps for the sort.

LOAD ID,concat(NAME,'',SEQ) as NAME
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
]
GROUP BY ID;

Merge rows

Thu, 13 May 2010 02:43:59 GMT

Hi tauqueer,

I tried this method, but for some unknown reason, it doesn't work correctly for my real data. The merged result is still not in the sequential format.

Merge rows

Thu, 13 May 2010 02:46:29 GMT

I edited Karl's script a bit and it is working fine now! Thanks Karl for the code!

Thanks to all for all the suggestions!