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?
1. I have a column of string that I need to parse out that looks like this
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.
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 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?
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.
"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= 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.
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!
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