There are three columns in my raw data, how can I change the Month from int 16 to string? Thank you.
Year | Month | POS |
2017 | 7 | 100 |
2017 | 8 | 258 |
2017 | 1 | 500 |
2018 | 9 | 658 |
So it will show like below?
Year | Month | POS |
2017 | Jul | 100 |
2017 | Aug | 258 |
2017 | Jan | 500 |
2018 | Sep | 658 |
I guess I need something like "iff[month] = 1, then Jan, else Feb?
Or is there any other way do to this beside using the transpose function?
Solved! Go to Solution.
You could use this formula in the Formula tool:
DateTimeFormat(DateTimeParse(ToString([Month]),"%m"),"%b")
Converts the field to a string... then turns it into a standard DateTime value with DateTimeParse formula ("%m" being the format for month as a number)... then formats it as a 3-letter month name using DateTimeFormat ("%b" being the format for 3-letter Month name).
FYI - you'll likely want to create a new field in the formula tool rather than reusing the existing Month field if it's set as type Int16, as the format using words will result in a Null value if kept in the same field.
Does that help? :)
NJ
... and in case the DateTime formatting is as much of a mystery to you as it was (is) to me, this post has some good info/a link to the formats used by both DateTimeParse and DateTimeFormat. :)
Hi Nicole;
Thank you very much for your solution. This formula works!
But for my fault, I just realized that for financial purpose, our financial calendar is different from the regular calendar because the first month is Feb
Therefore, 1 = Feb, 2=Mar, 3 = April...
so, in this case.... I don't know how to adjust this formula
Couple options... you could do something like below, which will add a month to the original number (or add 11 in the case of January):
DateTimeFormat(DateTimeParse(ToString(IF [Month] = 12 THEN [Month] - 11 ELSE [Month] + 1 ENDIF),"%m"),"%b")
Or you could opt for a SWITCH formula instead:
DateTimeFormat(DateTimeParse(ToString(Switch([Month],[Month]+1,12,1)),"%m"),"%b")
The switch formula lets you pick the field, set a default response, and then pick the result you want if the following values are found. So in this case it will add a day for everything except when the original month is 12, when it will replace it with 1.
Does that help? :)
NJ
Hi Nicole;
I had error message for the 1st formula
DateTimeFormat(DateTimeParse(ToString(IF [Month] = 12 THEN [Month] - 11 ELSE [Month] + 1 ENDIF),"%m"),"%b")
"Formula invalid type in subtraction operator"
For the 2nd formula, I also had error message as well
DateTimeFormat(DateTimeParse(ToString(Switch([Month],[Month]+1,12,1)),"%m"),"%b")
"type mismatch in operator +"
I have changed the data type to "int 16" by using select function, then changed the 1st formula as below....
But I think I still had some syntax errors....
DateTimeFormat(DateTimeParse(Tonumber(Month)(IF [Month] = 12 THEN [Month] - 11 ELSE [Month] + 1 ENDIF),"%m"),"%b")
Finally~~~I got it ~~I break down the formula into two parts~~~Thank you very much for help SQL idoit like me :)
IF [Month] = 12 THEN [Month] - 11 ELSE [Month] + 1 ENDIF
DateTimeFormat(DateTimeParse(ToString([Month]),"%m"),"%b")