Create a new Field like 'MMM - YY'

[dmohanty]

Hi All,

I have 2 fields like this in QVD. Using this, I need to create new Field "Month/Year" with data format like this 'MMM - YY'

I am using like this below script, but its getting converted to Char format (instead of Number format). Please help to get the converted field in Number format.

purgechar(CLNDR_YR_NUM,',') as CLNDR_YR_NUM,

MTH_ABBR_ID & ' - ' & right(purgechar(CLNDR_YR_NUM,','),2) as [Month/Year]

[Anonymous]

Try something like :

     =date(date#(MTH_ABBR_ID&num(num#(CLNDR_YR_NUM,'#,###'),'####') , 'MMMYYYY' ) , 'MMM - YY' )

[MK_QSL]

=Date(Date#(MTH_ABBR_ID & PurgeChar(CLNDR_YR_NUM,','),'MMMYYYY'),'MMM-YY')

[its_anandrjs]

Hi,

Try to convert the dates in date format and then use the Makedate function for date conversion and then convert into MMM - YY

LOAD

     MTH_ABBR_ID,

     num(Month(Date#(MTH_ABBR_ID,'MMM'))) AS MTH_ABBR_ID_New,

     Date#(PurgeChar(CLNDR_YR_NUM,','),'YYYY') AS CLNDR_YR_NUM,

     Date(MakeDate( Year(Date#(PurgeChar(CLNDR_YR_NUM,','),'YYYY')),Num(Month(Date#(MTH_ABBR_ID,'MMM')))),'MMM - YY') as MonthYear

FROM

Source

(ooxml, embedded labels, table is Sheet2);

Regards

Anand

[sunny_talwar]

You can use the Mapping Load with Apply map to create a date using the following script.

Months:

Mapping LOAD * INLINE [

    Month Name, Month Number

    Jan, 1

    Feb, 2

    Mar, 3

    Apr, 4

    May, 5

    Jun, 6

    Jul, 7

    Aug, 8

    Sep, 9

    Oct, 10

    Nov, 11

    Dec, 12

];

Table:

LOAD MakeDate(CLNDR_YR_NUM, ApplyMap('Months', MTH_ABBR_ID), 1) as Date,

  MonthName(MakeDate(CLNDR_YR_NUM, ApplyMap('Months', MTH_ABBR_ID), 1)) as MonthYear,

  *;

LOAD * INLINE [

    MTH_ABBR_ID, CLNDR_YR_NUM

    Jul, 2011

    Nov, 2008

    Dec, 2024

    Dec, 2023

    Aug, 2015

    Jun, 2008

    Apr, 2022

    Dec, 2009

    Oct, 2017

    Oct, 2030

    Jan, 2009

    Feb, 2024

    Aug, 2030

    May, 2009

    May, 2010

    Sep, 2011

];

Best,

S

[maxgro]

date(

     makedate(                                                     // makedate(year, month)

          purgechar(CLNDR_YR_NUM, ','),                // year

          month(date#(MTH_ABBR_ID,'MMM'))          // month (depends on MonthNames)

          )                                                             

     , 'MMM - YY') as [Month/Year]                        // date formatting: date(datefield, format)