Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

nickedw0
Contributor

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
Partner
Partner

Re: Eliminate hierachical values from Left joins

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

 

2 Replies
Partner
Partner

Re: Eliminate hierachical values from Left joins

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
Contributor

Re: Eliminate hierachical values from Left joins

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