Where Exists() and resident load

[RSvebeck]

Hi

Have these two tables:

tab1:
LOAD * INLINE [
    F1, F2
    A, 1
    B, 2
    C, 3
    D, 4
];

tab2:
LOAD * INLINE [
    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

[#2]

Hi ,

    Try the below code

tab1:

LOAD * INLINE [

    F1, F2

    A, 1

    B, 2

    C, 3

    D, 4

];

tab2:

LOAD * INLINE [

    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

[jagan · Accepted solution]

Hi,

Try this script

tab1:

LOAD * INLINE [

    F1, F2

    A, 1

    B, 2

    C, 3

    D, 4

];

tab2:

LOAD * INLINE [

    F3, F4

    A, AA

    B, BB

    C, CC

    E, EE

];

qualify *;

Table3:

LOAD

    *

RESIDENT tab2

WHERE Not Exists(F1, F3);

Hope it helps you.

Regards,

Jagan.

[Gysbert_Wassenaar]

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

tab1:

LOAD * INLINE [

    F1, F2

    A, 1

    B, 2

    C, 3

    D, 4

];

tab2:

LOAD * INLINE [

    F3, F4

    A, AA

    B, BB

    C, CC

    E, EE

]

Where not Exists(F1,F3) ;

[RSvebeck]

Thank you for all answers.

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

[Anonymous]

Thanks It helps me also.

[Anonymous]

clean !!

Anant

[#8]

Hi Jagan,

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

Thanks

[#9]

Hi

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

Thanks