Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.

Alteryx Designer Desktop Discussions

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

Merge data from duplicate rows based on a unique column

AlexL
7 - Meteor

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 

 

 

7 REPLIES 7
gc
9 - Comet

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
12 - 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
7 - Meteor

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
12 - 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
7 - Meteor

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  

 

Joe_Mako
12 - Quasar

How about the attached?

 

sample v2.png

AlexL
7 - Meteor

 

Thanks Joe!!!

 

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

Labels