2022.1.1.30569 Patch Release Update

The 2022.1.1.30569 Patch/Minor release has been removed from the Download Portal due to a missing signature in some of the included files. This causes the files to not be recognized as valid files provided by Alteryx and might trigger warning messages by some 3rd party programs. If you installed the 2022.1.1.30569 release, we recommend that you reinstall the patch.

Alteryx Designer Discussions

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

How to remove unwanted separators/delimiters from the beginning of a string

daophuongtrinh
8 - Asteroid

Hello everyone,

 

I'm having some strings as follow:

, 5536463784, 5536643484

, , 5536643383, 5536644436, 5536644990

, , , 5536630664, 5536630770, 5536633864, 5536641613

, , , , , , , , , , 5536634549, 5536635255, 5536636403, 5536636437, 5536636540, 5536636541, 5536636718, 5536637694, 5536637695, 5536642011, 5536642012

 

These strings are results of concatenating using ", " as separator. I would like to ask whether we can use RegEx or any other idea to remove the unwanted delimiters, so the output can be like this:

5536463784, 5536643484

5536643383, 5536644436, 5536644990

5536630664, 5536630770, 5536633864, 5536641613

5536634549, 5536635255, 5536636403, 5536636437, 5536636540, 5536636541, 5536636718, 5536637694, 5536637695, 5536642011, 5536642012

 

Thank you very much

Trinh

10 REPLIES 10
JosephSerpis
16 - Nebula
16 - Nebula

Hi @daophuongtrinh you can use Regex to solve this.

 

REGEX_Replace([Field1], "^\,*(\d+.*)", "$1")

This will look for a comma or commas e.g. ^\,*  at the begining then it will find a digit or more aka eveything else and it will replace it with that last part.

daophuongtrinh
8 - Asteroid

Hi @JosephSerpis 

 

Thank you very much for your suggestion. However the beginning delimiter is comma with space, and I have tried to add "\s" to the expression but somehow it didn't work.

 

Could you help take a look at the workflow I attached?

JosephSerpis
16 - Nebula
16 - Nebula

Hi @daophuongtrinh amened the regex to work in this scenrio and also found a non-regex way to solve this as well.

Qiu
19 - Altair
19 - Altair

@daophuongtrinh 
I think we can not restrict the order of comma and space.

REGEX_Replace([Field1], "^[\,\s]*(\d+.*)", "$1")

Asd.PNG 

DawnDuong
13 - Pulsar
13 - Pulsar

Hi @daophuongtrinh 

one non-regex way is to use the Text to Column tool. The way to visualize the solution is that you currently have concatenated strings which also include “blank” values which are concatenated. So you want to “decompose” them, take out the blank values, and then only concatenate the remaining non-blank values. 
this can be done in 4 steps:

1) use a RecordID tool to enable you to recombine the parts without mixing up the original strings

2) use a TextToColumn tool, but choose out put to rows and with comma as the delimiter. After this step you will have many rows than before, each row contains either a blank or a desired string.

3) filter tool: to filter out the blank. Note: do experiment with isempty() and isnull() formula to see how their behaviours differ.

4) use the summarize tool to concatenate the “broken up” parts. Do remember to group by the RecordID field so that you get back the original string combinations minus the blanks.

i did not attach a workflow, hopefully the above schematics gives you enough pointers to reach the desired outcomes.

cheers

dawn 

daophuongtrinh
8 - Asteroid

Wow amazing :D

 

Thank you @JosephSerpis and @Qiu 

daophuongtrinh
8 - Asteroid

Hi @DawnDuong 

 

Thank you for your idea, however the example I have here is only a column in a dataset and filtering out the blank rows would make the other numeric columns to be incorrect so I think we cannot go with this option though.

DawnDuong
13 - Pulsar
13 - Pulsar

Hi @daophuongtrinh 

You need do the first 2 steps :)

do explore the uniqueID tool and the TextToColumn Tool - these are really handy 

dawn 

Qiu
19 - Altair
19 - Altair

@daophuongtrinh 
Glad to be any help. 😁

Labels