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.
Solved! Go to Solution.
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
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
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.
@luo
A slight different one with others.
@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.