Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
can u post sample data.
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!
I meant the document
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')
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
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
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
thank you so much it is working!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
yoel
Your welcome