Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

REGEX Question, i believe..

Dan5
8 - Asteroid

I have a table that looks like this, and wish to extract the <Product> Value into a new column:

 

IDNameValue
1text_"product"_textstring value
1text_"product"number
1"product" textsome value

 

Id like to transform this to something like this:

IDproducttext__texttext_ text
1<product1>string valuenumbersome value
1<product2>string valuenumbersome value
     

 

Any examples, or ideas floating around?

7 REPLIES 7
fmvizcaino
17 - Castor
17 - Castor

Hi @Dan5 ,

 

Does your product name/code have any kind of rule? For example, number of characters, no numbers, 3 numbers and 4 letter.

 

Best,

Fernando Vizcaino

Dan5
8 - Asteroid

I wouldnt say there is a rule, however there is a discrete list of values that i could include in the code. There are only about half a dozen products to account for. 

Kenda
16 - Nebula
16 - Nebula

Hey @Dan5 

 

I think this will at least get you close to what you're looking for. 

 

First, I did use some RegEx to pull out the different pieces of text you were looking for.

 

I created a product field with this expression:

REGEX_Replace([Name], '(.*)?\"(.*)\"(.*)?', "$2")

This will pull out the text between double quotes. This works by assuming there are three groups of text (signified by the three pairs of parentheses). The outside two groups are optional. Then it says to only keep the second group, here it is the one surrounded by the quotes. 

 

I also created a text field that basically did the opposite of the first expression and keeps everything except what is inside the double quotes:

REGEX_Replace([Name], '(.*)?\"(.*)\"(.*)?', "$1$3")

 

Kenda_0-1617118157216.png

 

Lastly I used a crosstab tool to put the text fields as the headers and the product fields as the values. This is the bit that you might need to change based on your use case and data.

Kenda_1-1617118370806.png

 

 

Hope this helps!

apathetichell
18 - Pollux

o.k. - saw what you're trying to do... I'll rework.

Kenda
16 - Nebula
16 - Nebula

@Dan5   I realized after the fact that this is actually probably how you'd like to configure your crosstab tool based on your original post...

 

Kenda_0-1617118746852.png

 

Dan5
8 - Asteroid

thanks for the quick workflow!

 

The double quotes were actually just to illustrate the location of the product value. There is no single syntax rule, which may make regex the wrong approach. 

 

They would be something like marketsize_trains_2025, pipeline_trains, buying motion trains. as examples of variation. 

Kenda
16 - Nebula
16 - Nebula

Gochya @Dan5. Hopefully this will get you closer than the previous approach...

 

Since there is a small, finite list of values for your products, I recommend putting that list in a text input tool. Then, use a find replace tool to append that field to your other data. 

 

Kenda_0-1617119541871.png

 

 

From there, you can create your text field by just getting rid of that product piece using that new field you just appended:

Replace([Name], [Product], "")

 

Then ending with a crosstab, as before.

Kenda_1-1617119618419.png

 

 

Labels