Merge rows from same column
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi Alteryx Community,
I’m working on a project where I need to merge rows with similar descriptions into a single row, while combining their numerical values. For example, I have rows like:
- "Allowances for impairment losses on financing"
- "and advances, net"
I want to combine these rows into one, so the resulting row will include both descriptions and the summed numerical values from each row.
I’ve been considering using regex to identify and group similar descriptions, but I’m struggling to get it right.
Could anyone provide guidance or suggest an approach to achieve this? I’m open to ideas on how to effectively use regex or any other methods in Alteryx to accomplish this task.
Thank you!
Attached is the input and expected output
- Labels:
- Regex
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@hari24 - An easier way might to create a mapping field for the columns you want to combine. There are multiple ways you can add the mapping: Text Input + Join, Formula, etc.
Once the rows are mapped, you can use the summarize tool to concatenate the text and sum the amounts. See attached.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hey @hari24 Another approach -
I see your part of the string in F1 column is moving to next row along with the values and if that is the case always, you can use multi-row formula to correct F1 column. Please follow below steps -
1. Filter out the row which contains Adjust for value in F1 which you can union later if you want with all data.
2.In multi row formula, click on update existing column and choose F1.
3. Write the formula - IF !Isnull(F3) THEN ROW-1:F1 + F1 ELSE F1 ENDIF. This will concatenate the data from last row and current row.
4. Lastly use filter tool to remove rows where F3 is null.
These steps will provide you the expected output. Let me know if this works.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
