Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Folks,
I'm having one table called "STICHPROBE" , so normally we are running script below for QVD generator to load data every 2 hours from Database.
Suppose script started running every Monday than Tuesday...Wed...Thus... But on Friday What I'm looking is to load data once again from Monday . So on every Friday's of the Month we gonna load data once again from Monday.
So could u please help how this can be handled by using below script:
********************************************Script Start***********************************************************
LET vStartDate = date(makedate(2015,1), 'DD.MM.YYYY hh:mm:ss');
Set vQVD = 'D:\qvprod\QV_QVD';
Let vQVDCurrentYear = 'D:\qvprod\QV_QVD\'& year(today());
$(Include=.....\0_db_connections.qvs)
Let vStart = now();
Let vReloadStart = now();
///$tab STICHPROBE
if filesize('$(vQVDCurrentYear)\$(vPlant)_STICHPROBE.qvd') >0 then
trace >>>>>> $(vQVDCurrentYear)\$(vPlant)_STICHPROBE.qvd ;
MAX_TIME:
load
max(DTTSERF) as LAST_LOAD_TIME
from $(vQVDCurrentYear)\$(vPlant)_STICHPROBE.qvd (qvd);
let vLAST_LOAD_TIME = timestamp(peek('LAST_LOAD_TIME',0,'MAX_TIME'), 'DD.MM.YYYY hh:mm:ss');
trace >>>>>> LAST_LOAD_TIME: $(vLAST_LOAD_TIME);
drop table MAX_TIME;
else
LET vLAST_LOAD_TIME = '$(vStartDate)';
trace >>>>>> LAST_LOAD_TIME: $(vLAST_LOAD_TIME);
end if
// load new values
STICHPROBE:
LOAD
'$(vPlant)' as Plant,
*;
SQL SELECT
*
FROM $(vPlantAutor).STICHPROBE
WHERE DTTSERF > to_timestamp('$(vLAST_LOAD_TIME)', 'DD.MM.YYYY HH24:MI:SS');
// load prior values
if filesize('$(vQVDCurrentYear)\$(vPlant)_STICHPROBE.qvd') >0 then
concatenate(STICHPROBE)
load
*
from $(vQVDCurrentYear)\$(vPlant)_STICHPROBE.qvd (qvd);
end if
// store all values
store STICHPROBE into $(vQVDCurrentYear)\$(vPlant)_STICHPROBE.qvd;
LET vSTICHPROBE_LOAD_DURATION = Interval(now() - vStart) ;
LET vStart = now();
LET vSTICHPROBE_Size = num(filesize('$(vQVDCurrentYear)\$(vPlant)_STICHPROBE.qvd')/1024,'00.0') & ' KB';
LET vSTICHPROBE_Records = QvdNoOfRecords ('$(vQVDCurrentYear)\$(vPlant)_STICHPROBE.qvd');
*************************************************************************Script End******************************************************************
Thanks,
AS
Hahahaha you are right. My bad
Thanks u Khushal and Sunny for ur time and suggestion.
Will let u know once I'm having some output.
Thanks,
AS
Sunny,
Getting one error now , please see log + script file.
Thanks
AS
What about this:
LET vLoadFrom = TimeStamp(If(Num(WeekDay(vLAST_LOAD_TIME)) >= 6 orNum(WeekDay(vLAST_LOAD_TIME)) = 0, WeekStart(vLAST_LOAD_TIME), vLAST_LOAD_TIME), 'DD.MM.YYYY hh:mm:ss');
or
LET vLoadFrom = TimeStamp(If(Num(WeekDay('$(vLAST_LOAD_TIME)')) >= 6 orNum(WeekDay('$(vLAST_LOAD_TIME)')) = 0, WeekStart('$(vLAST_LOAD_TIME)'), '$(vLAST_LOAD_TIME)'), 'DD.MM.YYYY hh:mm:ss');
Added single quotes around the variable in the second expression.
Sorry!
Still the same in both cases:
Thanks,
AS
Try this:
SET TimestampFormat='MM.DD.YYYY hh:mm:ss';
and then this:
LET vLoadFrom = TimeStamp(If(Num(WeekDay('$(vLAST_LOAD_TIME)')) >= 6 orNum(WeekDay('$(vLAST_LOAD_TIME)')) = 0, WeekStart('$(vLAST_LOAD_TIME)'), '$(vLAST_LOAD_TIME)'));
Sorry
LET vLoadFrom = TimeStamp(If(Num(WeekDay('11.04.2016 14:07:00')) >= 6 orNum(WeekDay('11.04.2016 14:07:00')) = 0, WeekStart('11.04.2016 14:07:00'), '11.04.2016 14:07:00'))
04.11.2016 15:39:45: Error: Script line error:
Thanks,
AS
Can you share the logfile for this refresh?
PFA!
You did not change the environmental variable?
From
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT'
To
SET TimestampFormat='MM.DD.YYYY hh:mm:ss';