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. 

[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

[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]

Hey! You would do a new Tool for each column. 

 If you have a bunch of columns, then I'd probably involve a Transpose Tool but I'd need to map that one out

[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

[Kushalv]

Thanks Carolyn, 

Also, this expressions is only looking into next row only, but  my data have random splits where some are split into 1 and some into 3. 

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

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.

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