Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Trouble understanding Where Exists

Hello,

I'm trying to create a table containing only the leaders of an organisation based on a table that contains all the employees in the organisation.

So in the big table, there is data like:
EmployeeID (One unique for each employee)

LeaderFlag (1 if the employee is a leader, 0 if not)

I want to create a smaller table with only the leaders. I have basically tried:

LeaderData:

LOAD

EmployeeID,

Employee.Surname_Firstname as Leader.Surname_Firstname

Resident Employee

Where Exists (LeaderFlag);

This results in no data in the LeaderData table. I tried to understand the Where Exists explanations in various posts in the forum, but I don't get it.

1 Solution

Accepted Solutions
Not applicable
Author

I think you need the following:

LeaderData:

LOAD

EmployeeID,

Employee.Surname_Firstname as Leader.Surname_Firstname

Resident Employee

Where LeaderFlag = 1;

Where Exists determines whether a specific field value exists in a specified field of the data loaded so far.

View solution in original post

6 Replies
Not applicable
Author

I think you need the following:

LeaderData:

LOAD

EmployeeID,

Employee.Surname_Firstname as Leader.Surname_Firstname

Resident Employee

Where LeaderFlag = 1;

Where Exists determines whether a specific field value exists in a specified field of the data loaded so far.

MayilVahanan

Hi

Try like this

LOAD

EmployeeID,

Employee.Surname_Firstname as Leader.Surname_Firstname

Resident Employee

Where LeaderFlag = 1;

Exists check that specified field values exists already in the previous table.

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
puttemans
Specialist
Specialist

Hello Tharald,

There is a difference between 'WHERE' and 'WHERE EXISTS'

I guess if you would replace the last line into 'WHERE Leaderflag = 1;', your tabel will be loaded with only the leaders.

To use where exists, you previously need to load a table based upon the key you want to use in the where exists function. See example below.

Map_Product_Scope_Temp_2:

LOAD
%TempProdKey
Resident Map_Product_Scope_Temp;

NoConcatenate

150:
LOAD
*
FROM
[$(vQVDTempPath)\$(vCurrentProduct)_$(vCurrentYear$(vCurrentProduct))_100.qvd]
(
qvd)
WHERE Exists(%TempProdKey);

This is used merely when you have 2 tables with no corresponding variables but the one you want to connect on. And when you want to load only a subset of this table. In your case, everything is in one table, so 'where exists' would be a detour.

Kind regards,

Not applicable
Author

Thanks!

That was all very helpful. However, after fiddling around a bit, I found that my original plan isn't going to work without some additional work.

Originally, two separate tables were merged into one, where the first data contains employee details, and the second table contains a registry of employee IDs that have leadership positions. The Leadership table is structured a bit strangely, where each leader is listed as many times as the number of employees he/she is leading.

I want to merge the two tables, but using a left join (based on the large employee table) causes each leader to be listed as many times in the employee table as in the leader table.

So right now I'm getting

Employee table:
LeaderID1,

LeaderID1,

LeaderID1,

LeaderID2,

LeaderID2,

etc.

where it should have been

Employee table:

LeaderID1,

LeaderID2,

I'm sorry about the basic questions, but what should I do to avoid multiple listings?

The join code I have used is:

Left Join

Load

    FK_HR_EMPLOYEE_LEADER as FK_HR_EMPLOYEE,

    LEADER_FLAG

  

FROM

(qvd);

puttemans
Specialist
Specialist

Hi,

If the number of employees in the 'employee details table' is correct, then don't use a join to combine both, but a mapping.

Leaders:

MAPPING LOAD DISTINCT

employee_ID,

1 as flag

FROM 'your leadership table'

Then when you load the employee table

APPLYMAP('Leaders', employee_ID,0) as Leader

This will identify your leaders in the employee table, without multiplication.

Not applicable
Author

Brilliant! Cheers!