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
Output from=WeekStart(Today())
Thanks,
AS
This looks good to me, give it a shot with your if statement and see if it works or not
So you think this below might work???
Let vLoadFrom = timestamp(If(Weekday($(vLAST_LOAD_TIME)) >= 5, WeekStart($(vLAST_LOAD_TIME)), $(vLAST_LOAD_TIME)), 'DD.MM.YYYY HH24:MI:SS');
Thanks
AS
I don't think that it will always give you monday. Instead try
Let vLoadFrom = timestamp(If(Weekday($(vLAST_LOAD_TIME)) >= 5, ($(vLAST_LOAD_TIME)-5), $(vLAST_LOAD_TIME)), 'DD.MM.YYYY HH24:MI:SS');
So Kushal this below is not required:
let vLAST_LOAD_TIME = timestamp(peek('LAST_LOAD_TIME',0,'MAX_TIME'), 'DD.MM.YYYY hh:mm:ss');
I have to change this with the new logic???
Thanks,
AS
I think -5 would be an issue on a Saturday or Sunday refresh. I don't see why WeekStart() won't give a Monday all the time Kush?
I think it should work. Only other thing to check is Num(WeekDay(Today())) to ensure that you get 1. and Num(WeekDay(Today() + 5)) gives you 5, Num(WeekDay(Today() + 6)) gives 6 and Num(WeekDay(Today() + 7)) gives 7 in a text box object
Num(WeekDay(Today())) and Num(WeekDay(Today() + 7)) giving me 0 in text box
Rest 5 and 6 is fine.
Thanks,
AS
Then try this:
LET vLoadFrom = TimeStamp(If(Num(WeekDay($(vLAST_LOAD_TIME))) >= 6 or Num(WeekDay($(vLAST_LOAD_TIME))) = 0, WeekStart($(vLAST_LOAD_TIME)), $(vLAST_LOAD_TIME)), 'DD.MM.YYYY HH24:MI:SS');
The one last thing that concerns me is the this date format -> DD.MM.YYYY HH24:MI:SS
I don't think QlikView will recognize this, may be you want this -> DD/MM/YYYY hh:mm:ss
LET vLoadFrom = TimeStamp(If(Num(WeekDay($(vLAST_LOAD_TIME))) >= 6 or Num(WeekDay($(vLAST_LOAD_TIME))) = 0, WeekStart($(vLAST_LOAD_TIME)), $(vLAST_LOAD_TIME)), 'DD/MM/YYYY hh:mm:ss');
I think this has to be like this:
'DD.MM.YYYY hh:mm:ss'
Let me give a try!
Thanks,
AS