Alteryx designer Discussions

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

convert month name to number

Asteroid

My month column is like below and I try to convert the month name to 2 digit number by using formula function.

Month
Feb
Mar
Apr

 

Here is what I keyed in, but I continue getting this error messages.  "Malformed function call" 

DateTImeFormat(DateTimeParse(ToNumber([month]), "%m")

 

How should I fix it?  Thank you.

Highlighted
Quasar

Hi @ll1100000

 

The Month column isn't a date format, so the DateTime functions won't work on it. Instead, you can use the following in your Formula Tool.

 

SWITCH([Month], Null(),

"Jan", "01",

"Feb", "02",

"Mar", "03",

"Apr", "04",

"May", "05",

"Jun", "06",

"Jul", "07",

"Aug", "08",

"Sep", "09",

"Oct", "10",

"Nov", "11",

"Dec", "12"

)

Highlighted

I'd probably go with @Philip suggestion but:

 

ToNumber(substring(DateTimeParse([month], "%b"),5,2))

Will work. Or if you have my function library:

Month(DateTimeParse([month], "%b"))
Highlighted
Meteoroid
 
Perfect Solution!
I can transform the date JANEIRO/2017
for 01/2017 remembering that my files are not in English but in Portuguese, I do not know if there is a translator, or I need to use a case for the solution

 

 

Highlighted
Meteoroid

Did you manage to use a switch function to recursively replace your month name inside de string field? (i mean a complete date string baldy formated as "Date 4 janv. 2019" for example).

Highlighted
Meteoroid

That's indeed pretty clean. Though Alteryx cannot recognise localized shorten month name.

 

I'm trying to use the dynamic rename action to create proper date format for some columns and I seem to be stuck with some nasty dirty imbricated replace function (and I mean 12 in a row) to replace things like Mars, Août or Déc. into proper integers.

Would you have any advise to have a cleaner code? Isn't there a replace function able to get an assoc as a parameter to replace a key by its value?

Highlighted
Moderator
Moderator

Hi @MatthieuArzel,

 

At this point localization in month names is not widely supported across our tools. My recommendation would be to use a Find Replace on the abbreviated months to convert them into either a numeric format or English format, at which point you can use the DateTime tool custom format feature to update your dates into a proper datetime format.

Highlighted
Meteoroid

Hi @KylieF 

 

That's a perfect solution. I do that already when i have to clean format inside my rows.

The trick here is that you can't use search and replace action then "feed" the dynamic rename action with the result.

I'm stuck in using only formula.

Labels