2 Left Joins To One Table?

[mjt_childrens]

In the example below I am loading a table then attempting to do 2 left joins to this table. The first left join (Parts) works fine. But I end up without any records from the second join (Labor). In the Script Execution window it appears that nearly 1 million labor records are being loaded.

If I comment out the "parts" left join then the "labor" left join works correctly. What am I missing here? In a previous Qlikview app I was able to do a second left join to a table, the only difference was the second join was to a resident table.

Thanks in advance for any help. Here is my script:

VendorPerformanceInvoices:
LOAD [Charge-cust],
Document,
[Equipment-cust] AS [Vendor-code],
[Invoice-date],
[Invoice-Type],
[Seq-no],
[Wo-no]
FROM
..\DataSources\VendorPerf.qvd
(qvd)
WHERE [Invoice-Type] <> 'Sales';

LEFT JOIN (VendorPerformanceInvoices)
LOAD
'Parts' AS SubCategory,
Cost AS UnitCost,
[Item-no],
[Net-price] AS UnitPrice,
[Qty-shipped],
[Seq-no],
[Qty-shipped] * [Net-price] AS SaleAmt,
[Qty-shipped] * Cost AS CostAmt
FROM
..\DataSources\Invoice_Line.qvd
(qvd)
WHERE EXISTS([Seq-no]);

LEFT JOIN (VendorPerformanceInvoices)
LOAD
'Labor' AS SubCategory,
Time_Type,
[emp-id],
[wo-no],
[Sheet-date],
Hours,
[Cost-rate],
[Charge-amt],
// Document,
[Seq-no],
[Cost-rate] * [Hours] AS [Actual-labor-cost]
FROM
"..\DataSources\[ST-TIME-SH].qvd"
(qvd)
WHERE EXISTS([Seq-no]);

[deepakk]

The second and Third table is getting joined based on the SubCategory field.

[mjt_childrens]

Hi Deepak,

I'm not sure I understand your answer. The second and third tables have 2 fields in common: Seq-no and SubCategory. I still don't understand why none of the data from the third join appears to be loaded.

I'm having problems switching my thinking from a Relational to an Associative database. Still waiting for my "AHA! I get it!" moment.

[mjt_childrens]

I'm still not sure I understand why this didn't work but was able to work around the issue by taking a different approach to how I was loading the data.

[#5 · Accepted solution]

Hi Thompson,

The problem here is the key between 1st table and third table.1st you are joining 1st table and 2nd table with seq no key.Then you are trying to join the o/p of 1st & 2nd table with 3rd table.

Key between the o/p table and 3rd table is seq no and SubCategory. Now this forms the composite primary key. While joining the o/p table and 3rd table Qlikview will look for the combination of keys and takes the data from 3rd table what ever is matching with the o/p of 1st & 2nd table.

Since 2nd table has SubCategory = 'Parts' and 3rd table has SubCategory = 'Labor' the combination never match , so it is not taking the data from the 3rd table to 1st table.

Hope this may help you.

-Peterson

[mjt_childrens]

Thanks. I think I understand now.