topic Re: incremental load from sql database in QlikView

incremental load from sql database

Sat, 14 Dec 2013 22:35:19 GMT

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

Re: incremental load from sql database

Sat, 14 Dec 2013 22:44:05 GMT

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

Re: incremental load from sql database

stabben23 — Sat, 14 Dec 2013 22:47:37 GMT

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

//Staffan

Re: incremental load from sql database

Sat, 14 Dec 2013 22:52:33 GMT

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

Re: incremental load from sql database

Sat, 14 Dec 2013 22:59:11 GMT

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

Re: incremental load from sql database

Sat, 14 Dec 2013 23:04:04 GMT

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

Re: incremental load from sql database

stabben23 — Sat, 14 Dec 2013 23:04:14 GMT

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

Re: incremental load from sql database

stabben23 — Sat, 14 Dec 2013 23:06:50 GMT

Yes, correct, to fast answer.

Re: incremental load from sql database

Sat, 14 Dec 2013 23:12:21 GMT

or like this if he needs the timestamp

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

Re: incremental load from sql database

Sat, 14 Dec 2013 23:19:49 GMT

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

Re: incremental load from sql database

Sat, 14 Dec 2013 23:25:13 GMT

can u post sample data.

Re: incremental load from sql database

Sat, 14 Dec 2013 23:26:56 GMT

Of course

DATE	INFO	OPT	STRIKE	1M1Y	2M1Y	3M1Y	6M1Y	9M1Y	1Y1Y	18M1Y	2Y1Y	3Y1Y	4Y1Y	5Y1Y	7Y1Y
2013-12-09 15:43:48.153	ALPHA	0	0	1.038	1.591	1.227	1.382	1.55	1.544	1.634	1.679	1.575	1.536	1.438	1.241
2013-12-10 16:45:50.013	RHO	0	0	0.37	0.36	0.36	0.36	0.36	0.36	0.34	0.32	0.32	0.32	0.3	0.26
2013-12-10 16:45:50.033	NU	0	0	0.64	0.635	0.63	0.62	0.6	0.59	0.58	0.46	0.44	0.35	0.31	0.32
2013-12-10 17:51:00.550	PRICE	0	0	6.5	10.4	10.2	17.3	25.2	32.2	49.5	73.1	108.9	137.7	154.4	177.9
2013-12-10 17:51:00.670	NVOL	0	0	28.4	31.9	25.7	30.7	36.5	40.5	51	65.3	80	88.1	88.7	86.8

thank you!

Re: incremental load from sql database

Sat, 14 Dec 2013 23:28:49 GMT

I meant the document

Re: incremental load from sql database

Sat, 14 Dec 2013 23:29:27 GMT

But i can see in your DATE you have more than YYYY-MM-DD HH:MM:SS you also have miliseconds:

try this:

=timestamp(now(), 'YYYY-MM-DD HH:MM:SS.fff')

Re: incremental load from sql database

Sat, 14 Dec 2013 23:31:48 GMT

here is my script

//==========================

// Génération du calendrier

//==========================

LET DCX= Date(Floor(timestamp(now(), 'MM-DD-YYYY HH:MM:SS')),'YYYY-MM-DD HH:MM:SS');

LET MDX=Date(Floor(timestamp(now(), 'MM-DD-YYYY HH:MM:SS')),'YYYY-MM-DD HH:MM:SS');

load * from CALENDRIER.qvd (qvd)where [Date] <=today();

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

concatenate load * from Cotation.qvd (qvd);

TMP:

CrossTable(COMPO, DATA, 6)

LOAD *

resident Cotation;

Zscore:

load *, autonumber(Info_Zscore&DATE_ID_ZS) as Zscore_ID;

load

COMPO,

right(COMPO, len(COMPO)-FindOneOf(COMPO , 'M%Y' ) ) as [COMPF],

left(COMPO, FindOneOf(COMPO , 'M%Y' ) ) as [COMPD],

DATA,

INFO as Info_Zscore,

DATE as DATE_zc,

year(DATE)*10000 + month(DATE)*100 + day(DATE) as [DATE_ID_ZS]

resident TMP;

drop table TMP;

thank you

Re: incremental load from sql database

Sat, 14 Dec 2013 23:32:42 GMT

right, but you used Floor (do it is not a problem) no?

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

thanks

Re: incremental load from sql database

Sat, 14 Dec 2013 23:34:11 GMT

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

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

Re: incremental load from sql database

Sat, 14 Dec 2013 23:40:38 GMT

thank you so much it is working!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

yoel

Re: incremental load from sql database

Sat, 14 Dec 2013 23:41:39 GMT

Your welcome