Qlik Community

Qlik Sense App Development

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

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
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
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.