Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

how to merge rows

luo
8 - Asteroid

Hi all, I have collect a raw data file. 

Below is the example of the data

 

abcdef
apple12    
  chicago9  
    756US
banana21    
  New York20  
    891Canada

 

I want to merge every 3 rows into one row:

 

abcdef
apple12chicago9756US
banana21New York20891Canada

 

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

5 REPLIES 5
RolandSchubert
16 - Nebula
16 - Nebula

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

danilang
19 - Altair
19 - Altair

Hi @luo 

 

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

 

MergeRows.png

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

 

o.png

 

Dan

pedrodrfaria
13 - Pulsar

Hi @luo 

 

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

 

pedrodrfaria_0-1609189774154.png

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.

 

Qiu
20 - Arcturus
20 - Arcturus

@luo 
A slight different one with others.

1229-luo.PNG

luo
8 - Asteroid

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

Labels