Hi, trying to find a solution to this problem:
I have same pattern column headings come in each month. I need to drop the R (can do with clean easy enough), split _# again easy enough in text to columns but I also need to turn the #### into a workable date
1501 represents Jan 2015
1502 Feb 2015 and so on
I'm new to Alteryx and struggling with a regex to work where is does all of the above once or at least can turn parse for the #### in to dates...
Solved! Go to Solution.
Hi, one question. How do you want to handle the duplicate fields? It seems you have two columns for each month. Do you also want the field names to simply be changed to year, month?
Hi @ammorris85,
You could try the attached to clean your column names. The one assumption I did make was that all dates will be in the 2000's. If that's not true you might have to tweak it a bit.
Best,
mmenth
thanks for the prompt response, and great question. One I am debating myself.
my first thought was:
retain both variants as one is report 1 of the month and the other report 2 of the month but how..
second though is:
just keep the columns ending _2
looks like this could work with the small amend of using the _1 or _2 to define as 1st or 15th
Let you know how it fits
thank you
Hey
So gave your workthrough a good run over and came out with the following output where column 4 and 5 have taken on the date name and lost their original names and where the very last two coloumns show names 'Field_42_4' 5 respectively where these should be the dates
any thoughts? If can fix that the solution is perfect
Hmmm are there fields in your input file that should not have a date as the column name? If so I would remove them first and rejoin them downstream. Here are some screenshots of one way to do it:
First add two select tools after your input tool:
Have the fields that have date columns checked in the top select tool, and all other fields checked in the bottom select tool:
Then you can rejoin them at the end by selecting 'Join by Record Position'
Let me know if that does it for you!
Best,
mmenth
Thanks for your help, it did the job perfectly with a couple of additions to achieve the final outcome (see above)
This will save my team 2/3 hours at least on a monthly basis!
Thankss again
Glad to hear it!