topic Re: Makedate function not working in QlikView

Makedate function not working

Tue, 31 Mar 2015 15:22:27 GMT

I have a text field with Date in format Mmm dd, yyyy (ex: Jan 8, 2015, Mar 13, 2015)

Qlik wont recognize it as date so trying to use the makedate function below and the year and month work fine but the Day comes through as 1 only. But when I pull the textbetween function out on its own and run it, I get the actual day number (8, 13). Why is it putting in a 1 when used in MakeDate function. I tried to convert to Num and trim spaces but still doesn't work. It appears it is not recognizing it as a number. what am I missing and is there a better way to get the Day out rather than using the textbetween. the number is either 1 or 2 digits so cannot use mid function.

MakeDate(

right([Timesheet Entry Date],4),

applymap('Month_Map',left(upper([Timesheet Entry Date]),3),

num(trim(TextBetween([Timesheet Entry Date],' ',',')))))

Re: Makedate function not working

MK_QSL — Tue, 31 Mar 2015 15:25:47 GMT

Use below line in your script while loading the date field

=Date(Date#(YourDateField,'MMM D, YYYY'))

Re: Makedate function not working

Anonymous — Tue, 31 Mar 2015 15:30:30 GMT

I had success with this formula

mid('Jan 13, 2015', FindOneOf('Jan 13, 2015',',')-2,2)

it works with 13 and with 8 in a textbox

hope it works for you too

Re: Makedate function not working

Tue, 31 Mar 2015 15:35:02 GMT

THANK YOU!!!! WORKED PERFECTLY and SO MUCH SIMPLER!!!

Still confused why the above doesn't work because the same exact one works when embedded in the Weekstart() function. It actually gives me the correct weekstart based on the date, not the first of month week start. I copy and pasted it from one to the other.

Re: Makedate function not working

Anonymous — Tue, 31 Mar 2015 15:35:53 GMT

=Date(Date#('Jun 8, 2015','MMM D, YYYY'))

Re: Makedate function not working

MK_QSL — Tue, 31 Mar 2015 15:37:04 GMT

Try this...

Date(MakeDate(

right([Timesheet Entry Date],4),

applymap('Month_Map',left(upper([Timesheet Entry Date]),3),

num(trim(TextBetween([Timesheet Entry Date],' ',','))))))

Let me know the Date Format inside your script..

SET DateFormat=

Re: Makedate function not working

MK_QSL — Tue, 31 Mar 2015 15:45:36 GMT

Kindly close the thread if you have received your answer.

Re: Makedate function not working

Thu, 02 Apr 2015 17:00:27 GMT

Not sure what you mean by "close the thread" I believe we want others to be able to search and find the answer via this thread. I marked your answer as correct.