topic Re: Date in script not working in QlikView

Date in script not working

samuel_brierley — Tue, 27 May 2014 12:25:26 GMT

Hi, first of all thank you for any help.

in my data excel file there is a timestamp in the format 'dd/mm/yy hh:mm' ~( the apostraphies are actually there!)

now in my scipt i use the following to extract the date

Date(mid(Time,2,8),'dd/MM/YYYY') as Date,

for some reason this doesnt work at all, it works for some date but not others, i think it gets confused but I dont know why.

to try n identify what is going wrong I load in just one file and clear as day in the source file the timestamp says '28/04/2014 13:01' but for some reason qlikview is showing the 8/05/2014.

just to make life more dificult I am using a Generic Load

any ideas?

Re: Date in script not working

ashfaq_haseeb — Tue, 27 May 2014 12:30:00 GMT

Hi,

Can you post your Excel here?

Regards

ASHFAQ

Re: Date in script not working

Anonymous — Tue, 27 May 2014 12:31:20 GMT

Hard to tell without seeing your excel file. Try this

date(floor(Time), 'DD/MM/YYYY') as Date

Re: Date in script not working

tresB — Tue, 27 May 2014 12:33:21 GMT

Try using Date#() like:

Date(Date#(mid(Time,2,8),'DD/MM/YYYY')) as Date,

Re: Date in script not working

samuel_brierley — Tue, 27 May 2014 12:36:36 GMT

Im actualy not sure what was wrong but its fixed, I never changed anything but loading in one file at a time worked. must of been a computing error, not sure why though wasnt that many records.

thanks for the answers anyway guys, forum as supportive as always

Re: Date in script not working

its_anandrjs — Tue, 27 May 2014 12:40:29 GMT

As you say it is works for you if not then you can try with below script also

Load

Date(Date#(Mid(Time,2,8),'DD/MM/YYYY'),'DD/MM/YYY' ) as Date

From Location;

Re: Date in script not working

Anonymous — Tue, 27 May 2014 12:41:33 GMT

Good it works now.

Anyway, a piece of advice... Format pattern is case sensitive, for example MM is for months, mm is for minutes. DD and dd maybe work the same way, but it better to be consistent and use capital letters for day/month/year, and small for hours/minutes/seconds, as in DD/MM/YYYY hh:mm:ss

Regards,

Michael

Re: Date in script not working

samuel_brierley — Tue, 27 May 2014 12:45:16 GMT

thats a nice bit oif advice thank you