Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW

Reload process trigger on external event (e.g. completion of DW load)

No ratings
cancel
Showing results for 
Search instead for 
Did you mean: 
richbyard
Contributor III
Contributor III

Reload process trigger on external event (e.g. completion of DW load)

Last Update:

Sep 6, 2015 1:28:36 AM

Updated By:

richbyard

Created date:

Sep 6, 2015 1:28:36 AM

Attachments

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.

  1. At the end of the ETL process the source database can be made to write a success value to a specific table in the DB for us to use as an indicator of a successful reload. For example, this might be today's date.
  2. Create a Qlik application (QlikView or Sense) which will be scheduled at the start of the reload process and all other applications will be dependent on this, and any other dependencies they have. This initial application is a simple script that loops until a condition is met by the data being loaded. In this example, when the date loaded from the source DB is equal to today.
  3. This application will be scheduled around the time of the earliest conceived completion of the source DB process or slightly before. This ensures that our reload process is ready and waiting for a completion record, in effect polling the source DB for a completion record.
  4. As this application completes all subsequent reloads will start. This ensures that your reload process kicks off as soon as the source DB has completed processing and never before.

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.

  1. Download them and have them in the same folder.
  2. Kick off the reload of the QlikView document and watch as it loops through as the condition is not met.
  3. Open the Trigger.txt file and change the date to today's date (format D/M/YYYY unless you change your date settings in the script)
  4. Save the .txt file and watch as the next time the application loops it will complete the reload.
  5. Simple!

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.

DateFormat.png

Comments
rohitk1609
Master
Master

Yes I implemented it successfully

AcumenJonFoote
Partner - Contributor II
Partner - Contributor II

Thanks Rich

Just what I needed to fix a reload issue with a client..

Cheers

Jon

0 Likes
gbarrett
Partner - Contributor III
Partner - Contributor III

Hi rohitk1609,

Do you have a copy of the completed QVF or script that you can share with me?

Thanks,

George

0 Likes
richbyard
Contributor III
Contributor III

Hi George,

The qvf example and the text file is attached to the post on Community so you can download from there. You'll just need to repoint the folder connection to where you save the text file.

Cheers

Richard

0 Likes
rohitk1609
Master
Master

Hi George,

I have asked to my DB team to create one flag in Database which will set to 1 when new data comes to DB and writing  code below for the same:

/ SET ThousandSep=',';

// SET DecimalSep='.';

// SET MoneyThousandSep=',';

// SET MoneyDecimalSep='.';

// SET MoneyFormat='$#,##0.00;-$#,##0.00';

//SET TimeFormat='h:mm:ss TT';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY h:mm:ss[.fff] TT';

// SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

// SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

LET vDWDate = 0;

LET vToday = num(today());

// Start loop script that will continue until condition is met.

Do until vDWDate=1

Trigger:

LOAD

//num(Timestamp(@1,'DD/MM/YYYY hh:mm:ss tt')) as DWDate

@1 as  DWDate

FROM [lib://Sample Data (dr_rkumar)/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=1 then

//write your sql statment which will reset Isrefreshrequired flag to 0;

  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

Please mark my comment helpful if I answered your question properly!

0 Likes
gbarrett
Partner - Contributor III
Partner - Contributor III

Richard, I was looking more for Rohit's solution that can be used multiple times in a single day.

Rohit, I actually got your original script working by modifying the "DO UNTIL" line to this : DO UNTIL vDWDate > vToday. I am curious though, for the script above, what resets the database flag back to 0 after Qlik has loaded the data?

Thank you.

0 Likes
rohitk1609
Master
Master

What you need to do, you need to add one SQL statement to set Isrefreshrequired flag to 0,

Use SQL keyword and write the query which will set the Isrefreshrequired flag to 0. Its just an small statement, syntax depends on type of server.


Please mark my comment helpful if I answered you correctly!


Best,

Rohit

0 Likes
denniste
Contributor II
Contributor II

The recommended solution looks so simple and makes you wonder why it's not as common as the EDX implementation.  We have implemented something similar but placed the sleep(wait time) as a separate pause task.

If I have multiple trigger jobs, wouldn't it take up the all available Qlikview engine?  Or is there an option to exclude this task from the count of running jobs.  FYI. Pause tasks are not allocated an engine.

Allocating new QlikView Engine. Current usage count=0 of 6 (of type non-reader).

0 Likes
nandhuvji
Contributor
Contributor

Hello Richard,

I don't want to manually run QV application as the DB load completion time may change every week depends on the data volume and other factors. Once the DB load complete, i want QV application to be scheduled automatically. 

My requiremnent is to  to schedule a job weekly (every saturday) based on DB load completion w/o any manual intervention. Please let me know how to implement the same.

Regards

Anand

0 Likes
richbyard
Contributor III
Contributor III

That is a consideration for sure as yes it would use one of the available engine slots... There is no simple way around this without moving to API (or EDX) calls to Qlik from the relevant different data sources (as I assume you have many in this case) as they complete...

0 Likes
Version history
Last update:
‎2015-09-06 01:28 AM
Updated by: