I have a table that looks like this, and wish to extract the <Product> Value into a new column:
ID | Name | Value |
1 | text_"product"_text | string value |
1 | text_"product" | number |
1 | "product" text | some value |
Id like to transform this to something like this:
ID | product | text__text | text_ | text |
1 | <product1> | string value | number | some value |
1 | <product2> | string value | number | some value |
Any examples, or ideas floating around?
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
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.
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")
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.
Hope this helps!
o.k. - saw what you're trying to do... I'll rework.
@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...
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.
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.
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.