topic Re: time stamp issue in QlikView

time stamp issue

Fri, 17 May 2013 09:10:53 GMT

hi all

i am importing raw data from MS Access and Date column in MS Access has date followed by time stamp

5/16/2013 12:00:00:00

when i load the raw data and i compare the date column with variable declared in script

tdate= date (today(),'M/D/YYYY');

load * from MS access table where Date='$(tdate);

this gives an error of mismatch format

please advise for to resolve in script

thanks

Re: time stamp issue

sushil353 — Fri, 17 May 2013 09:14:40 GMT

try:

Now() instead of today()

HTH

Sushil

Re: time stamp issue

Fri, 17 May 2013 09:17:22 GMT

how will that help...

i need a formula to us on Date column in MS Access while loading it in Qlikview script to remove time stamp

any idea

Re: time stamp issue

Fri, 17 May 2013 09:17:34 GMT

write according to this syntax

tab1:

load *,date(Date,'M/D/YYYY') as Date_new

from MS access table ;

load * resident tab1 where Date_new=$(tdate);

Re: time stamp issue

Fri, 17 May 2013 09:32:43 GMT

You can try - MakeDate(Year(Date),month(Date),Day(Date))

while loading the access data on qlikview... this will remove the time from time stamp

hope it helps...

Re: time stamp issue

kumarnatarajan — Fri, 17 May 2013 09:40:06 GMT

Hi,

Try this one

Let tdate= Date(today(),'M/D/YYYY');

Ex: load * from MS access table where floor(Date(Date,'M/D/YYYY'))=$(tdate);

Re: time stamp issue

Fri, 17 May 2013 10:39:03 GMT

none of the above 3 suggestions are working as its MS Access so its Select instead of Load and the error i am getting for all teh 3 options is type mismatch in SQL query

please advise

Re: time stamp issue

sivarajs — Fri, 17 May 2013 10:58:21 GMT

Use let statement to define your date and use in load like

load * from test where Date='$(tdate)';

Re: time stamp issue

Fri, 17 May 2013 11:01:00 GMT

hi Siv

as mentioned in my original post, i hav already declared the variable and your suggestion doesnot work

thanks

Re: time stamp issue

Fri, 17 May 2013 11:32:34 GMT

You can use-

//first load table from access...

Temp:

select Date,

Data1,

Data2

from AccessTableName;

// Use load statement...

Final Table:

load

Date,

MakeDate(Year(Date),month(Date),Day(Date)) as Final Date,

Data1,

Data2

resident Temp;

Drop Table Temp;

Hope it will resolve the issue.

Re: time stamp issue

Fri, 17 May 2013 12:16:03 GMT

still does not work

Re: time stamp issue

gaurav2017 — Fri, 17 May 2013 12:23:49 GMT

hi,

can you send some sample data in excel or a qvw?

Regards

Re: time stamp issue

rwunderlich — Fri, 17 May 2013 14:57:40 GMT

does not remove the time portion from the internal value.

You need to add the floor function

date(floor(Date,'M/D/YYYY')) as Date_new

Read this excellent Technical Brief to understand the details of using date fields in QV.

http://community.qlik.com/docs/DOC-3102

-Rob