Hi all, I have collect a raw data file. Below is the example of the data abcdefapple12 chicago9 756USbanana21 New York20 891Canada I want to merge every 3 rows into one row: abcdefapple12chicago9756USbanana21New York20891Canada I am not sure if this is possible. Can anyone give me some suggestion? Thank you.

how to merge rows

Hi all, I have collect a raw data file.

Below is the example of the data

a	b	c	d	e	f
apple	12
		chicago	9
				756	US
banana	21
		New York	20
				891	Canada

I want to merge every 3 rows into one row:

a	b	c	d	e	f
apple	12	chicago	9	756	US
banana	21	New York	20	891	Canada

I am not sure if this is possible. Can anyone give me some suggestion? Thank you.

Workflow

Transformation

Join

Accepted answers

pedrodrfaria

Hi @luo

See below for a quick way of doing this, by simply concatenating the fields:

We use the Tile tool to assign the number of rows we need to combine and then the concatenate to do combine it all.

Pedro.

New Workflow10.yxmd

All comments

RolandSchubert

Hi @luo ,

I think, a sequence of Multi-Row Formula tools could do the job. You need one of these tools for each field to fetch the content of the previous rows (if not empty). The last row of a group (1-3, 4-6) will have all fields completed.

I've attache a sample workflow. Let me know if it works for you.

Best,

Roland

MergeRows.yxmd

danilang

Hi @luo

Here's a more dynamic workflow that will handle cases where the columns included in the data set may change

Start by adding a Group field to create groups of rows. Transpose the data rows with the Group as key and remove all rows with empty values. Crosstab back with Group as key to give you

Dan

MergeRows.yxmd

pedrodrfaria

Hi @luo

See below for a quick way of doing this, by simply concatenating the fields:

We use the Tile tool to assign the number of rows we need to combine and then the concatenate to do combine it all.

Pedro.

New Workflow10.yxmd

Qiu

@luo
A slight different one with others.

1229-luo.yxmd

luo

@Qiu @pedrodrfaria @danilang @RolandSchubert

Thank you all for helping me. I found out that my dataset has null value. So I have accepted @pedrodrfaria 's reply as solution. I know other solution will work too with a different dataset.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703