Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
The simple attached applications (and sample code) will allow you to setup your Qlik reload processing (in QlikView Server, Publisher or Qlik Sense) to kick off directly on completion of a process on a source system (e.g. an ETL process on a DW).
A simple process.
Tip 1 - Add in a pause to the loop so that your application doesn't spam the DB with requests.
A sleep command is added which pauses the reload following an unsuccessful pass. This could be set to every 10 or 60 secs to reduce a constant barrage of requests being sent to the DB. When the condition is met the script will exit the loop before the sleep is activated.
Tip 2 - Multiple triggers to set off different reload streams.
If you have a more complex environment where reload windows are constrained you could setup multiple triggers based on different event outputs from the DW. For example, where the DW completes a sales transactions fact but has many other dependent tables still to process you could have an event written to allow one stream of your reload to commence. The other streams could rely on different event triggers when all table reloads are complete.
Tip 3 - Keep it simple!
I have seen many different ways of setting up processes such as this, most of which IMHO are over-complicating something quite simple. One of the pleasures of working with Qlik products is the relative lack of complexity and the ease of understanding each implementation you come across. This is something we should promote rather than the tendency in IT to introduce/play with another bit of code or tool which makes debugging issues more technical. It also results in hidden steps in the process when someone new is introduced to the implementation, and documentation rarely gets the message across. When I say 'hidden' I would suggest that an initial trigger application in the reload schedule is very clear to someone new to the specific Qlik implementation, actions in source DBs or other programs are not immediately clear and require some digging etc to find and understand.
The example code based on a .txt file in Qlik Sense - change the LIB reference to a defined folder location for QlikView.
LET vDWDate = null();
LET vToday = num(today());
// Start loop script that will continue until condition is met.
Do until vDWDate=vToday
Trigger:
LOAD num(@1) as DWDate
FROM [lib://Trigger/Trigger.txt]
(txt, codepage is 1252, no labels, delimiter is '\t', msq);
LET vDWDate = peek('DWDate');
// where condition is met, exit and complete.
if vDWDate=vToday then
Exit Do
// where condition is not met, drop that table ready to restart the load process.
else
DROP TABLE Trigger;
// The sleep means that we can control the frequency of calls to the source database (or file).
Sleep 10000; // 10000 milliseconds = 10 seconds.
end if
// where condition is not met then restart loop.
loop
Try it yourself!
The attached files can be used to test this process.
Date formats
It is important to match the formats of the dates in both files. Check your global variables in the main tab to understand the settings you are assigning to the vToday variable as this will need to match the data being read from the source to complete the process. The screenshot below shows where to check in Qlik Sense, the same applies in QlikView.
Hi Anand,
The scheduling is done through the QV server QMC and the process documented here allows a simple approach to check when a DW load has completed rather than assume a time with a set schedule. You still need to schedule this step as the starting point for any reload processing you are doing as a separate QVW or as the first sheet in your script.
Cheers
Richard
Thanks for your response
I want to automate the entire tasks scheduling process. We have autosys agent installed on servers. I am trying to find solution to integrate Autosys and QV. I have gone thru Qlik Community but i don't find any proper document on the same. Can you please share your thoughts on how to integrate QV and Autosys? If any documents would be really helpful.
Regards
Anand
Hi Marcus and others,
Qlik Sense doesn't have EDX triggers as such. Sure you can interact with the API, but there isn't a built-in option to trigger tasks on an external event.Regards,
Rick
HI Rich,
Thanks for sharing this.
But this will be a problem when multiple triggers are there.
e.g.- In our case. There 30 different clients data will refresh at a different time interval and hence different Database tables are maintained. And we are keeping a single app where all the clients data will be loaded but in a different time interval.
If Client A data is available @11.00 AM the Dashboard will be refreshed at 11.00 AM for client A
If Client B data is available @2.00 AM the Dashboard will be refreshed at 2.00 AM for client B.. and so on.
So there, we are keeping multiple Start Task for each of this client. In such case how can we Trigger the First Task for each client using your methododlogy.
Best Regards
Sunil
Great post, i have used this and the info from the comments, to add in a couple of extra steps for the scenario I had for QMC tasks and not filling all concurrent reloads slots available.
Scenario: As task one in task chain perform the below checks and create success or failure in QMC.
1. Check to see if source table has updated, (on successful update entry created with today's date.) if so successfully complete task, next task is set to run on successful completion of this task, it can load updated data and continue.
2. If table has not updated wait/sleep and try again later.
3. If after retry limit met, log details and create failure in task chain.
The reason for the failure is deliberate, so that task two doesn't run as the data has not updated. This is set up in the QMC to trigger on successful completion of task one. I then have an app that reloads the failure details for that day so it can be restarted later.
Below is what i'm using, the inline table would be replaced with the source you are checking, amending the date in that table will go through either step.
LET vSleepCount = 0; // setting sleep to 0
LET vDWDate = null(); // clearing start value
LET vToday = num(today()); // creating today as number to reference to max date value in table this can avoid format issues
LET vStarttime = now(); // creating start time stamp used for output tables
// Start loop script that will continue until either condition is met.
Do until vDWDate=vToday
LET vSleepCount = vSleepCount+1; // adds 1 to count each run until hits max
TRACE number of runs $(vSleepCount);
Trigger:
LOAD *, num(MAX_RUN_DATE1) as LAST_UPDATE_DATE // used for check
, date(MAX_RUN_DATE1) as MAX_RUN_DATE // used for output tables
;
// data is grouped so just picks up latest data from check.
LOAD WORK_ID, SCRIPT, FREQUENCY, max(RUN_DATE) as MAX_RUN_DATE1
group by WORK_ID, SCRIPT, FREQUENCY
;
// edit run date in inline table below to todays date to see success routine or previous day to see failure routine, this results in load error but is required for scenario.
load * inline [
WORK_ID, SCRIPT, FREQUENCY, RUN_DATE
1001, Test Script, Weekly, 13/01/2020
]
;
LET vDWDate = peek('LAST_UPDATE_DATE');
// where condition is met, exit and go to RunSection which successfully completes script and logs results.
if vDWDate = vToday then
trace UPDATED;
Set ActiveSection = 'RunSection';
Exit Do
// if after retries hits max number below then exit and goes to failure section to create table entry and fail task.
elseif vSleepCount = 4 then
trace Not Updated;
Set ActiveSection = 'FailSection';
exit do
else
// where condition is not met, drop the trigger table, sleep and then restart the check.
trace Continue Loop;
DROP TABLE Trigger;
// The sleep means that we can control the frequency of calls to the source database (or file).
Sleep 5000; // 1000 milliseconds = 1 second, set to 5 seconds for example but alter to suit needs 600000 = 10 minutes with 4 sleeps task runs and retries 3 additional times totalling 30 minutes then ends
end if
loop
Switch ActiveSection
case 'RunSection'
COMPLETED_TABLE:
Load
'$(vStarttime)' as TASK_START_TIME
, now() as QMC_COMPLETION_TIME
, DocumentName() as APP_ID
, WORK_ID
, SCRIPT
, FREQUENCY
, MAX_RUN_DATE as SOURCE_MAX_DATE
, date(NOW()) as EXPECTED_MAX_DATE
, '$(vSleepCount)' as NUMBER_RETRIES
Resident Trigger;
drop table Trigger;
//store COMPLETED_TABLE into [lib://Required File Location/QMC_TASK_SUCCESS.qvd] (qvd);
case 'FailSection'
FAILURE_TABLE:
// if history is required load previously saved QVD and concatenate load new line to QVD then resave over existing file
// LOAD
// *
// FROM [lib://Required File Location/QMC_TASK_FAILURE.qvd]
// (qvd);
// Concatenate
Load
'$(vStarttime)' as TASK_START_TIME
, now() as QMC_FAILURE_TIME
, DocumentName() as APP_ID
, WORK_ID
, SCRIPT
, FREQUENCY
, MAX_RUN_DATE as SOURCE_MAX_DATE
, date(NOW()) as EXPECTED_MAX_DATE
, '$(vSleepCount)' as NUMBER_RETRIES
Resident Trigger;
drop table Trigger;
//store FAILURE_TABLE into [lib://Required File Location/QMC_TASK_FAILURE.qvd] (qvd);
// this section is required to halt QMC tasks process with error.
// if you wish to see the failure results comment out the below 2 lines
Failure:
load * resident notupdated;
// error is due to notupdated table doesn't exist with The following error occurred: Table 'notupdated' not found