Discussion Board for collaboration related to QlikView App Development.
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?
Go to 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
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....
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
Perfect works like a charm.
Thanks very much!!