topic Re: Left Join Where in QlikView

Left Join Where

lbunnell — Fri, 24 Jan 2014 14:39:03 GMT

I'm trying to join 2 tables from qvd's resident and keep only the records in Table 1 where there is an associated record in Table 2 like this:

There is only 1 field in each table that is the same - the key field. Is there a way to do this in QlikView?

Re: Left Join Where

Nicole-Smith — Fri, 24 Jan 2014 14:42:17 GMT

You'll want to use INNER JOIN. See the following for further explanation:

http://www.qlikfix.com/2011/02/07/merging-tables-the-join-prefix/

Re: Left Join Where

Fri, 24 Jan 2014 14:42:37 GMT

Hi Lawrence

Yes don't use JOIN but KEEP

KEEP has the same effect but keep 2 disitnct tables

So you can drop the one you don"t want anymore

Table _A:

Right KEEP

LOAD * resident Tablee_B; (Table B already loaded)

best regards

Chris

Re: Left Join Where

jvitantonio — Fri, 24 Jan 2014 15:05:27 GMT

Hi Lawrence,

This is a bit tricky and can be done a lot easier in SQL. Here's my solution:

A:
LOAD F1 as FieldA INLINE [
    F1
    s1
    s2
    s3
    n1
    n2
];

B:
LOAD F1 as FieldB INLINE [
    F1
    n1
    n2
    C
];


Final:
LOAD FieldA as FinalField Resident A
where not Exists(FieldB, FieldA)
;
drop tables A, B;

I hope it helps.

BI Experience | A place to share our Business Intelligence experiences

Re: Left Join Where

lbunnell — Fri, 24 Jan 2014 18:03:01 GMT

I wasn't able to get QlikView to drop the table. I included it in a drop tables statement, but it keeps the table anyway so I end up with 2 tables.

Re: Left Join Where

lbunnell — Fri, 24 Jan 2014 18:05:20 GMT

Sorry, to clarify, I meant to say that I want to keep the records in table A where there is NOT a corresponding record in B

Re: Left Join Where

Anonymous — Fri, 24 Jan 2014 18:52:55 GMT

If I understand it right:

First, load the table B.

After this, load table A with a condition where not exists(KeyField).

And, drop table B.

Regards,

Michael

Re: Re: Left Join Where

Mon, 27 Jan 2014 16:59:23 GMT

hi Lawrence

sorry i made a mistake

here is an example

chris

Re: Left Join Where

lbunnell — Tue, 28 Jan 2014 17:26:56 GMT

I'm trying to do this from a qvd. Is that possible with this example?

Re: Re: Left Join Where

jvitantonio — Tue, 28 Jan 2014 17:49:53 GMT

Hi Lawrence,

Yes, it would be the same:

A: 
LOAD F1 as FieldA FROM C:\MyTableA.qvd;

B: 
LOAD F1 as FieldB FROM C:\myTableB.qvd;


Final: 
LOAD FieldA as FinalField Resident A 
where not Exists(FieldB, FieldA) 
; 
drop tables A, B;

Best,