topic Re: Where Exists() - Field not found in App Development

Where Exists() - Field not found

ttollin11 — Fri, 17 Aug 2018 17:54:02 GMT

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;

Re: Where Exists() - Field not found

vamsee — Fri, 17 Aug 2018 18:02:06 GMT

Try this

Office_Table:

NoConcatenate

Load

Officer_cd as "Officer Code",

Other Data

From qvdA

Where Exists ("Officer Code", Office_cd);

Re: Where Exists() - Field not found

vamsee — Fri, 17 Aug 2018 18:05:27 GMT

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/InterRecordFunctions/Exists.htm

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/

Re: Where Exists() - Field not found

sunny_talwar — Fri, 17 Aug 2018 18:18:51 GMT

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;

Re: Where Exists() - Field not found

BalaBhaskar_Qlik — Sat, 18 Aug 2018 16:26:03 GMT

Officer Table:

LOAD

Officer_cd as "Officer Code",

Other Data

FROM qvdA

Where Exists("Officer Code", Officer_cd);

Drop Table tempOffList;

Re: Where Exists() - Field not found

ttollin11 — Fri, 24 Aug 2018 19:30:11 GMT

Went with this option. Thanks as always, Sunny.