regular expression to remove duplicates

[juan1]

Dear all, 

I have been trying to wrap my head around regular expressions for a problem that I have. I have reviewed similar posts but none worked. 

The problem is that I have a set of values that were duplicated from a text to columns before. I wanted to separate into rows strings that had "/". I tried using the following RegEx

^(.*)(\r?\n\1)+$

and replacing with \1. 

This is my data; I want to remove one of the duplicate "3"s.

Value	total
5180331	3
5180331	3
5180331	85
5180332	3
5180332	3
5180332	20

Desired output: 

Desired output
Value	total
518033	3
518033	85
518033	3
518033	20

Thank you in advance for your time and help.

Best,

Juan1

[MarqueeCrew · Accepted solution]

@juan1 ,

 have you considered using the SAMPLE tool?  Group by value and skip first record?  

https://help.alteryx.com/current/designer/sample-tool

then you can use the left () function to remove the last digit?

 Cheers,

 mark

[echuong1]

I am a bit confused by your description and example. Are you just trying to remove the duplicates based on the Total-Value pairs, and then trim the last digit of the Value?

If so, you can use a unique with both total and value checked. This will remove the duplicates and your unique values will come out of the U output. From there, you can use regex to trim the last number. You can also achieve this with a formula tool.

[Ben_H]

Hi @juan1 

The only thing I have to add is to ask why your text to columns introduced duplicates in the first place?

Wouldn't it be easier to fix that than have to have an extra step?

Regards,

Ben

[jf97hernandez]

@MarqueeCrew 

Thank you for your suggestion! The sample tool worked marvelously; I solved it without the need of the LEFT function. 

For anyone looking at this in future searches:

My data totaled 50 so I chose N=50.

Best,

Jf97hernandez

[juan1]

@Ben_H

I had used a text to columns before and the values have distinct categorizations. Imagine it like the same transaction number for a credit card purchase in which i divided between the cost and the credit card usage fee. That is why I had duplicates

So, before getting to this, I used the text to columns to split into rows to clean some data and had the original value more than twice (which was what I needed). In the end I got rid of the unnecessary duplicates using the sample tool.

Best,

Juan1