topic Re: Where Exists() and resident load in QlikView

Where Exists() and resident load

RSvebeck — Wed, 17 Apr 2013 08:44:51 GMT

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

Re: Where Exists() and resident load

Wed, 17 Apr 2013 11:26:53 GMT

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

Re: Where Exists() and resident load

jagan — Wed, 17 Apr 2013 11:38:17 GMT

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

Gysbert_Wassenaar — Wed, 17 Apr 2013 11:40:43 GMT

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

Re: Where Exists() and resident load

RSvebeck — Wed, 17 Apr 2013 12:17:43 GMT

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

Re: Where Exists() and resident load

Anonymous — Tue, 24 Jun 2014 05:51:48 GMT

Thanks It helps me also.

Re: Where Exists() and resident load

Anonymous — Tue, 01 Jul 2014 06:22:03 GMT

clean !!

Anant

Re: Where Exists() and resident load

Wed, 28 Jan 2015 12:33:11 GMT

Hi Jagan,

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

Thanks

Re: Where Exists() and resident load

Wed, 28 Jan 2015 13:39:34 GMT

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

Thanks