topic Re: Error in script in QlikView

Error in script

schultem — Thu, 05 Apr 2012 14:37:49 GMT

I need some help. I made this script (copied a bit from an example on this community).

ODBC CONNECT TO [algemeen;DBQ=ALGEMEEN] (XUserId is HXeGdXNMUTeA, XPassword is AKeZORRGYZMEXZNB);

Table:
Load *;
SQL SELECT 'gabel' omgeving, a.owner owner , a.table_name tabel_naam, b.num_rows num_rows, b.last_analyzed last_analyzed
FROM all_tables@gabel a
,    dba_tab_statistics@gabel b
where a.owner not in ('SYSTEM', 'SYS')
and    a.table_name = b.table_name ;
CONCATENATE
Load *;
SQL SELECT 'gpbel' omgeving, a.owner owner , a.table_name tabel_naam, b.num_rows num_rows, b.last_analyzed last_analyzed
FROM all_tables@gpbel a
,    dba_tab_statistics@gpbel b
where a.owner not in ('SYSTEM', 'SYS')
and    a.table_name = b.table_name ;

INNER JOIN (Table)
LOAD *
WHERE "Only in One Table?"
OR "Amount Different?"
;
LOAD
tabel_naam
,if(count(a.table_name)<2,-1) as "Only in One Table?"
,if(max(num_rows)<>min(num_rows),-1) as "Amount Different?"
RESIDENT Table
GROUP BY tabel_naam
;

I want to compare both tables. After reload of data I got error message:

Veld niet gevonden - <tabel_naam> (Veld niet gevonden = Field not found)

LOAD

tabel_naam

,if(count(a.table_name)<2,-1) as "Only in One Table?"

,if(max(num_rows)<>min(num_rows),-1) as "Amount Different?"

RESIDENT Table

GROUP BY tabel_naam

Can anyone help me to solve my problem? Thanks in advance.

Error in script

Jason_Michaelides — Thu, 05 Apr 2012 16:43:25 GMT

Maybe try putting the alias for tabel_naam in the QlikView LOAD rather than the SQL script?

Re: Error in script

llauses243 — Thu, 05 Apr 2012 23:55:09 GMT

Hi,

Pls to look image adjust ...

Good luck, Luis

Re: Error in script

schultem — Tue, 10 Apr 2012 13:06:34 GMT

Unfortunately this doesn't help. It still says:

Veld niet gevonden - <tabel_naam>

LOAD

tabel_naam

,if(count(tabel_naam)<2,-1) as "Only in One Table?"

,if(max(num_rows)<>min(num_rows),-1) as "Amount Different?"

RESIDENT Table

GROUP BY tabel_naam

Can anybody help me?

Re: Error in script

Miguel_Angel_Baeyens — Tue, 10 Apr 2012 14:04:56 GMT

Hi,

Shouldn't it be "table_name" as the name of the field instead of tabel_naam? Or does it load fine if you comment the script that does not work?

Miguel

Re: Error in script

schultem — Wed, 18 Apr 2012 07:41:30 GMT

Well I finally found the solution. With this syntax:

SQL SELECT 'gabel' omgeving, a.owner owner , a.table_name tabel_naam, b.num_rows num_rows, b.last_analyzed last_analyzed

FROM all_tables@gabel a

, dba_tab_statistics@gabel b

where a.owner not in ('SYSTEM', 'SYS')

and a.table_name = b.table_name ;

Qlikview creates the column names in CAPITALS! So my code is now:

LOAD

TABEL_NAAM

,if(count(TABEL_NAAM)<2,-1) as "Only in One Table?"

,if(max(NUM_ROWS)<>min(NUM_ROWS),-1) as "Amount Different?"

RESIDENT Table

GROUP BY TABEL_NAAM

I did CTRL-T to show the table structure and there I saw the capitals.