Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
nickedw0
Creator
Creator

Eliminate hierachical values from Left joins

After running a sql query thru a team and left  left joining on a 'ReportsTo' field, I am left with 3 tables. e.g.:

Team table:

id1

id2

id3

id4

Supervisor table:

id2

id4

Manager table:

id4

;

1 How can I eliminate id2 and id4 from the Team table.

2 How can I eliminate Id4 from the Supervisor table?

Thanks for any help

 

1 Solution

Accepted Solutions
uacg0009
Partner - Specialist
Partner - Specialist

Hi,

You can get what you want using not exists(). For example:

You have

team_id in Team Table

supervisor_id in Supervisor Table

manager_id in Manager Table

Then you need to :

 

 

Load * resident Team_table where not exists(supervisor_id,team_id);

Load * resident Supervisor_table where not exists(manager_id,supervisor_id);

 

These 2 tables above is what you want.

Please try.

Aiolos Zhao

 

View solution in original post

2 Replies
uacg0009
Partner - Specialist
Partner - Specialist

Hi,

You can get what you want using not exists(). For example:

You have

team_id in Team Table

supervisor_id in Supervisor Table

manager_id in Manager Table

Then you need to :

 

 

Load * resident Team_table where not exists(supervisor_id,team_id);

Load * resident Supervisor_table where not exists(manager_id,supervisor_id);

 

These 2 tables above is what you want.

Please try.

Aiolos Zhao

 

nickedw0
Creator
Creator
Author

Thanks this produced the subsets I needed. I followed with joins to merge into single table with columns for supervisors and managers.