I have a large data set that contains duplicate records but have unique sub reasons with the duplicate record like below:
I am looking for a way to automate merging/collapsing duplicate records while preserving the unique sub reason(s) in the process.
Go to Solution.
Go to Solution.
Would the Tranpose tool do that for you? That's what I would try. Or CrossTab if Transpose didn't work...I always get those backwards and end up using trial and error :)
How about the attached?
- Unique based on the fields that identify uniqueness (this combination of fields is used throughout the workflow)
- Multi-Row Formula on the duplicate records to have a Sub Reason Number, starting at 2
- Cross tab to make a field for each Sub Reason Number (using the 4 key fields)
- Join Multiple on the 4 key fields
- Dynamic Rename to get the field name format of "Sub Reason (#)"
Thanks for the response!
Unfortunately, I'm running v10.6 and cannot view your workflow.
(In process of upgrading to v11)
Can you share the snapshot with the expression used in the Multi-Row Formula icon?
Here it is as a 10.5(10.6) version, I used the technique from https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Adjusting-Alteryx-Files-for-Different-Versio...
Here is the expression:
IF IsNull([Row-1:Sub Reason Number]) THEN 2 ELSE [Row-1:Sub Reason Number]+1 ENDIF
Thanks for the downgrade tip! I have shared it with my co-workers
Your workflow works perfect for the sample that was provided.
Unfortunately, when I ran it through the entire data set, I notice that some records have more than 1 primary reason as well.
I tried adjusting the workflow to account for the additional column but was not successful.
You have saved me from a lot of frustration and time!