Extracting Month from a string.

[jasonwills]

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

[alexandros17]

Mid(columnName, 4,3) as month

let me know

[swuehl]

Month(Date#(Trim(SubField(columnName,'-',2)),'MMM')) as Month,

or

Month(Date#( columnName,'DD-MMM-YYYY')) as Month

[#4 · Accepted solution]

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.

[jasonwills]

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‌

[swuehl]

Maybe

Dual( Date(columnname,'MMMM'), Month(columnname) ) as MonthLong,

[jasonwills]

Works a treat, many thanks swuehl‌, I appreciate it.

Regards

Jason