Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
new to Qlik Sense, but loving it so far - however, I am stuck on one thing I was hoping you could help with.
I have a date from a column in excel which is in this format:
DD-MMM-YYYY
e.g.
11-NOV-2015
what I am trying to do, is in the data load editor is create a new column with the month in.
I have the following so far:
Trim(SubField(columnName,'-',2)) as Month,
This gets me NOV into a new field - however, if I try to wrap that inside a Month function I get nothing imported.
Any help is greatly appreciated.
Many Thanks
Jason
if you try and import the column as a date field, it will be cleaner for any future grouping
in the list of "SETS" in Load Editor, change to SET DateFormat='DD-MMM-YYY';
this should import your column as a date field.
you can then use MONTH(columnname) as well as YEAR(columnname), WEEKSTART(columnname) etc.
Mid(columnName, 4,3) as month
let me know
Month(Date#(Trim(SubField(columnName,'-',2)),'MMM')) as Month,
or
Month(Date#( columnName,'DD-MMM-YYYY')) as Month
if you try and import the column as a date field, it will be cleaner for any future grouping
in the list of "SETS" in Load Editor, change to SET DateFormat='DD-MMM-YYY';
this should import your column as a date field.
you can then use MONTH(columnname) as well as YEAR(columnname), WEEKSTART(columnname) etc.
Many thanks to all the replies - and so quickly aswell.
Alexandros17, your answer does give me an extract of what I needed, as does yours swuehl, however i feel that Andrews gives me the tidiest solution going forward.
Thanks to you all, and just one more thing if I may? - is it straight forward to expand it to longnames? at the moment it is coming out as Nov - can that be expanded to November?
Thanks Again - I will mark Andrews as the correct answer.
Many Thanks
Jason
Maybe
Dual( Date(columnname,'MMMM'), Month(columnname) ) as MonthLong,