Left Join Where

[lbunnell]

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?

[Nicole-Smith]

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/

[#3]

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

[jvitantonio]

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.

JV

BI Experience | A place to share our Business Intelligence experiences

[lbunnell]

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.

[lbunnell]

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

[Anonymous]

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

[#8]

hi Lawrence

sorry i made a mistake

here is an example

chris

[lbunnell]

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

[jvitantonio]

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,

JV