Merge cells in a row

[#1]

Hi,

I need to merge several cells in a row into one cell, is this possible? I know it can be done by writing out the column names with & between them but this is a solution I'm trying to avoid due to the number of columns.

So my data looks something like the below with Result as the ultimate aim:

	TEST1	TEST2	TEST3	TEST4	TEST5	RESULT
Row1	Y	Y	N	Y	N	YYNYN
Row2	N	N	N	Y	N	NNNYN
Row3	Y	Y	N	Y	Y	YYNYY
Row4	N	Y	N	Y	N	NYNYN
Row5	N	Y	Y	Y	N	NYYYN

Is there any way to pick up all cells in a row with a column name beginning with TEST for example and merge them all together?

I need to do this in the LOAD script also.

[marcus_sommer]

Your table is a cross table which is rather unsuitable for many cases. You could count the fields per fieldcount() and create in a nested loop per fieldname() to reach such a result - but this is quite complicated and with a large table surely not very performant.

Better would be to transform your table per crosstable in a "normal" table und use then concat() as aggregation:

t1:

Load * Inline [

Row, TEST1, TEST2, TEST3, TEST4, TEST5

Row1, Y, Y, N, Y, N

Row2, N, N, N, Y, N

Row3, Y, Y, N, Y, Y

Row4, N, Y, N, Y, N

Row5, N, Y, Y, Y, N

];

t2:

CrossTable(Column, [Yes/No], 1) Load * Resident t1;

t3:

Load Row, concat([Yes/No], '') as Result Resident t2 Group By Row;

drop tables t1, t2;

[#3 · Accepted solution]

Hi, try this:

Concatenate columns on the basis of 'IND%'

[#4]

Thanks for the reply, Marcus!

I probably should have provided a better example as my table isn't a crosstable. The table in question has millions of rows along with 50+ columns which includes these "Y/N" columns which are the result of tests applied before the data is uploaded.

What I have done is give all of these result columns the same prefix, TEST, so that they can easily be identified. I was hoping to concatenate all of these columns into one column so that I can apply an additional check on the data to get a master result column.

Do you know if this is possible?

From what I've found online it seems that using & between the column names is the only way to go about this.

[#5]

Hi, Marcus.

This solution doesn't work properly, because sorted concatenation isn't supported:

The result of "Row1"  must be "YYNYN", but your script "NNYYY".

Sorry, but my english so so

[marcus_sommer]

Hi Alexander,

I'm afraid my englisch isn't really better ...

You are right in this case the sorting won't be correct - it needed an additionally load-step to create a numeric sorting-field for concat:

t1:

Load * Inline [

Row, TEST1, TEST2, TEST3, TEST4, TEST5

Row1, Y, Y, N, Y, N

Row2, N, N, N, Y, N

Row3, Y, Y, N, Y, Y

Row4, N, Y, N, Y, N

Row5, N, Y, Y, Y, N

];

t2:

CrossTable(Column, [Yes/No], 1) Load * Resident t1;

t2b:

Load Row, [Yes/No], num(KeepChar(Column, '0123456789')) as ConcatSorting Resident t2;

t3:

Load Row, concat([Yes/No], '', ConcatSorting) as Result Resident t2b Group By Row;

drop tables t1, t2, t2b;

- Marcus

[#7]

Excellent work Alexander! That script worked a treat.

Thanks to yourself and Marcus for picking this up!

[marcus_sommer]

Try simply both approaches. You could take this one what is faster or fit better to your data-model.

- Marcus

[#9]

you are welcome