8 Replies Latest reply: Jan 28, 2015 8:39 AM by pavan Kumar

# Where Exists() and resident load

Hi

Have these two tables:

tab1:
F1, F2
A, 1
B, 2
C, 3
D, 4
];

tab2:
F3, F4
A, AA
B, BB
C, CC
E, EE
];

Now I want to know how to use Where not exists() and resident load to get a new table containing this data:

F3,F4
E,EE

I know how to solve this by using mapping load, but I want to understand the Exists() function.

Regards,

Robert

• ###### Re: Where Exists() and resident load

Hi ,

Try the below code

tab1:

F1, F2

A, 1

B, 2

C, 3

D, 4

];

tab2:

F3, F4

A, AA

B, BB

C, CC

E, EE

];

Tab3:

NoConcatenate

LOAD F1 as F3, F4 Where Not Exists(F1);

LOAD F3 as F1 , F4

Resident tab2;

DROP Table tab2;

//Yusuf

• ###### Re: Where Exists() and resident load

Hi,

Try this script

tab1:

F1, F2

A, 1

B, 2

C, 3

D, 4

];

tab2:

F3, F4

A, AA

B, BB

C, CC

E, EE

];

qualify *;

Table3:

*

RESIDENT tab2

WHERE Not Exists(F1, F3);

Hope it helps you.

Regards,

Jagan.

• ###### Re: Where Exists() and resident load

All three solutions works, but I found Jagans solution most elegant, easy to grasp and also easiest to implement into my much more complicated reality... :-)

Best Regards

Robert

• ###### Re: Where Exists() and resident load

Thanks It helps me also.

• ###### Re: Where Exists() and resident load

clean !!

Anant

• ###### Re: Where Exists() and resident load

If you add the where not exists clause to the second load you'll get only E,EE in tab2:

tab1:

F1, F2

A, 1

B, 2

C, 3

D, 4

];

tab2:

F3, F4

A, AA

B, BB

C, CC

E, EE

]

Where not Exists(F1,F3) ;

• ###### Re: Where Exists() and resident load

Hi

Can you please tell me whether we can get the same result using apply map.

Thanks

• ###### Re: Where Exists() and resident load

Hi Jagan,

Could you please explain how this can be achieved by using apply map??

Thanks