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

Doubt with strings, replacements and dates

Inactive User
Not applicable

Hi everyone,

 

I have a doubt with one issue that I found several times and I don't know yet how to solve it in the best and fastest way. I have a column with Dates but in a string format and the values are "Jan-2015", "Feb-2015", "Mar-2015", etc. And I want to change it to a Date field. 

 

My first idea was to create a formula using Replace. And replace Jan for 01, Feb for 02, etc. and after that use the DateTime tool. My doubt is: why I can't use a single formula statement like replace([String],"Jan","01") and replace([String,"Feb","02") ... and I have to use different statements for each replacement? There's a faster way to do it?

 

Thanks!

Pablo

 

 

18 REPLIES 18
Inactive User
Not applicable

Thanks to everyone! 

 

I have try all your proposals and all work perfectly. That's what I love of Alteryx: One problem and a huge number of possible solutions :-)

 

Thanks again,

Pablo

chris_love
12 - Quasar

Nice work @s_pichaipillai I like this approach. The find replace tool is a perfect tool for this. I'm not sure why you are using the TexttoColumns tool though as your macro doesn't go on to use them, so you can save two tools there.

s_pichaipillai
12 - Quasar

@Chris, you are right, i was preparing with some other direction and then changed mind to use find & replace 

yes it should work without the TTC tool :)

Nice catch :)

 

Thanks

Saravanan

yingxin
7 - Meteor

being frustrated with this type "string" to "datetime" conversion. Paul's formula works on "Feb 22, 2015", but return "Null" for "Feb 2, 2015". I can't figure out a way to pad the single digit of the day in the middle with "0", nor i don't want to make a table of 96 values like "Jan 1" by using Find/Replace. please help me out!

many thanks,

Yingxin

MarqueeCrew
20 - Arcturus
20 - Arcturus

Here is a sledge hammer approach:

 

Read your DATE (STRING) into a "Text to Columns" tool and parse [DATE] with a \s delimiter into 3 fields.

Formula Tool next for [DATE]

[DATE1]+' '+RIGHT(PADLEFT([DATE2],3,"0"),3)+[DATE3]

 

This will convert Feb 3, 2015 to Feb 03, 2015 and leave all other dates alone.

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
yingxin
7 - Meteor

thanks it works. I thought some ways even heavier. like extract the day, trim space, convert to int, if <10 then *10 and reverse... while....

 

jdunkerley79
ACE Emeritus
ACE Emeritus

While I like the TTC solution as clear what it is doing. You can use a REGEX_Replace formula to add the missing 0 and then DateTimeParse will work.

 

The REGEX expression for this would be something like:

REGEX_Replace([Text], "([a-z]{3})\s*([0-9]{1}),","$1 0$2,")

Passing this into the DateTimeParse formula gives:

DateTimeParse(
  REGEX_Replace([Text], "([a-z]{3})\s*([0-9]{1}),","$1 0$2,"),
  "%b %d,%Y")

I tried in a range of dates from 2014 to present and seems to work.

 

yingxin
7 - Meteor

Thanks! I was shy away of Regex becasue I can't fully grasp it. I understand the pattern part in Regex_replace, but not sure what ' "$1 0$2,") ' is doing. Thanks in advance if you or anyone here can enlighten me.

 

RodL
Alteryx Alumni (Retired)

To break this down...

REGEX_Replace([Text], "([a-z]{3})\s*([0-9]{1}),","$1 0$2,")

 

The '$' sign and a number are indicating the 'marked groups' in the initial expression. A 'marked group' is whatever is within the parentheses with the number after the "$' sign indicating the order they are in.

 

So it is saying take any 3 alpha characters (the [a-z]{3}) and make that the first marked group, followed by 'zero or more spaces' (the \s*), followed by a single digit (the [0-9]{1}) making that the second marked group, followed by a comma.

 

Now it take the contents of the first marked group, and put a space and a '0' before the second marked group followed by a comma. It is essentially pulling out whatever is in the first and second marked groups, eliminating the space, and then putting the first and second marked groups back together with a space and '0' in between.

 

It doesn't do anything when the day has two digits since the expression is only looking for (and working on) an string with a single-digit day.

 

Hope this makes sense.  

Labels