Removing duplicates with If conditions

Hi All

This should not be difficult but for the life of me I cannot figure it out. I have almost a million rows of data that is joined between different systems. That part I have set up.

I have concatenated the unique identifiers to determine if the group has different settlements. If it has all the same settlement than I need only the first line and if they have different settlements then all need to be brought over. I am attaching a small sample the last input data (is an example of what the output should look like if I could get it to work).

The input is 29 and the output should be 17

I have had to do this in excel in batches but the output is usually around 30K rows of data vs the almost 1M.Any help would be appreciated.

Consolidated settlement file.yxmd

Workflow

Datasets

Accepted answers

AbhilashR

Hi @MelGibson, I put together a quick solution to replicate your output. Let us know if the approach is flexible enough on your larger dataset.

I identify the first record for rows with repeated Settlement number by numbering the rows, and identifying the first record in it (min RecordID).

Hope this helps.

Consolidated settlement file_modified.yxmd

All comments

AbhilashR

Hi @MelGibson, I put together a quick solution to replicate your output. Let us know if the approach is flexible enough on your larger dataset.

I identify the first record for rows with repeated Settlement number by numbering the rows, and identifying the first record in it (min RecordID).

Hope this helps.

Consolidated settlement file_modified.yxmd

MelGibson

Hi @AbhilashR I tested on a smaller set and it worked. I am going to build it into my join and test it now. I will check back and if all is good I will accept as the solution. This is awesome though and I am going to have to learn Alteryx better as we just got our licenses. I look forward to seeing what I can do in it. Thanks again and I will be back when I put it with the 1M dataset.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703