How to check for the existence of relationship featuring a desired attribute?

[#1]

Hi there!

I have this dataset

Employees:

ID	Name
1	John
2	Smith
3	Joe

Offices:

ID	City
1	New York
2	London

Activities:

ID	Description
1	Manager
2	Operator
3	Director

Employes_Offices_Activities:

EmployeID	OfficeID	ActivitieID	Enabled
1	1	1	True
1	1	2	False
2	2	3	True
3	1	1	False
3	2	1	False

I want to load Employee with an extra flag that determines if it's active or not. One employee will be active if he has one enabled activity in at least one office. Otherwise, he will be not active. I intend to get this Employee table:

Employee:

ID	Name	Active
1	John	Yes
2	Smith	Yes
3	Joe	No

Joe is not active since he has no enabled activity in any office.

How can I perform this load? It would be great if you could provide some guidance. I'll appreciate a lot!

Thank you!

Matias

[swuehl · Accepted solution]

Maybe something like

MAP:

MAPPING LOAD

EmployeID, 'Yes' as Flag

FROM Employes_Offices_Activities

WHERE Enabled = 'True';

LOAD

     ID,

     Name,

      ApplyMap('MAP',ID,'No') as Active

FROM Employees;

Adapt the FROM parts to your original data sources.

[#3]

Hi Stefan!

Thank you a lot! That made the trick!

Best regards!

Matias