Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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/
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
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
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.
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
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
hi Lawrence
sorry i made a mistake
here is an example
chris
I'm trying to do this from a qvd. Is that possible with this example?
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