topic Re: Only reload QVW if condition is met, cancel if not in QlikView

Only reload QVW if condition is met, cancel if not

Anonymous — Mon, 21 May 2012 16:07:30 GMT

Hi,

hoping someone can answer this....

Have a QVW that pulls data from a SQL Datawarehouse (which gets populated from other sources via SSIS).

The document reloads via the QMC once an hour. However sometimes the Datawarehouse load from SSIS is still in progress when the QMC reload kicks off.

This is causing partial data to appear in the QVW. To avoid this I have tried removing QMC from the equation and tried adding the load of the QVW to SSIS by using the command line prompt and a batch file. However this process kept hanging in the background and wasn't reliable enough

Some back to using the QMC to schedule reloads.

ideally we want the QVW to only reload the data if the SSIS job has completed. If it hasn't completed, abort the reload and leave the QVW as is

My question is, it possible to have in the QVW script do something along the lines of:

e.g Check a SQL table to see if Job has completed

If Completed > Execute the scripts

Else Cancel Reload

Exit QVW without Saving any changes

Thanks in advance

Only reload QVW if condition is met, cancel if not

Jason_Michaelides — Mon, 21 May 2012 17:37:54 GMT

I've not tried this and it might save the qvw with no data (obviously not good) but try this at the top of your script after the settings:

//assuming a 1 field, 1 row table in SQL populated by SSIS on completion

Check:

LOAD

IsCompleted

; SQL SELECT IsCompleted From...;

IF Peek('IsCompleted') <> 'Yes' THEN

Exit Script;

END IF;

...Rest of script...

Hope this helps,

Jason

Re: Only reload QVW if condition is met, cancel if not

jonathandienst — Mon, 21 May 2012 19:11:45 GMT

To ensure that the qvw is never left in an empty state, what about:

start with binary load. This must be the first line of the script.
check whether SSIS job is complete (eg if the job drops a file or updates a database table on completion).
If the job is not complete, terminate.
If the job is complete, drop all the tables and reload normally.

I have added an idea a while ago for the ability to trigger a user defined error in the script that would result in the same behaviour is a normal script error - ie the model gets reread from disk after an error. This would make this type of problem easy to solve - go to ideas and vote for it (I did it a while ago and cant remember what I called it!)

Jonathan

Re: Only reload QVW if condition is met, cancel if not

jonathandienst — Mon, 21 May 2012 19:19:04 GMT

Idea was called "Exit script error code" in September 2011

Regards

Jonathan

Re: Only reload QVW if condition is met, cancel if not

rbecher — Mon, 21 May 2012 19:33:24 GMT

Hi,

there are two ways to accomplish:

1. Trigger the QlikView server reload after the data warehouse is loaded from SSIS via EDX:

http://community.qlik.com/docs/DOC-2650

2. Use a partial reload instead a (full resp normal) reload where all data remain in the QVW even if no additional data are loaded.

HTH,

- Ralf

Re: Only reload QVW if condition is met, cancel if not

Anonymous — Tue, 22 May 2012 15:22:59 GMT

Hi Jason

Tried the below but unfortunately it saves the document with no data

Thanks anyway

Re: Only reload QVW if condition is met, cancel if not

Anonymous — Tue, 22 May 2012 15:24:11 GMT

Hi Jonathan,

Thanks.

Will give this a try and let you know

Only reload QVW if condition is met, cancel if not

Anonymous — Thu, 31 May 2012 14:00:55 GMT

Hi Jonathan,

Thanks for the suggestion. The binary load worked along side Jason's suggestion

For info, the way I finally did it was

BinaryLoad the document in itself
Pull Back Status of Job via SQL
Use the IF Statement
IF TRUE then drop the Status table - this needs to be done to ensure the Binary Load doesn;t get changed
Exit the Script
IF Not true the drop the tables that were loaded from the binary load
This loop works very well:

let numTables = NoOfTables();

for i=1 to $(numTables)

let tt = TableName(0);

drop table [$(tt)];

Normal Reload Scripts

Thanks for the help

Re: Only reload QVW if condition is met, cancel if not

Tue, 18 Dec 2012 10:39:02 GMT

Thanks Jonathan.

Binary at first

then if condition=1 then drop all the tables and reload again

else exit script

is perfect !