Replacing Month number with month name
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I have a data set with the field mth with values 1 through 12. I changed the data type to a string.
I'm trying to replace the mth number with a month name, so 1=Jan, 2=Feb and so on.
I've tried the switch, IIF and If then else formulas on current field and they change the mth field name to Jan. not what i'm after
the formula editor does not recognize my mth field as a variable.
Solved! Go to Solution.
- Labels:
- Expression
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Switch should work for you:
Switch([mth],Null(),1,"Jan",2,"Feb",3,"Mar",4,"Apr",5,"May",6,"Jun",7,"Jul",8,"Aug",9,"Sep",10,"Oct",11,"Nov",12,"Dec")
In the above I'm using the "mth" field as a numeric --- if it's a string, wrap each of the numbers in quotes.
What are you getting as a result, if any? Can you post some screenshots and/or sample data?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
You can hardcode the month number,names into a text input tool and then use Alteryx's find and replace tool to either replace your numerical month field or to just add a new column with the month name without getting rid of the numerical field. If you want to store the month number as a string, make sure that after creating the text input tool you use a select node to convert the month number into a string there as well.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
There are many options and the following is just one of them:
datetimeformat("2000-"+padleft(tostring([mth]),2,"0")+"-01","%b")
Because you can read any given date and output the month "%b" as the abbreviated month name, I chose this method because I'm lazy.
Cheers,
Mark
Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
When I do this, the result is the values of my mth field are changed to null.
in reply to @danrh
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Instead of replacing the field in the Formula tool, create a new field. I don't have your excel file so I can't test it out, but I would guess it's because you're outputting a string to a numeric field.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
perfect, thank you @MarqueeCrew
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@danrh thank you, this also works now when I output to new column using the formula tool.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you so much for the solution. How would you add year to the output by using same formula? Example: Apr 2017
Cheers,
John
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
connect a formula tool to the one used for the formula output to your month field
datetimeformat("2000-"+padleft(tostring([mth]),2,"0")+"-01","%b")
and concatenate the year and month and output to a new field
[mth]+" "+[yr]
where mth and yr are fields in your data
