Field Not Found using Load Script

[H-Zey]

Hi All,

I'm having a problem referencing a field from a previously loaded table. Here's what I have in my script:

Any help is greatly appreciated

-----------------------------------------------------------------------------------------------------------------------------------------------

TEMP_TABLE1:
Load
FIELD1 AS TEMP_FIELD1,
FIELD2 AS TEMP_FIELD2,
FIELD3 AS TEMP_FIELD3,
FIELD4 AS TEMP_FIELD4,
FIELD5 AS TEMP_FIELD5;

SQL SELECT DISTINCT ALIAS1."FIELD1",
ALIAS1."FIELD2",
ALIAS1."FIELD3",
ALIAS1."FIELD4",
ALIAS1."FIELD5"

FROM "DATABASE"."TABLE1" as ALIAS1

INNER JOIN "DATABASE"."TABLE2" as ALIAS2
ON

ALIAS1.FIELD6 = ALIAS2.FIELD6
and
ALIAS1.FIELD7 = ALIAS2.FIELD7

WHERE ALIAS1.FIELD8 = 'VALUE'
AND ALIAS2.FIELD9 = 'VALUE';

INNER JOIN

TEMP_TABLE2:
Load
FIELD1 as TEMP_FIELD1;

SQL SELECT DISTINCT ALIAS1."FIELD1"

FROM "DATABASE"."TABLE1" as ALIAS1

INNER JOIN "DATABASE"."TABLE2" as ALIAS2

ON ALIAS1.FIELD6 = ALIAS2.FIELD6
and
ALIAS1.FIELD7 = ALIAS2.FIELD7

WHERE ALIAS1.FIELD8 = 'ANOTHER VALUE'
AND ALIAS2.FIELD9 = 'VALUE';

TEMP_TABLE3:
Load
FIELD1 AS XX_FIELD1;

SQL SELECT
DISTINCT ALIAS1."FIELD1"
FROM "DATABASE"."TABLE" as ALIAS1
INNER JOIN "DATABASE"."TABLE" as ALIAS2
ON ALIAS1.FIELD2 = ALIAS2.FIELD2
and
ALIAS1.FIELD3 = ALIAS2.FIELD3
WHERE FIELD4 = 'VALUE'
AND ALIAS1."FIELD4" is not null;

/* The above load script section executes successfully and gives me the desired result in ALL THREE TABLES */

/* here's where the error occurs. I want to create a new table (TEMP_XX_DISTINCT)  that loads  XX_FIELD1 from the above table (TEMP_TABLE3) only if it does not exist in  table TEMP_TABLE1 and field TEMP_FIELD1.
The error says 'TEMP_TABLE1.TEMP_FIELD1' not found 

Thanks

*/

TEMP_XX_DISTINCT:
load XX_FIELD1 From_Field(TEMP_TABLE3,XX_FIELD1)
Where not Exists(TEMP_TABLE1.TEMP_FIELD1);

[Mark_Little]

Hi

Within the Qlik Script you do not need to tell it which table it does not exist in Where not Exists(TEMP_FIELD1);

Qlik does not reference Table.Field like SQL

[H-Zey]

Hi Mark,

Thanks for the reply. I have tried with the table name and without. still the same error.

What I noticed is that I'm unable to access any table in the load script that isn't the one right above. 

[marcus_sommer]

Only if you named a field like: F1 as TableXYZ.F1 or using a qualify-statement you will get the table-name within the field-name. Further all tables with an identically structure will be auto-concatenated unless they are loaded with a noconcatenate-statement - so your field and/or they table which you want to access may not exists. A look on the table-structure will show you what's really there.

- Marcus

[H-Zey]

Hi Marcus,
If I comment out this last bit 

TEMP_XX_DISTINCT:
load XX_FIELD1 From_Field(TEMP_TABLE3,XX_FIELD1)
Where not Exists(TEMP_TABLE1.TEMP_FIELD1);

My script works and I see the tables and the field names. I went further and I commented out the
"Where not Exists(TEMP_TABLE1.TEMP_FIELD1);" and that works as well.

Also:
(1) I have added the unqualify and nonconcatenate to the script.
(2) I removed the "TEMP_TABLE1." prefix from the where clause

Am I doing something in the where clause that is not allowed in Qlik?

[marcus_sommer]

You are using a load From_Field which is a quite specific feature which is very rarely needed (at least by us). Therefore I'm not sure if it supports any where-clauses and/or if the entire syntax ok. If you don't have a special reason for it you may change the load to:

TEMP_XX_DISTINCT:
load XX_FIELD1 resident YourTable
Where not Exists(XX_FIELD1); // or  something like: not Exists(FIELD, Expression);

- Marcus

[H-Zey]

Hi Marcus,

First,

I have changed that section to now be only the load without the where clause and as you recommended

load XX_FIELD1 Resident TEMP_TABLE3;

The script loads and all the tables that I want are there.

I still get an error once I add the where clause to the above code.

Again I feel like it is not letting me access objects that are farther up the load script. 

[marcus_sommer]

In general you could access all previously loaded fields and tables.

Important is that they are properly written - Qlik is here case-sensitive and that they are named like they exists within the tables (if they have a table-prefix with it and if not the belonging table-name must not be applied) and further that the names are wrapped in [] or "" if they contain any space or special chars. 

Further the field must be included within the specified (resident) table. A direct load from a field is also possible with the from-field feature or by looping through the distinct values of the belonging system-table of this field but like above hinted this are specific features which are only in certain use-cases helpful.

- Marcus