Discussion Board for collaboration related to QlikView App Development.
I am really new to this. I have a text field (called Period) containing date information. I want to create a Month, Year and a YearMonth field but don't know how to do it. Can anyone help, please ?
The contents of this Period field looks like 2017-M03, 2017-M04 etc
I have seen a function Date# but don't know how to strip out the M.
Once you have the field that Sunny has described how to create you can do other stuff with it in a preceding load, you can also change the format of it;
Month(YearMonth) as Month,
Year(YearMonth) as Year
Date(Date#(Replace(Period, 'M', ''), 'YYYY-MM'), 'MMM-YY') as YearMonth,
This will give you four fields from your original field, as follows:
YearMonth: Mar 2017
Hope that helps.
View solution in original post
You can try this
Date(Date#(PurgeChar(Period, 'M'), 'YYYY-MM'), 'YYYY-MM') as YearMonth
Date(Date#(Replace(Period, 'M', ''), 'YYYY-MM'), 'YYYY-MM') as YearMonth
Thanks for the additional information, Steve.
Thanks Sunny. I was trying to go through the list of commands in the Help option to work out what to use but I got a bit lost.
Try Like this.
LOAD *,Month(MonthYear) as Month,Year(MonthYear) as Year;
LOAD *,DATE#(left(Period,4)&'-'&SubField(Period,'-M',2),'YYYY-MM') as MonthYear;
LOAD * INLINE [