Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

time stamp issue

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

12 Replies
sushil353
Honored Contributor II

Re: time stamp issue

try:

Now() instead of today()

HTH

Sushil

Not applicable

Re: time stamp issue

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

Not applicable

Re: time stamp issue

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);

Not applicable

Re: time stamp issue

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...

kumarnatarajan
Valued Contributor

Re: time stamp issue

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);

Not applicable

Re: time stamp issue

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

sivarajs
Valued Contributor II

Re: time stamp issue

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

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

Not applicable

Re: time stamp issue

hi Siv

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

thanks

Not applicable

Re: time stamp issue

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.


Community Browser