topic Need to ignore missing SQL tables on reload of data in QlikView

Need to ignore missing SQL tables on reload of data

stevedark — Tue, 05 May 2009 17:07:01 GMT

Hi there,

I am producing a QlikView document that I wish to point to many different servers with similar but not identical data layouts. I wish to have the complete set of tables that I may wish to pull in in the script - but the script to ignore tables that are not there.

Happy to poll the sysdatabases and sysobjects tables to find if the tables exist - but I don't know how to then do a conditional statement around the load of the missing table.

Would even be equally happy with just putting an 'on error' / 'try catch' type construct around that table - but I am not sure if one of those exists.

Obviously the desired result could be achieved by commenting parts of the script when pointing to different servers, but I would rather not have users having to do that.

Many thanks in advance for any pointers you can give me.

Cheers,
Steve

Need to ignore missing SQL tables on reload of data

Anonymous — Tue, 05 May 2009 19:27:35 GMT

Steve,
I think what you're looking for is the ErrorMode. See online help on this topic, I'm sure you'll figure out how to use it.
The simple way is to use ErrorMode=0, but you can do much more with it.

Need to ignore missing SQL tables on reload of data

Oleg_Troyansky — Tue, 05 May 2009 19:28:47 GMT

Search the On-line Help for an article "Error Variables" between ErrorMode and ScriptError, you can construct syntax similar to try... catch - there is a nice example there.

cheers,

Oleg

Need to ignore missing SQL tables on reload of data

stevedark — Tue, 05 May 2009 19:52:52 GMT

Thanks guys for the quick response. After your pointers the finished chunk of code looks like this:

set ErrorMode=0;
ErrorTemp:
SQL SELECT *
FROM DBName.dbo.tbl_ErrorCode
WHERE 0 = 1;

if ScriptError <> 4 and ScriptError <> 5 then
set ErrorMode = 1;
ErrorCode:
LOAD ErrorCode as ErrorCodeId,
ErrorDesc as ErrorMessage;
SQL SELECT *
FROM DBName.dbo.tbl_ErrorCode;
end if

set ErrorMode = 1;
drop table ErrorTemp;

I don't tend to like relying on turning off error trapping - it is there for a reason after all, but using the first query to check for a specific couple of error conditions before retrying means that it won't skip over a table because of a mistyped field name.

I guess this could be re-written to use a select from sysdatabases and sysobjects, and then take a 'peek' at the data - but this solution does me fine for the demo I am putting together.

Cheers,
Steve

Need to ignore missing SQL tables on reload of data

rwunderlich — Wed, 06 May 2009 00:09:25 GMT

Steve,

Useful bit of code.

How about writing this up as a wiki page?
http://community.qlik.com/wikis/qlikview-wiki/default.aspx

-Rob

Need to ignore missing SQL tables on reload of data

stevedark — Wed, 06 May 2009 04:34:58 GMT

Hi Rob,

Thanks for the tip about putting the code on a wiki - but I am sure there are more elegant solutions. Feel free to post the code elsewhere though if you wish.

It's worth noting that the second instance of setting the error mode back to 1 is better off after the drop - as the temporary table is not created if none of the tables being checked for in that way are referenced. In the scenario I am using the code it is an either one table or the other situation - so it makes little difference.

Cheers,
Steve

Need to ignore missing SQL tables on reload of data

stevedark — Tue, 28 Jul 2009 17:36:22 GMT

Hullo,

The error trap suggested above worked fine, and following the motto 'if it ain't broke' I have run with it for some time. However, as soon as I tried to get Publisher to do a reload with the error trap in place it failed - seemingly ignoring the fact that I was instructing it to ignore errors.

I have therefore coded it with a reference to Sysdatabases, and it works a treat:


Sysdatabases:
LOAD
 name as DatabaseName;
SQL SELECT *
FROM master.dbo.sysdatabases;
let vHasBanking = FieldIndex('DatabaseName', 'ABCBanking');

if vHasBanking > 0 then
 ErrorCode:
 LOAD ErrorCode as ErrorCodeId,
 ErrorDesc as ErrorMessage;
 SQL SELECT *
 FROM ABCBanking.dbo.tbl_ErrorCode;
end if

Obviously both developer and publisher user accounts must have read rights to the system tables, and that was already the case in this environment.

Cheers,
Steve