topic Left join referencing fields in both tables in QlikView

Left join referencing fields in both tables

Tue, 02 Jun 2009 15:12:26 GMT

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

Left join referencing fields in both tables

Tue, 02 Jun 2009 15:33:34 GMT

Hi Marko,

Try this:

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

Regards,

Gordon

Left join referencing fields in both tables

Tue, 02 Jun 2009 16:15:58 GMT

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

Left join referencing fields in both tables

johnw — Wed, 03 Jun 2009 01:17:20 GMT

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
;