Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

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

cheunghm4532
7 - Meteor

Hi Alteryx Experts,

 

OMS 1.png

 

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

 

 

 

 

2 REPLIES 2
CharlieS
17 - Castor
17 - Castor

@cheunghm4532

 

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

cheunghm4532
7 - Meteor
Thanks Charlie! It’s much appreciated!
Labels