Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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.
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.
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,
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);
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.
Brilliant! Cheers!