cancel
Showing results for 
Search instead for 
Did you mean: 

Merge data from duplicate rows based on a unique column

SOLVED
Highlighted
AlexL
Meteoroid

Hello,

 

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

RecordService Date-TimeCustomerCustomer TypePrimary ReasonSub Reason
1Apr 4 1:55pmDavid PHome OwnerPurchaseShed
2Apr 4 2:00pmJohn SHome OwnerGeneral InfoShed Catalog
3Apr 4 2:00pmJohn SHome OwnerGeneral InfoGarage Catalog
4Apr 4 2:05pmMike RContractorGeneral InfoPower Tools Catalog
5Apr 4 2:15pmSarah CHome OwnerPurchaseDrill
6Apr 4 2:20pmBrian DHome OwnerPurchaseshed
7Apr 4 2:20pmBrian DHome OwnerPurchaseDrill
8Apr 4 2:25pmPeter AContractorPurchaseGarage

 

 

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

 

Ideal outcome-

 

RecordService Date-TimeCustomerCustomer TypePrimary ReasonSub ReasonSub Reason (2)
1Apr 4 1:55pmDavid PHome OwnerPurchaseShed 
2Apr 4 2:00pmJohn SHome OwnerGeneral InfoShed CatalogGarage Catalog
4Apr 4 2:05pmMike RContractorGeneral InfoPower Tools Catalog 
5Apr 4 2:15pmSarah CHome OwnerPurchaseDrill 
6Apr 4 2:20pmBrian DHome OwnerPurchaseshedDrill
8Apr 4 2:25pmPeter AContractorPurchaseGarage 

 

 

  • Alteryx Designer
  • Data Challenge
gc
Alteryx Partner

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

Quasar
Quasar

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

 

sub reason.png

AlexL
Meteoroid

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?

Quasar
Quasar

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
Meteoroid

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:

RecordService Date-TimeCustomerCustomer TypePrimary ReasonSub Reason
1Apr 4 1:55pmDavid PHome OwnerPurchaseShed
2Apr 4 2:00pmJohn SHome OwnerGeneral InfoShed Catalog
3Apr 4 2:00pmJohn SHome OwnerGeneral InfoGarage Catalog
4Apr 4 2:00pmJohn SHome OwnerPurchaseSaw
5Apr 4 2:05pmMike RContractorGeneral InfoPower Tools Catalog
6Apr 4 2:15pmSarah CHome OwnerPurchaseDrill
7Apr 4 2:20pmBrian DHome OwnerPurchaseshed
8Apr 4 2:20pmBrian DHome OwnerPurchaseDrill
9Apr 4 2:25pmPeter AContractorPurchaseGarage

 

 

Ideal Outcome:

RecordService Date-TimeCustomerCustomer TypePrimary ReasonPrimary Reason (2)Sub ReasonSub Reason (2)Sub Reason (3)
1Apr 4 1:55pmDavid PHome OwnerPurchase Shed  
2Apr 4 2:00pmJohn SHome OwnerGeneral InfoPurchaseShed CatalogGarage CatalogSaw
5Apr 4 2:05pmMike RContractorGeneral Info Power Tools Catalog  
6Apr 4 2:15pmSarah CHome OwnerPurchase Drill  
7Apr 4 2:20pmBrian DHome OwnerPurchase shedDrill 
9Apr 4 2:25pmPeter AContractorPurchase Garage  

 

Quasar
Quasar

How about the attached?

 

sample v2.png

AlexL
Meteoroid

 

Thanks Joe!!!

 

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