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

sunindia‌ any suggestion ???

Thanks,

AS

Kushal_Chawda

as you are storing the date in timestamp, so from monday from what time you want to load the data?

sunny_talwar

Hey Amit -

Let me get some clarity here.

I think this is where you are loading the data from, right?

STICHPROBE:

LOAD

    '$(vPlant)' as Plant,

    *;

SQL SELECT

*

FROM $(vPlantAutor).STICHPROBE

WHERE DTTSERF > to_timestamp('$(vLAST_LOAD_TIME)', 'DD.MM.YYYY HH24:MI:SS');

On a Friday, you want to load all the new data starting from Monday of the current week. Am I correct so far?

Now is this the place where you are trying to set value for vLAST_LOAD_TIME using if then statement?

amit_saini
Master III
Master III
Author

Hi Sunny,

On "I think this is where you are loading the data from, right?"  : Yes

"On a Friday, you want to load all the new data starting from Monday of the current week. Am I correct so far?" : Yes Correct

For "Now is this the place where you are trying to set value for vLAST_LOAD_TIME using if then statement?"


Yes looking some condition here for "vLAST_LOAD_TIME" to achieve this .


Thanks,AS

amit_saini
Master III
Master III
Author

We are starting load from 6:00:00 AM.

Thanks,

AS

sunny_talwar

Since vLAST_LOAD_TIME seems to be based on today, I am not sure why we need the if then else statement. May be something like this might work?

LET vLAST_LOAD_TIME = TimeStamp(If(WeekDay(Today()) = 'Fri', DayStart(Now()-4) + 6/24, DayStart(Now()) + 6/24), 'YourRequiredFormatHere');

amit_saini
Master III
Master III
Author

I'm trying something like below:

Let vLoadFrom = timestamp(If(Weekday($(vLAST_LOAD_TIME)) >= 5, WeekStart($(vLAST_LOAD_TIME)), $(vLAST_LOAD_TIME)), 'DD.MM.YYYY HH24:MI:SS');

STICHPROBE:

LOAD

    '$(vPlant)' as Plant,

    *;

SQL SELECT

NSPCIDNR,

NTOOLNR,

NSTPNR,

NPERS_IDNR

FROM $(vPlantAutor).STICHPROBE

WHERE DTTSERF > to_timestamp('$(vLoadFrom)', 'DD.MM.YYYY HH24:MI:SS');

store STICHPROBE into $(vQVDCurrentYear)\$(vPlant)_STICHPROBE.qvd;

But no sure if this is right???

Thanks,

AS

Kushal_Chawda

try this

MAX_TIME:

  load if(weekday(today())='Fri', date(today()-5)+maketime(6),LAST_LOAD_TIME1) as LAST_LOAD_TIME;

  load

     max(DTTSERF) as LAST_LOAD_TIME1

  from $(vQVDCurrentYear)\$(vPlant)_STICHPROBE.qvd (qvd);

sunny_talwar

1st check if WeekStart($(vLAST_LOAD_TIME)) is actually taking you to a Monday. You can do this on the front end of the application by check if WeekStart(Today()) is giving tyou 04/11/2016 or 04/10/2016 or some other date. You might have to adjust it if it isn't giving you a Monday -> WeekStart($(vLAST_LOAD_TIME), 0, 1) (Play around with the third argument in the WeekStart function).

Other than that, give it a shot and see if it works or not. I would be curious to know if it is working or not.