Execute only a part of the script

[#1]

I would like to know whether it is possible to execute only a part of the script or not. It would be nice to have this condition work similar to the buffer load with the stale option.

Thanks in advance.

[peterbergman]

Hi Citera

Hope this can help you

1) Make an inputbox with a variable ie $MyDebugVariable. You can have more than 2 different values (0,1, 2, 3....)

2) In the script use If and end if at the different part of the script you would like to run

You can control wich part to execute just by changing the variable in the inputbox. Or you can add SET $MyDebugVariable= 1 or 0 at the top of the script. Remeber that setting the variable in the script overrides the value you put in the inputbox.

Good luck

Peter

Example:

If $MyDebugVariable = 1 then

SELECT

* FROM TABLE;

end if

If

$MyDebugVariable = 0 then

SELECT

* FROM ANOTHERTABLE

end

if

[#3]

First of all, thanks for all the suggetions. I realize that I have partially explained my challenge.

The partial reload is not just for one table but for also for the joins. The suggested if - end if construction with the variable will do quite nicely. I already use this for my live and debug version of the script and indeed this would be taking me a step closer to my goal.

What I would like to achieve is only running parts of the script every one (or few) hour(s) while other parts only should be run once a day. The "stale" option in the buffer reload does this but only for the sql select part (correct me if I am wrong) and I still would like to add some other fields to the initial loaded table. The stale option gives a user the ability to stale a designated number of hours/days.

This is exactly what I would like to achieve with the if - end if construction and would work if somehow the variable is able to detect the time passed by since the last run and detects whether it should run the part of the script or not.

Thanks for your input.

[rwunderlich]

BIServices wrote: What I would like to achieve is only running parts of the script every one (or few) hour(s) while other parts only should be run once a day. The "stale" option in the buffer reload does this but only for the sql select part (correct me if I am wrong) and I still would like to add some other fields to the initial loaded table. The stale option gives a user the ability to stale a designated number of hours/days.

The buffer/stale statement does not really suppress any "running" of the script, it just changes the source of the data for the load. The load statement is always executed, the table is always reloaded. It's just that the source for the load is either the buffer or the sql select. The table can still be further processed in a load resident.

I have an incremental load that runs hourly except the first run each Tuesday is a full reload. The conditional portion of the script is:

IF (weekday(today(1)) = 'Tue') AND (today(1) - $(#LAST_FULL_RELOAD_DATE) > 0) THEN

LAST_FULL_RELOAD_DATE is maintained by the script. You could probably use ReloadTime() for the completion of the last reload.

If you wanted synch some block of code with the buffer such that the block only gets executed when the buffer is stale -- that would be an interesting exercise.

I have a feeling I'm not understanding what you're after. Can you provide an example?

-Rob

[Anonymous]

Speaking of "...if somehow the variable is able to detect the time passed by since the last run..." - take a look at the system function QVDCreateTime(). Your buffers are qvd files.
I assume that you rely on QlikView to handle them automatically, so the names and locations are may be not convenient to use in the script. If you want more control, you can create QVDs explicitly in the script, and you'll now what exactly files to check.

[#6]

Hi Rob,

We have a script running each morning which rebuilds all of our fact and dimension tables. Executing this script is taking quite some time (approx 2.5 / 3 hours) and is holding us back to start running the script more than once a day. The rebuilding of the fact tables is consuming the most of the time and not all of the fact tables need to be refreshed every time the script is going to run.

For instance, we only create invoices once a day, so this could be skipped nearly all of the times, except for just after the invoicing process. We have more of this kind of fact tables that only need to be refreshed once a day because the source is only refreshed once or because we believe that one refresh a day is sufficient. On the other hand we also have some processes which we would like to monitor on a more frequent basis.

I hope this provides you with a bit more detail on what we would like to achieve. The pointers that you and Michael gave (LAST_FULL_RELOAD_DATE & QVDCreateTime) provided me with some food for thought and I will let you guys know how we advance (or not) ;).

Thanks for your support guys.

[#7]

Hi Marc,

It looks to me like you're after an 'incremental load' (search for it on QlikCommunity, or go straight to Rob Wunderlich's QlikView Cookbook for an example (download the cookbook from here).

Basically what this does is loading data, storing it into a QVD, and next time around loading from the QVD (which is fast) and only loading updates from your original datasource. Ofcourse this requires recording the last reload time (you could put it in a variable, or use the QVD's creation date/time), and having some date/time-flag in your fact data to decide which records were updated since the last reload.

Regards,

Martijn ter Schegget

CND Development

PS: Sorry I tagged your reply as an answer, mis-read 'Suggest as answer' as 'Suggest an answer'...

[#8]

Hi Martijn,

I have looked at Rob's example before and noticed that the modification date is required in order to be able to use the incremental load in the first place. Unfortunately, the tables which I have to read don't contain a modification date (time for a new ERP system ) and therefore, this is not the solution to my current challenge.

It seems like the QvdCreateTime compared to a preset variable will enable us to trigger the reload process for the QVD when required.

[#9 · Accepted solution]

Hi Everyone,

Thanks for all the help you all provided. I have solved it a bit differently because we don't have any fields on which we can base the incremental load. In our case we have set the QlikView server to refresh the data every hour and the example shown below provides us the solution we require. When you have your QlikView server refreshing the data more frequenlty you need to adjust the section which determines the variable vTimeSlot.

Furthermore, the file that is mention in this example is the QVD we were looking at and you need to adjust this as well.

// ========================================
// Set the directory the QVDs are stored in
// ========================================
let vDataInput = 'D:\BI\QVD';

// ==================================================================================
// Set the variable for the timeslot within the part of the script should be executed
// The vTime variable should be set according to the 24:00 clock (in full hours).
// ==================================================================================
let vTime = 22;

// ==================================================
// Detect the date the current qvd has been refreshed
// ==================================================
let vLastRunDate = date(QvdCreateTime('$(vDataInput)\FactTransacties.qvd'), DD-MM-YYYY);
let vNowTime = num(timestamp(now(),DD-MM-YYYY));
let vNowDate = date(now(),DD-MM-YYYY);
let vToday = num(Today());
let vExecute = 0;

//=======================================================================================
// Determine whether the current time is in the timeslot for executing this script or not
//=======================================================================================
if (vNowTime - vToday >= 1/24*($(vTime)) and vNowTime - vToday < 1/24*($(vTime)+1)) then
 let vTimeslot = 1;
else
 let vTimeslot = 0;
end if

// ======================================================================================
// When the current date is different from the date the qvd has been refreshed and when
// the time is in the timeslot the first part of the if then will be executed. When these
// conditions are not met the else part will executed (which is actually nothing).
// NOTE: the statement "let vExecute = 1;" should be replaced with the script
// you want to executed.
// ======================================================================================
if vLastRunDate <> vNowDate and vTimeslot = 1 then
 let vExecute = 1;
else

end if<div></div>

[rwunderlich]

BIServices wrote:For instance, we only create invoices once a day, so this could be skipped nearly all of the times, except for just after the invoicing process. We have more of this kind of fact tables that only need to be refreshed once a day because the source is only refreshed once or because we believe that one refresh a day is sufficient. On the other hand we also have some processes which we would like to monitor on a more frequent basis

The approach we take having a number of "master file load" qvws who's only job is to load data and create a QVD that other qvws consume. This keeps the loading and scheduling logic out of the user facing applications. So things like customer master get created once per day, order status once per hour. A user facing qvw that uses this data need only load these QVDs.

-Rob