Only reload QVW if condition is met, cancel if not

[Anonymous]

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

[Jason_Michaelides]

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

[jonathandienst · Accepted solution]

Hi

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

[jonathandienst]

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

Regards

Jonathan

[rbecher]

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

[Anonymous]

Hi Jason

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

Thanks anyway

[Anonymous]

Hi Jonathan,

Thanks.

Will give this a try and let you know

[Anonymous]

Hi Jonathan,

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

For info, the way I finally did it was

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

let numTables = NoOfTables();

for i=1 to $(numTables)

let tt = TableName(0);

drop table [$(tt)];

next

8. Normal Reload Scripts

Thanks for the help

[#9]

Thanks Jonathan.

Binary at first

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

else exit script

is perfect !