Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
RSvebeck
Specialist
Specialist

Where Exists() and resident load

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

Svebeck Consulting AB
1 Solution

Accepted Solutions
jagan
Luminary Alumni
Luminary Alumni

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.

View solution in original post

8 Replies
Not applicable

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
Luminary Alumni
Luminary Alumni

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) ;


talk is cheap, supply exceeds demand
RSvebeck
Specialist
Specialist
Author

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

Svebeck Consulting AB
Anonymous
Not applicable

Thanks It helps me also.

Anonymous
Not applicable

clean !!

Anant

Not applicable

Hi Jagan,

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

Thanks

Not applicable

Hi

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

Thanks