Left join referencing fields in both tables

[#1]

Hi all,

is it possible to do a join in QV while, at the same time referencing fields from both tables?

Let's say i have table A and table B. I'm left joining table B to A using key field [EntryNo], but only where [InvovicedQuantity] in table A is <> 0. Is the possible to do that in one join?

Or do I have to join first and than resident load from A while putting 0 or Null in the joined fields where InvoicedQuantity is 0?

Thanks for your help,

Marko

[#2 · Accepted solution]

Hi Marko,

Try this:

LOAD EntryNo,
InvoicedQuantity
FROM TableA
WHERE InvoicedQuantity <> 0;
LEFT JOIN
LOAD EntryNo,
Other
FROM TableB;

Regards,

Gordon

[#3]

Hi Gordon, thanks for your answer.

I'm already doing it like that (actually im using a iszero flag-field instead of a where clause, but it's a same principle). What I was looking for is all-in-one-join solution. I guess it's not possible to do it the way i have imagined it.

Cheers,

Marko

[johnw]

How about this?

LOAD
EntryNo
,InvoicedQuantity
,if(InvoicedQuantity,EntryNo) as InvoicedEntryNo
FROM TableA
;
LEFT JOIN
LOAD
EntryNo
,Other
FROM TableB
WHERE EXISTS(InvoicedEntryNo,EntryNo)
;
DROP FIELD InvoicedEntryNo
;