Complex removing duplicates

Hello Community,

here's a hard one for you: I have a dataset with two columns with account numbers. Whenever either of the account numbers is the same for two rows, I need to establish a charging rule. Accounts are charged -1 if they are inactive for each record but they are charged only once if they are active (no matter how many rows there are that fulfill this). If the records are both mixed between active and inactive, the account is charged -1 for each inactive, and just 1 for each active.

Do you have any easy way to do this? Ideally, I'd have a new column "charge code" with 1 or -1 for each records, obviously excluding doubles as stated above..

Thank you!

Sample_Alteryx Community.xlsx

Best Practices

Accepted answers

wdavis

No problem, I have attached a workflow that hopefully solves the two issues you mentioned.

The Tranpose tool puts all of your account numbers into one field so that you only need to use one Unique tool. If the data needs to be back in the format of the 2 different fields, then we can use a cross-tab tool to achieve this.

For the Null values, I have added some into the dataset in the workflow and then used a Filter tool to remove these out, so they are not included in any unique calculation.

Let me know if this works for you.

Thanks

Will

DuplicateRemove.yxmd

All comments

wdavis

Hi @m_hohaus

Would you be able to share some sample data of what the inputs for these would look like, then we can build a workflow to replicate your challenge.

In terms of the active/inactive status, is there anything in the dataset to determine this? Or is it based on the number of rows with the same account number?

Thanks

Will

LukeM

Hi @m_hohaus

The first step I would suggest is to download the Crew Macro pack as there is a tool in their called Only Unique. This will filter and provide you for the Account Numbers where you have duplicates.

I'd then create a column that flags if they are active. Then you can Summarize and count how many are active for that account number and then build you logic off that, using the formula tool.

As my esteemed colleague @wdavis says, it may be easier if you post a dummy example if you are still stuggling.

Luke

m_hohaus

Thank you Will! I have shared a sample of how the data would look like. The active/inactive status is based on the column "status" - this is attributed to each e-mail in the system. Multiple e-mails can be linked to one account (which is why we want to charge only 1 to the account, and not for each e-mail address).

Thank you for your help!

m_hohaus

Hello, am downloading this now and will let you know! Thank you for your support!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703