Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

RegEx/Parse Multi-coloumn Field Headings into dates

ammorris85
6 - Meteoroid

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

 issue.JPG

8 REPLIES 8
Paulteryx
11 - Bolide

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
11 - Bolide

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

ammorris85
6 - Meteoroid

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
6 - Meteoroid

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
6 - Meteoroid

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

outputpart1.JPGoutput part 2.JPG

 

any thoughts? If can fix that the solution is perfect

mmenth
11 - Bolide

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:

screen1.PNGHave the fields that have date columns checked in the top select tool, and all other fields checked in the bottom select tool:

 

screen2.PNGscreen3.PNG

 

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

 

ammorris85
6 - Meteoroid

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

mmenth
11 - Bolide

Glad to hear it!

Labels