Where...On

[andrespa]

Hi, I'm not sure if this is possible in any way on Qlikview, but I want to use the typical "on" in the where clause. I'll show you with an example of what I want.

Have two tables (a,b), and want to get the values that are just on table b:

TABLE_A:

LOAD * INLINE [

    KEY, VALUE

    1, A

    2, B

    3, C

    4, D

    5, E

];

TABLE_B:

LOAD * INLINE [

    KEY, VALUE

    1, A

    2, B

    3, C

    6, X

    7, Y

];

My desire output would be:

TABLE_FINAL

KEY, VALUE

6     ,X

7     ,Y

In SQL this would be as easy as say:

SELECT * FROM TABLE_A A

RIGHT JOIN TABLE_B B

ON A.KEY=B.KEY

WHERE A.KEY IS NULL

But in Qlikview I'm only able to put the right join but without the "on" so can't figure it out yet.

Any ideas would be highly appreciate it.

Cheers,

Andrés

[Gysbert_Wassenaar · Accepted solution]

TABLE_A:

LOAD * INLINE [

    KEY, VALUE

    1, A

    2, B

    3, C

    4, D

    5, E

];

TABLE_B:

NOCONCATENATE

LOAD * INLINE [

    KEY, VALUE

    1, A

    2, B

    3, C

    6, X

    7, Y

]

WHERE NOT Exists(KEY);

DROP TABLE TABLE_A;

[swuehl]

JOINs in QV are using keys that are implicitely derived from common field names, there is no possibility to specify the key fields explicitely.

[sunny_talwar]

May be using Where not exist

[swuehl]

TABLE_A:

LOAD * INLINE [

    KEY, VALUE

    1, A

    2, B

    3, C

    4, D

    5, E

];

TABLE_B:

NOCONCATENATE

LOAD * INLINE [

    KEY, VALUE

    1, A

    2, B

    3, C

    6, X

    7, Y

]

WHERE NOT Exist(KEY);

DROP TABLE TABLE_A;

[andrespa]

Thanks Gysbert!! It worked! So simple but couldn't see it!

Cheers!