Skip to main content

Announcements
Week 3: High-Volume Iceberg Ingestion and Smarter Data Prep - WATCH NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Convert months (numeric) into text

Hi Experts

I have a file that contains months in a numeric format. How can I convert the numeric values into text for the months? (Jan, Feb, e.g.)

Thank you for your help!

Jan

1 Solution

Accepted Solutions
preminqlik
Specialist II

Load *,

text(Date(Date#(fieldname,'MM'),'MMM'))               as               MonthName

from path;

View solution in original post

11 Replies
Not applicable
Author

Hi jan,

In your load script you set the date format = MON/DD/YYYY;

For Month kindly use MON i hope it would help you to convert.. try it.

Regards,

Vignesh

robert_mika
Master III

month(MakeDate(2000,YourNUmberFiled))

buzzy996
Master II

try to use MONTH(UR FIELD NAME)

Not applicable
Author

Depending on what you're tying to do, you can also use:

pick(your_field_name, 'JAN', 'FEB', 'MAR, ...., 'NOV', 'DEC');

This is assuming your_field_name is a number between 1 and 12.

Regards!

maxgro
MVP

you can use

subfield('$(MonthNames)', ';', yourmonthnumber)

also in the script, example

load

  rowno() as Month,

  subfield('$(MonthNames)', ';', rowno()) as MonthName

AutoGenerate 12;

1.jpg

MonthNames is defined at the beginning of the script

JonnyPoole
Former Employee

Another one:

if your values are  1,2,3 etc....

use an expression like this to interpret the numbers as months and then display them in 'mmm' format ( 'Jan','Feb','Mar' ....)

Date(Date#(MonthNumber,'M'),'MMM') as Month

Sample script:

numbers:

LOAD * INLINE [

    MonthNumber

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    11

    12

];

Dates:

load * , Date(Date#(MonthNumber,'M'),'MMM') as Month

Resident numbers;

drop table numbers;

preminqlik
Specialist II

Load *,

text(Date(Date#(fieldname,'MM'),'MMM'))               as               MonthName

from path;

mukesh24
Partner - Creator III

hi,

Simply load inline table in data model or join this table

DIS_MONTH:

LOAD * INLINE [

MONTH1, DISPLAY_MTN

1, Jan

2, Feb

3, Mar

4, Apr

5, May

6, Jun

7, Jul

8, Aug

9, Sep

10, oct

11, Nov

12, Dec

];

Not applicable
Author

Awesome! Thank you!