How to merge customer accounts into one based one multiple criteria?

[cheunghm4532]

Hi Alteryx Experts,

I am currently cleansing my customer database, and I would like to know if this can be done by Alteryx.

In my company, one customer can register multiple accounts (VIP code) with the same telephone number or email address (which is a big problem). I have identified them, and have combined the related accounts for each VIP code in Column B, separated by comma.

I am now trying to identify one single "Main" VIP code for each customer based on the following criteria:

• Criteria 1: Column H, Last Sales Transaction Datetime
  • The VIP code with the most recent transaction datetime becomes the "Main" account. But if the VIP codes share the same datetime or are "NULL", move on to Criteria 2.
• Criteria 2: Column I, Last Warranty Register Datetime
  • The VIP code with the most recent warranty datetime becomes the "Main" account. But if the VIP codes share the same datetime or are "NULL", move on to Criteria 3.
• Criteria 3: Column J, Last Email Opened Activity
  • The VIP code with the most recent email open datetime becomes the "Main" account. But if the VIP codes share the same datetime or are "NULL", move on to Criteria 4.
• Criteria 4: Column L, Last Modified Datetime
  • The VIP code with the most recent modified datetime becomes the "Main" account. But if the VIP codes share the same datetime or are "NULL", move on to Criteria 5.
• Criteria 5: Column K, Join Datetime
  • The VIP code with the most recent join datetime becomes the "Main" account.

Basically, I would like to evaluate each row (VIP code) based on the above criteria, and return the "Main" VIP code for each row in a new column in the end of the table.

Thanks for your help in advance!

Regards,

Herman

[CharlieS · Accepted solution]

@cheunghm4532

I have attached a solution to this post. Please let me know if you have any questions. 

[cheunghm4532]

Thanks Charlie! It’s much appreciated!