topic Left Outer Join in QlikView

Left Outer Join

Tue, 05 Oct 2010 17:15:39 GMT

Hi,

Any help would be really appreciated on this one.

I am trying to join 2 data tables together. Table 2 has more rows than Table 1 but I only want to bring through those rows in Table 2 that match to those in Table 1 (For example the load would only bring through invoice details for those invoices numbers contained in Table 1).

Currently my script retrieves all invoice numbers from the table "F_INV_NUM" and then brings through all invoice details for all invoices from table "T_AC_INVOICE_ITEMS".

My current code is below;

LOAD
"F_INV_COMPANY_ID"&'|'&"F_INV_NUM"&'|'&"F_INV_TRANSTYPE"&'|'&"F_INV_SUBNO" as %KEY_INVOICE_ITEM, 
"F_INV_NUM" AS [Invoice Number];
SQL SELECT * FROM
FLEETWARE."T_AC_INVOICES"
LEFT JOIN
"F_ITM_COMPANY_ID"&'|'&"F_ITM_NUM"&'|'&"F_ITM_TRANSTYPE"&'|'&"F_ITM_SUBNO" as %KEY_INVOICE_ITEM, 
"F_ITM_NUM" as [Inv Item Number],
"F_ITM_ACTION" as [Inv Item Detail]; 
SQL SELECT *FROM
FLEETWARE."T_AC_INVOICE_ITEMS"

I think some variation of LEFT OUTER JOIN is the way forward which specifies the condition that "F_INV_NUM" = "F_ITM_NUM".

As I understand it LEFT OUTER JOIN would only bring through those entries in table 2 which can be matched to those entries in table 1. I'm just not sure of how to execute it.

Thanks in advance

Left Outer Join

johnw — Tue, 05 Oct 2010 17:17:31 GMT

http://community.qlik.com/forums/t/35016.aspx

Left Outer Join

Tue, 02 Aug 2011 14:04:15 GMT

Hi John

Do you have the link to the article in the new forum?

The link above is dead.

Thanks

Moose

Left Outer Join

Tue, 02 Aug 2011 14:11:57 GMT

Hi there, you are missing a load statement and the outer keyword, you may try it like this:

Invoices:

LOAD

"F_INV_COMPANY_ID"&'|'&"F_INV_NUM"&'|'&"F_INV_TRANSTYPE"&'|'&"F_INV_SUBNO" as %KEY_INVOICE_ITEM,

"F_INV_NUM" AS [Invoice Number];

SQL SELECT * FROM

FLEETWARE."T_AC_INVOICES"

LEFT outer JOIN

Load

"F_ITM_COMPANY_ID"&'|'&"F_ITM_NUM"&'|'&"F_ITM_TRANSTYPE"&'|'&"F_ITM_SUBNO" as %KEY_INVOICE_ITEM,

"F_ITM_NUM" as [Inv Item Number],

"F_ITM_ACTION" as [Inv Item Detail];

SQL SELECT *FROM

FLEETWARE."T_AC_INVOICE_ITEMS"

Regards