topic Re: Trouble understanding Where Exists in QlikView

Trouble understanding Where Exists

Wed, 16 Jul 2014 13:30:39 GMT

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.

Re: Trouble understanding Where Exists

Wed, 16 Jul 2014 13:38:13 GMT

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.

Re: Trouble understanding Where Exists

MayilVahanan — Wed, 16 Jul 2014 13:38:14 GMT

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.

Re: Trouble understanding Where Exists

puttemans — Wed, 16 Jul 2014 13:39:09 GMT

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,

Re: Trouble understanding Where Exists

Thu, 17 Jul 2014 09:28:16 GMT

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,

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);

Re: Trouble understanding Where Exists

puttemans — Thu, 17 Jul 2014 09:36:54 GMT

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.

Re: Trouble understanding Where Exists

Fri, 18 Jul 2014 08:34:36 GMT

Brilliant! Cheers!