topic Re: Create a new Field like 'MMM - YY' in QlikView

Create a new Field like 'MMM - YY'

dmohanty — Sun, 22 Feb 2015 17:56:11 GMT

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]

Re: Create a new Field like 'MMM - YY'

Anonymous — Sun, 22 Feb 2015 18:28:59 GMT

Try something like :

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

Re: Create a new Field like 'MMM - YY'

MK_QSL — Sun, 22 Feb 2015 18:34:15 GMT

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

Re: Create a new Field like 'MMM - YY'

its_anandrjs — Sun, 22 Feb 2015 18:45:46 GMT

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

Re: Create a new Field like 'MMM - YY'

sunny_talwar — Sun, 22 Feb 2015 19:10:27 GMT

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,

Re: Create a new Field like 'MMM - YY'

maxgro — Sun, 22 Feb 2015 19:59:21 GMT

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)