community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
#SANTALYTICS

The highly anticipated Alteryx Community tradition is back! We hope you'll join us!

Learn More
SOLVED

How to remove repeating character from a string

Meteor

I have test cases to mark which string is invalid or valid. I used below in formula tool 

IF contains([Seller City], " Blank") then "Seller City is Blank"
ELSEIF (REGEX_Match([Seller City],"[a-zA-Z\-\.\~]+"))
then "Seller Country Valid"
ELSE "Seller Country Invalid"
ENDIF

After using above formula, It is showing invalid for  last one ABC DER, The output file after using formula is attached.

Please help me if there is any function or any way to achieve this

Below is the expected result

Avalid
AAInvalid
AAAInvalid
A AAInvalid
A AA AAAInvalid
A AA AAA AAAAInvalid
A12AInvalid
1234Invalid
A~Invalid
ÃBValid
$#%ABCInvalid
-Invalid
(!@#$%^&*(?) –Invalid
A-B-CValid
A.B.CValid
A@BInvalid
A#BInvalid
A$BInvalid
A%BInvalid
A^BInvalid
@Invalid
#Invalid
$Invalid
%Invalid
^Invalid
 Blank
ABCDValid
ABC DERValid

 

Thanks

Biswajit

Alteryx Certified Partner

Hi @biswajit86 ,

 

It looks as if you aren't handling the spaces and double spaces in the null fields.

Try the following:

 

IF contains([A], " Blank") then "Seller City is Blank"
ELSEIF (REGEX_Match([A],"[a-zA-Z\-\.\~\s]+[^\s\s+]"))
then "Seller Country Valid"
ELSE "Seller Country Invalid"
ENDIF

 

Let me know if that helps.

 

M.

Alteryx Certified Partner
Alteryx Certified Partner

Hello @biswajit86 ,

 

The formula should be like this:

 

IF contains([Seller City], " Blank") then "Seller City is Blank"
ELSEIF (REGEX_Match([Seller City],"[a-zA-Z\-\.\~\s]+"))
then "Seller Country Valid"
ELSE "Seller Country Invalid"
ENDIF

 

You had to add the space to be able to make it valid. This will also add as valid previous rows:

 

A AA

A AA AAA

A AA AAA AAAA

 

Which I really don't know how why you think they are different from ABD DER. You wouldn't be able to select then SAN FRANCISCO  (as an example).

 

Cheers

Meteor

Hi,

 

In the expected output result below should come as invalid but after applying the logic its coming as valid records

 

value remark
A AA AAAInvalid
A AA AAA AAAAInvalid

 

Thanks

Biswajit

Alteryx Certified Partner

Ok, I'm now confused.

Can you please show me what you're currently getting and what you want?

 

Meteor

current output attached in the sheet

 

A AASeller Country Invalid
A AA AAASeller Country Valid
A AA AAA AAAASeller Country Valid

 

1st one is correct but hte 2nd ,3rd row is not correct . last two also need to flag as invalid.

 

Alteryx Certified Partner
Alteryx Certified Partner

Hello @biswajit86 ,

 

I still don't get what you want. Could you please type the whole table as you wish it to be?

 

Is this how you want the results to be?

 

Avalid
AAInvalid
AAAInvalid
A AAInvalid
A AA AAAInvalid
A AA AAA AAAAInvalid
A12AInvalid
1234Invalid
A~Invalid
ÃBValid
$#%ABCInvalid
-Invalid
(!@#$%^&*(?) –Invalid
A-B-CValid
A.B.CValid
A@BInvalid
A#BInvalid
A$BInvalid
A%BInvalid
A^BInvalid
@Invalid
#Invalid
$Invalid
%Invalid
^Invalid
 Blank
ABCDValid
ABC DERValid

 

If it is, could you please explain what the difference between these 2 is. Because I don't know how to differentiate them.

 

A AAInvalid
ABC DERValid

 

Cheers

Alteryx Certified Partner

Hi @biswajit86 ,

 

Give this a go:

 

IF isempty([Seller City]) then "Seller City is Blank"
ELSEIF (REGEX_Match([Seller City],"\u|\u[B]|\u\u\u\u|\u\u\u\s\u+|\u.\u.\u"))
then "Seller Country Valid"
ELSE "Seller Country Invalid"
ENDIF

Meteor

In Test Cases tab have all the scenarios and expected results are there.

The single character also need to flag as invalid as it has no meaning.

Thanks  for your prompt help.

 

 

Thanks

Biswajit

Alteryx Certified Partner

Hi @biswajit86 ,

 

Assuming the "Test Cases" tab is the definitive list then the following gives the correct results:

 

IF isempty([Seller City]) then "Seller City is Blank"
ELSEIF (REGEX_Match([Seller City],"\u[B]|\u\u\u\u|\u\u\u\s\u+|\u.\u.\u"))
then "Seller Country Valid"
ELSE "Seller Country Invalid"
ENDIF

 

M.

Labels