RegEx/Parse Multi-coloumn Field Headings into dates

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...

Regex

Parse

Accepted answers

mmenth

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

All comments

Paulteryx

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?

mmenth

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

Regex example.yxmd

ammorris85

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..

suggested approach is:
- define anything ending in _1 as 1st of the month and anything ending _2 as 15th of the month

second though is:

just keep the columns ending _2

ammorris85

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

ammorris85

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

output part 2.JPG

any thoughts? If can fix that the solution is perfect

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703