Need values of ID column in Table A by excluding ID which are present in Table B

[MS90]

Need values of ID column in Table A by excluding ID based on leave category which are present in Table B

Using left join it is showing incorrect results in other calculations column

I achieved it by using Isnull() but it is showing performance issues -if(isnull(Leave category),Id)

Require o/p-I need only Id which are havingleave category null 

Please suggest me a solution volume of data is high

 

[qv_testing]

Hope this will help!

Table2:
LOAD * Inline [
ID, Leave Category
1, Frequently Leaves taken
4, Less leaves taken
5, Frequently Leaves taken
];

Table1:
Load * where not Exists(ID);
LOAD * INLINE [
ID, empname, emp performance source
1, A, 30%
2, B, 40%
3, C, 50%
4, D, 34%
5, E, 23%
6, F, 54%
7, G, 55%
];

drop Table Table2;

[anat · Accepted solution]

and also you can try using applymap to get LeaveCategory in table A

[Saravanan_Desingh]

Try using RIGHT JOIN

[Vegar]

I thinks @qv_testing suggestion to use not Exists() would be a good fit.

I encourage you to try that (and other suggestions) and come back to us with feedback if you need more support on this.

[vinieme12]

As below; since Where Not Exist will only load non-existing values once this needs to be a three step process 

//load distinct ID from leave category table
ExcludeIDs:
Load
ID
From TableB(LeaveCategories);
NOCONCATENATE

//Load ID's that need to be Kept i.e. those ID's that don't exist in leavecategory table
KeepIDs:
Load ID
Where Not Exists(ID)
;

Drop table ExcludeIDs;

//Final table with only ID's that exist in KeepIDs table
FinalTable:
Load *
From Table1
Where Exists(ID);

Drop table KeepIDs;