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]

can u post sample data.

[#3]

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!

[#4]

I meant the document

[#5]

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

[#6]

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

[#7]

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

[#8 · Accepted solution]

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

[#9]

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

yoel

[#10]

Your welcome