Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

How to remove repeating character from a string

biswajit86
7 - 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

18 REPLIES 18
mceleavey
17 - Castor
17 - Castor

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.



Bulien

afv2688
16 - Nebula
16 - Nebula

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

biswajit86
7 - 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

mceleavey
17 - Castor
17 - Castor

Ok, I'm now confused.

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

 



Bulien

biswajit86
7 - 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.

 

afv2688
16 - Nebula
16 - Nebula

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

mceleavey
17 - Castor
17 - Castor

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



Bulien

biswajit86
7 - 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

mceleavey
17 - Castor
17 - Castor

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.



Bulien

Labels
Top Solution Authors