Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!
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
Hi Sai,
Use the string functions like left(),right(), subfield() to extract specific values from the input.
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!
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....
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
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
Perfect works like a charm.
Thanks very much!!