Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
amit_saini
Master III
Master III

Script help

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

51 Replies
amit_saini
Master III
Master III
Author

Output from=WeekStart(Today())

Thanks,

AS

sunny_talwar

This looks good to me, give it a shot with your if statement and see if it works or not

amit_saini
Master III
Master III
Author

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

Kushal_Chawda

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

amit_saini
Master III
Master III
Author

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

sunny_talwar

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?

sunny_talwar

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

amit_saini
Master III
Master III
Author

Num(WeekDay(Today())) and Num(WeekDay(Today() + 7)) giving me 0 in text box


Rest 5 and 6 is fine.


Thanks,

AS

sunny_talwar

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

amit_saini
Master III
Master III
Author

I think this has to be like this:

'DD.MM.YYYY hh:mm:ss'

Let me give a try!

Thanks,

AS