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
sunny_talwar

Hahahaha you are right. My bad

amit_saini
Master III
Master III
Author

Thanks u Khushal and Sunny for ur time and suggestion.

Will let u know once I'm having some output.

Thanks,

AS

amit_saini
Master III
Master III
Author

Sunny,

Getting one error now , please see log + script file.

Thanks

AS

sunny_talwar

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.

amit_saini
Master III
Master III
Author

Sorry!

Still the same in both cases:

Thanks,

AS

sunny_talwar

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

amit_saini
Master III
Master III
Author

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

sunny_talwar

Can you share the logfile for this refresh?

amit_saini
Master III
Master III
Author

PFA!

sunny_talwar

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