Inspire EMEA 2022 On-Demand is live! Watch now, and be sure to save the date for Inspire 2023 in Las Vegas next May.
Announcement | We'll be doing maintenance between 2-3 hours, which may impact your experience. Thanks for your patience as we work on improving the community!

Alteryx Designer Discussions

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

Merging rows with data in different fields

jrobiso2
8 - Asteroid

I have 30K of rows similar to this:

 

NameUIDField1Field2Field3
hostname 1blah1x  
hostname 1blah1 x 
hostname 1blah1  x
hostname 2blah2 x 
hostname 2blah2x  
hostname 2blah2   
hostname 3blah3  x
hostname 3blah3 x 
hostname 3blah3x  

 

What I need to do is have the data end up like this:

 

hostname 1blah1xxx
hostname 2blah2xxo
hostname 3blah3xxx

 

I don't seem to be getting anywhere, either with Summary, or Multi-Row Formula, etc. I KNOW this should be easy, but the solution just isn't coming to me.

Help?!?

4 REPLIES 4
MilindG
11 - Bolide

I have attached couple ways. See if that works

jrobiso2
8 - Asteroid

Nice. Unfortunately, my boxes don't show that x can exist for the same field in more than one row, so the concat results in x,x,x, etc.

 

I'm thinking perhaps Max instead of Concat?

jrobiso2
8 - Asteroid

Got it! 

Formula to turn any "N/A" entry in the 3 Fields to blank, then a Summarize, grouping on all columns except those 3 . . . those 3 I use "Max" in the Summarize tool. (My Field values are 3 letter acronyms, hence the need to remove any "N/A" values, as those are 3 characters - so blank those to make Max work).

Thanks for the ideas in your Merge file!

MilindG
11 - Bolide

Awesome, Glad you found the solution. Cheers!

Labels