Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Where Exists() - Field not found

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;

1 Solution

Accepted Solutions
Highlighted

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;

View solution in original post

5 Replies
Highlighted
Specialist
Specialist

Try this

Office_Table:

NoConcatenate

Load

          Officer_cd as "Officer Code",

          Other Data

From qvdA

Where Exists ("Officer Code", Office_cd);

Highlighted
Specialist
Specialist

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/

Highlighted

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;

View solution in original post

Highlighted

Officer Table: 

LOAD 

    Officer_cd as "Officer Code", 

    Other Data 

FROM qvdA 

Where Exists("Officer Code", Officer_cd); 

 

Drop Table tempOffList; 

Highlighted
Contributor III
Contributor III

Went with this option. Thanks as always, Sunny.