incremental load from sql database

[#1]

Dear members of the Qlikview community,

I’m sorry to bother you and I already thank you.

I’ve a problem with my Qlikview application (the data comes from a SQL server management 2008/odbc database).

I would like to execute the application in live, and, thus, I need to realize incremental reloads : use qvd files with a simple add from the SQL database.

As I read on the Qlikview Manual Book, I added 2 variables (example for the reload of a table).

set DCX=Now();

….

Cotation:
Load autonumber(INFO&DATE_ID_T3) as Cotation_ID,*;
LOAD year(DATE)*10000 + month(DATE)*100 + day(DATE) as [DATE_ID_T3],*;
SQL SELECT *
FROM DBPRICING.dbo.HCOT where DATE>#$(MDX)# and DATE<#$(DCX)#;
concatenate load * from Cotation.qvd (qvd);

…

set MDX=Now();

However, I encounter an error: (scriperror.jpg the joined file…)

I’ve done a lot of tests but I got errors because of this part:

DATE>#$(MDX)# (I’ve tested without the # or with today() instead of now() )

But it works with DATE>Floor(CAST(GETDATE() AS float));

But it’s not suitable for me because I need a Year-Month-Day-Hour-Minute-Second format.

PS: In my SQL database, the date is in this format:

2013-12-10 16:45:50.013

Thanks,

Yoel

[#2]

You use MDX variable in SQL statement before you have SET it - I dont know if this could have an affect.

otherwise try this:

Remove # - The error occours cause your using # - You should enclose your timestamp with ' '

If i put now into a variable i get this: 14-12-2013 14:45:06

If i put # infront of i get this: #NOW is not a valid function

And furthermore you need it in

YYYY-MM-DD - Make sure this is the output you get. Test it in variable. as you can see mine is DD-MM-YYYY

example to how you can convert:

=Date(Floor(Timestamp#('12-12-2222 12:12:12','MM-DD-YYYY HH:MM:SS')),'YYYY-MM-DD HH:MM:SS')

[stabben23]

hi, Now() function is not valid in SQL server, use getdate.

//Staffan

[#4]

He doesnt use now() in SQL, he uses it to retrieve the date in qlikview.

[#5]

Thank you for your reply.

i tried set MDX=Now()

and with set MDX = Getdate and it doesn't work...

I tried with '$(MDX)' and it doesnt work...

Thomas is right, i use now to retrieve the date in qlikview.

Do you think it is because of the format  DD-MM-YYYY instead of YYYY-MM-DD (like in my sql database)??

How can i fix it (change my variable format)..

thanks

yoel

[#6]

Yes- First or all change your SET format in main script so they are like the way you want it!  If the dates are not in the same format you cannot use them in the where clause

If that is not enough then convert the date like the example i posted to you

=Date(Floor(Timestamp#('12-12-2222 12:12:12','MM-DD-YYYY HH:MM:SS')),'YYYY-MM-DD HH:MM:SS')

[stabben23]

LET MDX = timestamp(now(), 'YYYY-MM-DD'); could fix your format //Staffan

[stabben23]

Yes, correct, to fast answer.

[#9]

or like this if he needs the timestamp

Date(Floor(timestamp(now(), 'MM-DD-YYYY HH:MM:SS')),'YYYY-MM-DD HH:MM:SS')

[#10]

SQL##f - SqlState: 22008, ErrorCode: 242, ErrorMsg: [Microsoft][ODBC SQL Server Driver][SQL Server]La conversion d'un type de données varchar en type de données datetime a créé une valeur hors limites. (out of limits value) sorry for french...

SQL SELECT *

FROM DBPRICING.dbo.HCOT where DATE>'2013-12-15 00:12:00'

i don't understand this error .

How can i fix it please??

thanks