topic Re: Month and Year in QlikView

Month and Year

psk180590 — Thu, 30 Mar 2017 09:30:51 GMT

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!!

Re: Month and Year

karthiksrqv — Thu, 30 Mar 2017 10:03:15 GMT

Hi Sai,

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

Re: Month and Year

Anonymous — Thu, 30 Mar 2017 10:23:39 GMT

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

sunny_talwar — Thu, 30 Mar 2017 11:15:31 GMT

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....

Re: Month and Year

psk180590 — Thu, 30 Mar 2017 12:24:14 GMT

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

sunny_talwar — Thu, 30 Mar 2017 12:27:18 GMT

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

Re: Month and Year

psk180590 — Thu, 30 Mar 2017 12:34:06 GMT

Perfect works like a charm.

Thanks very much!!