Where Exists() - Field not found

[ttollin11]

I am having some trouble using a Where Exists when trying to load some data from a dimension table. Files A - C are used prior to this section to load data from extracts, and each have the Officer Code (or Primary Officer Number as shown) fields shown for the temp table.

I am using a similar method in a QlikView application and it is working, so I am not sure what I am doing wrong. Any tips?

tempOffList:
Load
"Officer Code"
FROM fileA
WHERE Today() - "Expire Date" > -30;


Concatenate(tempOffList)
Load
"Officer Code"
FROM fileB
WHERE Today() - "Expire Date" > -30;


Concatenate(tempOffList)
Load
"Primary Officer Number" as "Officer Code"
FROM fileC
WHERE Today() - "DOCUMENT EXPIRATION DATE" > -30;


Concatenate(tempOffList)
Load
"Officer Code"
FROM fileD
WHERE Today() - "Covenant Expiration Date" > -30;

Officer Table:
LOAD
    Officer_cd as "Officer Code",
    Other Data
FROM qvdA
Where Exists("Officer Code");

Drop Table tempOffList;

[vamsee]

Try this

Office_Table:

NoConcatenate

Load

          Officer_cd as "Officer Code",

          Other Data

From qvdA

Where Exists ("Officer Code", Office_cd);

[vamsee]

Exists() Function can take two parameters when the column names are not same or when you want to use an expression.

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/InterRecordFun...

Also, on the same note do not use exists when you are concatenating the 2nd table with the first one. (Not applicable to this situation)

https://qlikcentral.com/2016/01/21/the-problem-with-where-not-exists/

[sunny_talwar · Accepted solution]

You can also try this to keep the optimized load for your qvd load

tempOffList:

Load "Officer Code" as Officer_cd

FROM fileA

WHERE Today() - "Expire Date" > -30;

Concatenate(tempOffList)

Load "Officer Code" as Officer_cd

FROM fileB

WHERE Today() - "Expire Date" > -30;

Concatenate(tempOffList)

Load "Primary Officer Number" as Officer_cd

FROM fileC

WHERE Today() - "DOCUMENT EXPIRATION DATE" > -30;

Concatenate(tempOffList)

Load "Officer Code" as Officer_cd

FROM fileD

WHERE Today() - "Covenant Expiration Date" > -30;

Officer Table:

LOAD

    Officer_cd as "Officer Code",

    Other Data

FROM qvdA

Where Exists(Officer_cd);

Drop Table tempOffList;

[balabhaskarqlik]

Officer Table: 

LOAD 

    Officer_cd as "Officer Code", 

    Other Data 

FROM qvdA 

Where Exists("Officer Code", Officer_cd); 

Drop Table tempOffList; 

[ttollin11]

Went with this option. Thanks as always, Sunny.