Merge Multiple rows into 1 row

Kushalv

Hello All,

I am extracting some data from PDFs and I have rows split into multiple row for random reasons.

Ex:

Column 1 Column 2 Colum 3

1 default public ip n/a
[Null] delivered [Null]

2 Forte 833
[Null] [Null] [Null]
[Null] [Null] [Null]

Problem: If Column 1 has nulls, all the rows with column 1 with null should merge into 1 row.

Like:

Column 1 Column 2 Colum 3

1 default public ip delivered n/a

2 Forte 833

3 ... ...
How can we perform this task using any tools in Alteryx.

Thanks.

Custom Tools

Parse

Accepted answers

All comments

Carolyn

Hi! The Multi-Row Tool is your friend. There's a few ways you could do it but this is what I did. I was treating the row with Col 1 filled in as the row that I ultimately wanted to keep, so I had the Multi-Row Tool look at the next row and then modify my current row.

You'd repeat for Column 3

Multi Row formula.yxmd

Kushalv

Hello Carolyn,

The use data I need to transform have multiple columns o merge as 1 not just column 2. So, not sure how this specific example works. Can you please elaborate.

Thanks

Carolyn

@Kushalv - I have a better solution! I used the Multi-Row Tool to update Col 1, instead of Col 2 and 3. Then I used the Summarize Tool to Group By Col 1 and Concatenate Col 2 and 3. From the limited example you provided, it works fine, but if you use this, you should make sure to test it more thoroughly to make sure the concatenate isn't doing anything weird

See lower path in the attached

Multi Row formula.yxmd

Carolyn

Ugh, gross

I think the Summarize with Concatenate option will work best for you then (in my last post). Multi-Row will let you look at multiple rows forward/back (it defaults to 1 row, but you can change it to a higher number). In your case, you'd set it to be 3 rows forward/back, but then if you have 4 rows forward, it'll lose that.

The Summarize with Concatenate will be more dynamic and should solve for what you want (and the Multi-Row to fill in Column 1 will still work even with multiple blank rows)

Edit: Though what's also interesting in your screenshot, it looks like the "Application Flow" has repeating words within Index #1. For ex, your row 3 has words that are fully contained within row 2 and row 4, so you don't really want to mash them all together. Hmm... that might be above my Alteryx knowledge for the best way to deal with them when the words also repeat - sorry!

flying008

Hi, @Kushalv

FYI.

录制_2024_07_09_10_01_34_407.gif

BTW, as @Carolyn said, If you want to remove some repeating word line by line, maybe you need use regex tool to get your want.

录制_2024_07_09_11_53_06_462.gif

Input	Merge	Output
default public ip delivered	default public ip delivered delivered with firewall - no with firewall - no application application	default public ip delivered with firewall - no application
delivered with firewall - no
with firewall - no application
application

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335