topic Re: Where...On in QlikView

Where...On

andrespa — Tue, 29 Mar 2016 09:03:33 GMT

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

Re: Where...On

Gysbert_Wassenaar — Tue, 29 Mar 2016 09:05:53 GMT

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;

Re: Where...On

swuehl — Tue, 29 Mar 2016 09:06:11 GMT

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

Re: Where...On

sunny_talwar — Tue, 29 Mar 2016 09:06:16 GMT

May be using Where not exist

Re: Where...On

swuehl — Tue, 29 Mar 2016 09:07:32 GMT

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;

Re: Where...On

andrespa — Tue, 29 Mar 2016 09:09:40 GMT

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

Cheers!