Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
rsvebeck
Valued Contributor

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Where Exists() and resident load

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.

8 Replies
Not applicable

Re: Where Exists() and resident load

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

MVP
MVP

Re: Where Exists() and resident load

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.

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:

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
Valued Contributor

Re: Where Exists() and resident load

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

geetaalhan
Contributor

Re: Where Exists() and resident load

Thanks It helps me also.

anantmaxx
Contributor III

Re: Where Exists() and resident load

clean !!

Anant

Not applicable

Re: Where Exists() and resident load

Hi Jagan,

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

Thanks

Not applicable

Re: Where Exists() and resident load

Hi

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

Thanks

Community Browser