"Field not found" is prompted when joining tables

[#1]

Hi,

I'm new to QlikView. When I tried to join 2 tables using the following script, it prompted "Field <agent_id> not found" when I debug it. The field "agent_id" exist in both table_1 and table_2. Can you please let me know what's wrong with the following script? Thanks.

Table_A:

LOAD

agent_id as AgentNO

;

SQL

rownum <= 10;

Join

(Table_A) select * from table_1

where

LOAD

agent_id

as AgentNO;

SQL

select * from table_2;

[prieper]

Have you truncated your script? What does the "SQL" stand for? Usual syntax would be

Table_A: LOAD agent_id AS AgentNO; SQL SELECT * FROM YourSQL_Table;
JOIN (Table_A) LOAD agent_id AS AgentNO; SQL SELECT * FROM AnotherSQL_Table;

Actually if you have same naming of the fields, you do not need to JOIN, QlikView will automatically add the second table to the first one.

HTH
Peter

[#3]

Hi Peter,

Thanks for your quick reply. The "SQL" is just to identify the following script is a SQL statement (I guess it's optional to write it or not).

As there are some other fields having the same name in these 2 tables (e.g. last_modified_date, last_modified_by etc) which should not be used for joining. Hence, I'd like to join them using a specific field - <agent_id>. Would you please tell me how should I make it work? Many thanks.

Regards,

Lewis

[#4]

Had this issue as well. You need to add the fully qualified name to get the joins done in SQL e.g.

LOAD FieldA;

SQL SELECT "dbname".dbo."tablename".FieldA as FieldA

FROM "dbname".dbo."tablename1"

JOIN "dbname".dbo."tablename2"

ON "dbname".dbo."tablename1".FieldA = "dbname".dbo."tablename2".FieldA

Hope this helps.

Mike

[#5]

Hi Mike,

It works. Thanks for your solution.

Lewis