Transitive relationship

[SonPhan]

Hello, 

i have a question regarding scripting in QS.

Lets say I have a table temp. 

[temp]:
Load * inline [
Employee, Boss
100, 105
177, 180
105, 911
400, 402
];

I want to pair all possible relationship. The result should look like this:

Load * inline [
Employee, Boss
100, 105
177, 180
105, 911
400, 402
100, 911];

So in my example 100 -> 105 and 105 -> 911 means that people with the Ids 100 and 911 are in such a relationship. So I want their number to be listed in my result table.

Hope u can help me.

Best regards

Phan Thanh Son

[jyothish8807]

Hi SonPhan,

Try this:

[temp]:
Load * inline [
Employee, Boss
100, 105
177, 180
105, 911
400, 402
];

Left Join ([temp])
[temp2]:
Load
Employee as Boss,
Boss as New_Employee
resident [temp];

NoConcatenate
Final:
Load Employee,
Boss
Resident temp;

Concatenate(Final)
Load
Employee,
New_Employee as Boss
Resident temp where len(New_Employee) > 0;

Drop table temp;

[GaryGiles · Accepted solution]

A simple load script to create your posted result would be:

[temp2]:
Load * inline [
Employee, Boss
100, 105
177, 180
105, 911
400, 402
911, 125
];

Left Join ([temp2])
Load Employee as Boss,
Boss as Boss2
Resident [temp2];

[temp]:
Load Employee,
Boss
Resident temp2;

Load Employee,
Boss2 as Boss
Resident [temp2]
where not isnull(Boss2);

drop table [temp2];

This will only give you one level of relationship, Employee - Boss.  Since this is an adjacent node table, you may want to look into using the Hierarchy prefix in your load script.  You would need an Employee Name column.  An example would be:

[temp]:
Hierarchy (Employee, Boss, EmployeeName)
Load * inline [
Employee, EmployeeName, Boss
100, Employee A, 105
177, Employee B, 180
105, Employee C, 911
400, Employee D, 402
911, Employee E, 125
];

This "flattens out" the hierarchy in your table and would show you all relationships at different levels.  It can useful for analysis.

[SonPhan]

Thanks for your quick reply, unfortunately the solution is still not quite correct.

For this example:

[temp]:
Load * inline [
Employee, Boss
100, 105
177, 180
105, 911
400, 402,
911, 1000
];

The relation 100 -> 1000 is missing.

However, I will continue to build on your proposed solution.

Thank you!