how to delete inner join to find display nonmatching rows

[daveatkins]

I have two tables containing the history of 2 actions that users are supposed to do. I need to display only those rows from the first table where there is no corresponding entry in the second table, based on 3 common key fields that identify the event.

in sql, this would be like

select a.field1, a.field2, a.field3, a.field4

from tablea a

left outer join tableb b on a.field1=b.field1 and a.field2=b.field2 and a.field3=b.field3

where b.field1 is null

[vishsaggi]

Use Left Join as like in SQL. Let me know if this is not what you looking for?

Table1:

LOAD *

FROM Table1;

LEFT JOIN (Table1)

Table2:

LOAD *

FROM Table2;

[daveatkins]

I want to delete all the rows that would be in the inner join. Example:

Table A

1,1,1,buy product a

2,2,2,buy product b

3,3,3,buy product c

Table B

1,1,1,sell product a

2,2,2,sell product b

outcome of join:

3,3,3,buy product c

In other words, the result of the join to show me what products were bought but not sold

[vishsaggi]

Try this?

TableA:

LOAD * INLINE [

A,B,C,BuyProd

1,1,1, a

2,2,2, b

3,3,3, c

];

LEFT JOIN (TableA)

TableB:

LOAD * INLINE [

A,B,C,SellProd

1,1,1, a

2,2,2,b

];

NoConcatenate

TableC:

LOAD *

Resident TableA

Where SellProd <> BuyProd;

Drop Table TableA;

[daveatkins]

It is not working for me. This is what I have now. Sorry this does not match exactly what I put above.

table ppp columns of P, Date, and AccountNumber, plus other columns

table Claims has PI, ASD, AN, ClaimID

TableA:
LOAD *
Resident ppp
;

LEFT JOIN (TableA)

LOAD
ClaimID,
PI as P,
ASD as Date,
AN as AccountNumber
Resident Claims
;
 
NoConcatenate

TableC:
LOAD *
Resident TableA
where IsNull(ClaimID);

;

[vishsaggi]

Try

Where Not Exists(ClaimID);

?

If still no good, please share a sample app with an estimated output rows you want as an output, it would be easy to work on rather than guessing!!!

[daveatkins]

thank you, I was able to get this to work by not making so many tables and by using a concatenated key...

1) load the first table and create a single concatenated key with preceding load:

p:

LOAD *, Date & '|' & AccountNumber & '|' & N as key

2) load the second table as a left join

LEFT JOIN (p)
Claims:
  Load Date & '|' & AccountNumber & '|' & N as key , ClaimID
;

SQL...

3) load a new table with what I need:

NoConcatenate

TableC:
LOAD *
Resident p
where IsNull(ClaimID);

DROP Table p;

This appears to work

[vishsaggi]

Sounds good. I do not know the data model so could able to help the least.