Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
lbunnell
Creator
Creator

Left Join Where


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:

Left Join Where.bmp

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

9 Replies
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/

Not applicable

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
Luminary Alumni
Luminary Alumni

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

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

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

mov
Champion III
Champion III

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

Not applicable

hi Lawrence

sorry i made a mistake

here is an example

chris

lbunnell
Creator
Creator
Author

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

jvitantonio
Luminary Alumni
Luminary Alumni

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