Merge data from duplicate rows based on a unique column

[AlexL]

Hello,

I have a large data set that contains duplicate records but have unique sub reasons with the duplicate record like below:

Record	Service Date-Time	Customer	Customer Type	Primary Reason	Sub Reason
1	Apr 4 1:55pm	David P	Home Owner	Purchase	Shed
2	Apr 4 2:00pm	John S	Home Owner	General Info	Shed Catalog
3	Apr 4 2:00pm	John S	Home Owner	General Info	Garage Catalog
4	Apr 4 2:05pm	Mike R	Contractor	General Info	Power Tools Catalog
5	Apr 4 2:15pm	Sarah C	Home Owner	Purchase	Drill
6	Apr 4 2:20pm	Brian D	Home Owner	Purchase	shed
7	Apr 4 2:20pm	Brian D	Home Owner	Purchase	Drill
8	Apr 4 2:25pm	Peter A	Contractor	Purchase	Garage

I am looking for a way to automate merging/collapsing duplicate records while preserving the unique sub reason(s) in the process.

Ideal outcome-

Record	Service Date-Time	Customer	Customer Type	Primary Reason	Sub Reason	Sub Reason (2)
1	Apr 4 1:55pm	David P	Home Owner	Purchase	Shed
2	Apr 4 2:00pm	John S	Home Owner	General Info	Shed Catalog	Garage Catalog
4	Apr 4 2:05pm	Mike R	Contractor	General Info	Power Tools Catalog
5	Apr 4 2:15pm	Sarah C	Home Owner	Purchase	Drill
6	Apr 4 2:20pm	Brian D	Home Owner	Purchase	shed	Drill
8	Apr 4 2:25pm	Peter A	Contractor	Purchase	Garage

[gc]

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 :)

[Joe_Mako]

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 (#)"

[AlexL]

Hi Joe,

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?

[Joe_Mako]

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

[AlexL]

Hi Joe,

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.

Before:

Record	Service Date-Time	Customer	Customer Type	Primary Reason	Sub Reason
1	Apr 4 1:55pm	David P	Home Owner	Purchase	Shed
2	Apr 4 2:00pm	John S	Home Owner	General Info	Shed Catalog
3	Apr 4 2:00pm	John S	Home Owner	General Info	Garage Catalog
4	Apr 4 2:00pm	John S	Home Owner	Purchase	Saw
5	Apr 4 2:05pm	Mike R	Contractor	General Info	Power Tools Catalog
6	Apr 4 2:15pm	Sarah C	Home Owner	Purchase	Drill
7	Apr 4 2:20pm	Brian D	Home Owner	Purchase	shed
8	Apr 4 2:20pm	Brian D	Home Owner	Purchase	Drill
9	Apr 4 2:25pm	Peter A	Contractor	Purchase	Garage

Ideal Outcome:

Record	Service Date-Time	Customer	Customer Type	Primary Reason	Primary Reason (2)	Sub Reason	Sub Reason (2)	Sub Reason (3)
1	Apr 4 1:55pm	David P	Home Owner	Purchase		Shed
2	Apr 4 2:00pm	John S	Home Owner	General Info	Purchase	Shed Catalog	Garage Catalog	Saw
5	Apr 4 2:05pm	Mike R	Contractor	General Info		Power Tools Catalog
6	Apr 4 2:15pm	Sarah C	Home Owner	Purchase		Drill
7	Apr 4 2:20pm	Brian D	Home Owner	Purchase		shed	Drill
9	Apr 4 2:25pm	Peter A	Contractor	Purchase		Garage

[Joe_Mako · Accepted solution]

How about the attached?

[AlexL]

Thanks Joe!!!

You have saved me from a lot of frustration and time!