topic Checking for new data in script in QlikView

Checking for new data in script

justin_morley — Tue, 27 Aug 2013 12:37:13 GMT

Hi all,

I'm struggling to write a script that will help me check for new data in my data source.

My setup currently runs every half an hour to refresh data come hell or high water (I would like this to be a shorter period ideally. High/immediate data availability is a requirement of this system). This is obviously resource intensive, hammers the database as well as my QV server. The job takes about 6 minutes to run. I have Publisher set up and the completion of my QVD creator script triggers my application rebuilding. There is clearly no point in all this running on the dot every half an hour if there is no new data.

What I require is the following:

- My QVD creator script checks a field in the database (we have audit logs noting when data has changed)

- This is compared to a field saved into a QVD used to store the last load.

- If there is more recent data, run the script to import the new data, otherwise exit the whole job gracefully, including the application refresh

NB this is not an incremental load, although similar. The data is replaced completely rather than incrementally changed

The bit I'm struggling with here is the syntax of my If statement - how to compare the different dates, how to handle dates in variables etc.

Thanks in advance for any help,

Justin

Re: Checking for new data in script

rajeshvaswani77 — Tue, 27 Aug 2013 12:42:51 GMT

Hi Justin,

Please use an Incremetal Load. The standard way it is done in QlikView.

Your table needs to have a Primary Key as well as a field that has a date time stamp.

Everytime the job runs it will pick the fresh records only. There are various scenarios.

Please see whitepaper that has the details of how to do this.

Thanks,

Rajesh Vaswani

Re: Checking for new data in script

justin_morley — Wed, 28 Aug 2013 09:44:26 GMT

Hi Rajesh,

Thanks for your reply. In truth I've been deliberately avoiding using incremental load due to the complexity of the underlying data - the main data souce is an Oracle database containing 20 materialised views built on >130 tables, and propagating the modification date to that lot is no mean task! It's probably time to bite the bullet and finally implement incremental load... Can anybody help me with these follow-on questions?

As everything hangs off a single table at the top of my data hierarchy, is there a efficient way to avoid having to attach a modified date to every table?
My second question above isn't answered by any of the documentation I've seen about implementing incremental load. If no changes are made to the tables, what's the best way to prevent execution of the triggered job to build my app? Throw an "exception" somehow to bring down the job?

Thanks,

Justin

Re: Checking for new data in script

justin_morley — Thu, 29 Aug 2013 10:23:23 GMT

Thanks to Dave Denscombe from QlikTech for emailing me offline this list of suggestions for handling cancelling the rebuild job:

The forced failure (i.e. pretending to throw an exception. see here How to interrupt reload and throw error if field is empty?) is an option but a bit “yuk” as you say
Another would be very old fashioned, write out a dummy file which the downstream application
picks up to make the decision
Alternatively, set up the downstream application using an EDX trigger. The first script if
there is something to do calls the EDX command line tool to start the second
job

I shall investigate the EDX option which seems 'least worst'