Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
schultem
Contributor III
Contributor III

Error in script

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.

1 Solution

Accepted Solutions
schultem
Contributor III
Contributor III
Author

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.

View solution in original post

5 Replies
Jason_Michaelides
Luminary Alumni
Luminary Alumni

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

llauses243
Creator III
Creator III

Hi,

Pls to look image adjust ...

Good luck, Luis

schultem
Contributor III
Contributor III
Author

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?

Miguel_Angel_Baeyens

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

schultem
Contributor III
Contributor III
Author

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.