Checking for new data in script

[justin_morley]

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

[rajeshvaswani77]

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

[justin_morley]

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

[justin_morley]

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'