How can I make my duplicate values null?

[KonaCantTalk]

I was looking throughout the community thinking this was already asked, maybe it has been and I just couldnt find it, but I was curious to know if there was a way to make duplicate values show as null. 

For example, lets say I have customer ID numbers as follows: 

Customer ID #

12345

45832

78043

92378

72213

12345

I would like to have one of these customer ID numbers switched to null. Not the whole row of data just the one cell, or Customer ID #.

Thank you! 

[AndrewS]

Hi @KonaCantTalk 

You could try a multiRow - I've attached a workflow which should help.

If you have more than two customer IDs you would need to amend the formula to:

if [Customer ID #] = [Row-1:Customer ID #] then null()
elseif isnull([Row-1:Customer ID #]) then null() else [Customer ID #] endif

EDIT - attached a second option using a unique tool. Too many choices!

[MarqueeCrew]

I'd approach this by summarizing. First put a record Id on each record. Next summarize by grouping on your customer id and getting the first or minimum recordid. Now join this data back to your original data on customer id. 

a formula could now be built to null ids 

if recordid = min_recordid then custid 

else null()

endif

cheers,

mark

[KonaCantTalk]

@MarqueeCrew,

Do you have a screenshot or could you be a little more descriptive with this? I think this is the direction I need to head in. I tried the other solution but I have multiple duplicates of the same record and the first solution seems geared towards if you only have one duplicate. 

Also, for what it is worth, I already have a rather lengthy workflow going and have already spent some time cleaning my Customer ID column so if there is a way to do this without having to start from scratch that would be ideal.

I am new to this so thank you for your help!

[MarqueeCrew]

I'll build one now for you. Hold on. 

[MarqueeCrew]

@KonaCantTalk ,

I've performed this with both a FIND REPLACE and a JOIN.  The FIND REPLACE doesn't require a SORT.

Thanks for the opportunity to solve another post. 

Cheers,

Mark

[Nezrin]

What about filtering out duplicates from the unique tool and either use a formula tool to replace to null or use find and replace ?

[MarqueeCrew]

That is clever. I like that too. It is straight forward. 

for designer today, we should both be given accepted solutions. If however I'm the future this should run on an e2 engine, I'm not certain that the unique tool would promise to get the first occurrence. 

cheers,

 mark

[KonaCantTalk]

I used the unique tool to filter out the duplicates originally. I just couldn't figure out how to add them back into my data set as null once I identified the duplicates. Thank you all for your solutions, I will try them shortly and let you know how it goes. 

[AndrewS · Accepted solution]

Hi @KonaCantTalk , 

I think the second workflow I attached yesterday might have gone unnoticed, it included a potential solution which used a unique tool to split, change the duplicates to Null and then Union back the dataset. I added in an additional duplicate value to test against more than 1 duplicate. Hope this helps.