Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

incremental load from sql database

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

1 Solution

Accepted Solutions
Not applicable

Re: incremental load from sql database

no try without - I can see it was a static date:

this timestamp(now(), 'YYYY-MM-DD HH:MMSmiley FrustratedS.fff') should update it everytime with the right format

18 Replies
Not applicable

Re: incremental load from sql database

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:MMSmiley FrustratedS')),'YYYY-MM-DD HH:MMSmiley FrustratedS')

stabben23
Honored Contributor

Re: incremental load from sql database

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

//Staffan

Not applicable

Re: incremental load from sql database

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

Not applicable

Re: incremental load from sql database

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

Not applicable

Re: incremental load from sql database

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:MMSmiley FrustratedS')),'YYYY-MM-DD HH:MMSmiley FrustratedS')

stabben23
Honored Contributor

Re: incremental load from sql database

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

stabben23
Honored Contributor

Re: incremental load from sql database

Yes, correct, to fast answer.

Not applicable

Re: incremental load from sql database

or like this if he needs the timestamp

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

Not applicable

Re: incremental load from sql database

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

Community Browser