Moving the right text to the right cell

[Chalsea]

Hello community,

I'm pretty new to Alteryx and would love some help!

Maybe this is very simple, but somehow i cannot seem to find the solution: how can i move specific text from one column to another column within a workflow if those values are out of order? Also, how can I do it in bulk if there are many of the scenarios like this? 

For example, 

1. I have a column of string that I need to parse out that looks like this

East/Video/YouTube/20%Off/Toy/LA/Yes/NA/Small/Delivery

2. once it's parsed out, see below, however, some of the value is out of order, for example,

a. under Product column "20% Off" should belong to Price column,

b. and under Price column "Toy" should be belong to Product column,

c. under Promotion "LA" should be long to Market column

d. under Market column "Yes" should belong to Promotion column

e. under Size column "NA" is not useful and can be eliminated

f. under Delivery/Pick up column "Small" should be shifted to Size column

h. under Value 1 "Delivery" should be moved to Delivery column

Region	Source	Site	Product	Price	Promotion	Market	Size	Delivery/Pick up	Value 1	Value 2	Value 3
East	Video	YouTube	20% Off	Toy	LA	Yes	NA	Small	Delivery

ideally I need them to be like this.  

Source	Site	Product	Price	Promotion	Market	Size	Delivery/Pick up
Video	YouTube	Toy	20% Off	Yes	LA	Small	Delivery

I've attached the excel file and the workflow. Can someone show me how I should do it in the workflow? 

Here is an illustration of the messy data vs cleaned data. Ideally I would love to move all the color blocks that doesn't match the first row to its according color. 

Before:  

After: 

If anyone can send me into the right direction that would be awesome!

Thanks in advance! 

Chalsea

[thedr9wningman]

I'm not clear if your column order is variable or not from your post. But look into two things.

The REGEX parsing tool. It will let you define the boundaries of your parsing. such as: East/(.*)/(.*), etc. It's an awkward thing to manage (the syntax is crazy), but once you get it down, a very powerful way to recognise patterns and parse strings. 

If you want to REORDER columns, use a SELECT tool. Then you should be able to parse the way you wanted to.

If you want to copy values from one column to another, just use a FORMULA tool. Choose your column and use the [square brackets] to choose the value you want from the row. 

For this particular problem, it seems that you need to make sure your column definitions are consistent and your parsing algorithm is looking to map things appropriately. 

[Simha]

Hello @Chalsea , Here's a way to do that:

[Chalsea]

Thank you so much for the prompt reply!

It's not just a column issue but we have five different people inputting that tags and created lots of variations.

So there will be hundreds if not thousands of this similar cases show up. The reorder of a column won't be ideal as not all cases needed to be reorder just some of the values. I think the REGEX will be the most powerful way to solve this but I'm pretty new to REGEX. Do you have any recommendations in how I can learn from the scratch? Is any REGEX tutorials out there can apply to the REGEX in Alteryx? 

Thanks again!

Chalsea

[Chalsea]

Thanks for the prompt reply! I got the parse part down but not the rearranging part. In your example below is only showing parsing. I will still need to figure out how to move the right value to the right cell. Do you have any thoughts in how to reorder specific values? 

[thedr9wningman]

Pop open the REGEX tool. There is help and documentation in the tool! 

[Simha]

No problem @Chalsea . Reorder is done in the last tool (Select tool) in my solution. If your incoming data has a varying length of fields (meaning varying number of delimiters '\'), then the incoming data should have some indicator to tell the workflow as to which data belongs to which field.

If the incoming data is fixed in number of fields (data length can vary), then my solution above should work. Please let me know if you have additional questions. 

[yalmar_m]

Hi @Chalsea,

I think the select tool is the most suitable for you!

Kind regards,

Yalmar

[thedr9wningman]

"So there will be hundreds if not thousands of this similar cases show up. The reorder of a column won't be ideal as not all cases needed to be reorder just some of the values. "

In order to get a data solution, you will need to solve the process solution of variability. In essence, you can only programme in so much variability. Create some data standards, otherwise, I feel you're going to be fighting an uphill battle. 

Essentially, you need a consistent mapping. 

($1)/($2)/($3)/($4)

$1= Column 1

$2= Column 2, etc.

If you get data that is ($1)/($3)/($9), you'll never be able to automate or parse that.

If your data set is small enough, and you know which records are problems, you can filter them out and use a manual Formula tool to add new columns and overwrite columns. 

Example:

1/2/3/4/5

1/2/3/4/5

1/3/8/4/6

Add a recordID to those, filter out for the third one, and use your formula tool to realign the data manually. Or, you can add a Scenario column, as you mentioned, split out the REGEX or parsing order based on that Scenario column, and use a different set of parsing agents in each of those 'streams' of data. Scenario 1 will use Scenario1Parsing logic, while another will use its own. After you get each scenario squared away and the columns matching, you can UNION everything together. Another thing you can do is just rename the columns in each scenario using the SELECT tool. 

If you have any other way to parse these or name the order that you're receiving that will help (and will make your workflow more complicated). 

Best of luck. Just be glad you're not doing this in Excel!

[Chalsea]

Thanks for the guidance! I think I somewhat getting the idea but still would love a step by step example in the workflow. I updated the post with the workflow (initiated). And yea, I've been doing the manual excel work for many months and can't wait to solve this! XD