Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
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
MVP
MVP

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
MVP
MVP

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

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
geetaalhan
Creator
Creator

Thanks It helps me also.

anantmaxx
Specialist
Specialist

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