topic Re: Understanding Load with SQL in QlikView

Understanding Load with SQL

Thu, 16 Jun 2011 13:48:11 GMT

Hi, i'm trying to understand the Load functionality but i cant make it work with my SQL statement.

I want first to Load some SQL statement and then use this Resident table to filter some value.

I have this:

ODBC CONNECT TO [DB] (XUserId is CHXfAKVGTZNMTbUF, XPassword is KXVLKHFMSLdIXXJGQJMCDRC);

LOAD *;

SQL SELECT external_system_id from nsu_external_systems ;

ODBC CONNECT TO [DB2] (XUserId is RTJJKKVGTBNETYYB, XPassword is PWVHVBVOBbceGXJGQJMCTSA);

LOAD *;

SQL SELECT description, description_clean, responsible, external_system_id from T53421.nsu_system_interfaces_errors;

NoConcatenate

LOAD *

Resident A

Where external_system_id < 5;

This doesnt work, it says "Field external_system_id not found" or something like that.

I dont get it

Can someone help me please

Thanks!

Understanding Load with SQL

pat_agen — Thu, 16 Jun 2011 14:11:12 GMT

hi,

the field may have been sent back in uppercase. So you would need to write

where EXTERNAL_SYSTEM_ID < 5

Re: Understanding Load with SQL

Thu, 16 Jun 2011 14:23:25 GMT

Oh! Pat you are a genious

Ok, that fixed the error but is it brings me all the A table. Is like the Where clause isnt working

Any idea?

Thanks!

Understanding Load with SQL

pat_agen — Thu, 16 Jun 2011 14:31:31 GMT

start by taking out your load and sql call to table B (you can always bring this back in afterwards)

your field EXTERNAL_SYSTEM_ID is present in both A and B and I'd get that out of the way to work out why your 3rd call isn't giving you your expected results.

by the way if you really don't want the EXTERNAL_SYSTEM_ID which are gretaer than 5 you should put your where clause in your sql and just not bring them into qv.

Re: Understanding Load with SQL

Thu, 16 Jun 2011 14:33:59 GMT

Pat i know that i can filter my statement directly on the SQL statement.

I put this example to make some tests, but i dont understand why it brings me all records and not only those which are <5.

Thanks for your help, i will continue testing it

Re: Understanding Load with SQL

Thu, 16 Jun 2011 15:24:29 GMT

Another question:

How can i do a "Like" statement on a resident table B base on a value on a resident table A?

Something like this...

LOAD DESCRIPTION as "DESCRIPTION_2"

Resident A,B

Where FIELD_FROM_A LIKE '%FIELD_FROM_B%';

Thanks!

Re: Understanding Load with SQL

nagaiank — Thu, 16 Jun 2011 18:35:17 GMT

What is the datatype of the field EXTERNAL_SYSTEM_ID in the SQL Server table? Is it varchar or numeric? Depending on this, you may modify your WHERE clause.