Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
psk180590
Contributor III

Month and Year

Hello All,

I have a column Header in Excel as 'M 2016.01 Sales' for each month.

How can i create Year as 2016 and Month as 01,02....12 from this?

TIA!!

1 Solution

Accepted Solutions

Re: Month and Year

Try this in the resident load after the crosstable load

Year(Date#(KeepChar(MonthYear, '0123456789'), 'YYYYMM')) as Year

Month(Date#(KeepChar(MonthYear, '0123456789'), 'YYYYMM')) as Month,

Date(Date#(KeepChar(MonthYear, '0123456789'), 'YYYYMM')) as Date,

Date(MonthStart(Date#(KeepChar(MonthYear, '0123456789'), 'YYYYMM')), 'MMM-YYYY')) as MonthYear

6 Replies
Partner
Partner

Re: Month and Year

Hi Sai,

Use the string functions like left(),right(), subfield() to extract specific values from the input.

qlikdeez
Valued Contributor

Re: Month and Year

You can try using this sample:


Data:
load left(DateTemp1,4) as Year,
Right(DateTemp1,2) as Month,
DateTemp1 as YearMonth;
load trim(PurgeChar(Dates,'M .')) as DateTemp1; // YYYYMM FORMAT
LOAD * INLINE [
Dates
M 2016.01
M 2016.02
M 2016.03
M 2017.01
M 2017.02
M 2017.02 
]
;

Hope this helps!

Re: Month and Year

I think you might need to use The Crosstable Load to convert the columns into a field and then you can perform any string function manipulation to it....

psk180590
Contributor III

Re: Month and Year

HI stalwar1

Yes, i have converted them using Crosstable. But, i'm not sure how do extract Year and Month.

Could you please tell me how do i write Left for this  'M 2016.01 Sales'  to get Year as 2016 and also Month as 01

Re: Month and Year

Try this in the resident load after the crosstable load

Year(Date#(KeepChar(MonthYear, '0123456789'), 'YYYYMM')) as Year

Month(Date#(KeepChar(MonthYear, '0123456789'), 'YYYYMM')) as Month,

Date(Date#(KeepChar(MonthYear, '0123456789'), 'YYYYMM')) as Date,

Date(MonthStart(Date#(KeepChar(MonthYear, '0123456789'), 'YYYYMM')), 'MMM-YYYY')) as MonthYear

psk180590
Contributor III

Re: Month and Year

Perfect works like a charm.

Thanks very much!!