Month and Year

[psk180590]

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

[karthiksrqv]

Hi Sai,

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

[Anonymous]

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!

[sunny_talwar]

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]

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

[sunny_talwar · Accepted solution]

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]

Perfect works like a charm.

Thanks very much!!