topic Re: Script help in QlikView

Script help

amit_saini — Mon, 11 Apr 2016 09:53:55 GMT

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,

Re: Script help

amit_saini — Mon, 11 Apr 2016 10:01:45 GMT

sunindia‌ any suggestion ???

Thanks,

Re: Script help

Kushal_Chawda — Mon, 11 Apr 2016 10:04:55 GMT

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

Re: Script help

sunny_talwar — Mon, 11 Apr 2016 10:04:55 GMT

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?

Re: Script help

amit_saini — Mon, 11 Apr 2016 10:10:18 GMT

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

Re: Script help

amit_saini — Mon, 11 Apr 2016 10:11:51 GMT

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

Thanks,

Re: Script help

sunny_talwar — Mon, 11 Apr 2016 10:15:59 GMT

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

Re: Script help

amit_saini — Mon, 11 Apr 2016 10:18:41 GMT

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,

Re: Script help

Kushal_Chawda — Mon, 11 Apr 2016 10:20:41 GMT

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

Re: Script help

sunny_talwar — Mon, 11 Apr 2016 10:23:21 GMT

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.

Re: Script help

amit_saini — Mon, 11 Apr 2016 11:00:21 GMT

Output from=WeekStart(Today())

Thanks,

Re: Script help

sunny_talwar — Mon, 11 Apr 2016 11:07:27 GMT

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

Re: Script help

amit_saini — Mon, 11 Apr 2016 11:57:36 GMT

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

Re: Script help

Kushal_Chawda — Mon, 11 Apr 2016 12:04:34 GMT

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

Re: Script help

amit_saini — Mon, 11 Apr 2016 12:18:00 GMT

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,

Re: Script help

sunny_talwar — Mon, 11 Apr 2016 12:19:35 GMT

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?

Re: Script help

sunny_talwar — Mon, 11 Apr 2016 12:21:44 GMT

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

Re: Script help

amit_saini — Mon, 11 Apr 2016 12:28:40 GMT

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

Rest 5 and 6 is fine.

Thanks,

Re: Script help

sunny_talwar — Mon, 11 Apr 2016 12:34:39 GMT

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

Re: Script help

amit_saini — Mon, 11 Apr 2016 12:41:26 GMT

I think this has to be like this:

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

Let me give a try!

Thanks,