topic 2 Left Joins To One Table? in QlikView

2 Left Joins To One Table?

mjt_childrens — Thu, 20 May 2010 06:16:36 GMT

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]);

2 Left Joins To One Table?

deepakk — Thu, 20 May 2010 06:34:20 GMT

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

2 Left Joins To One Table?

mjt_childrens — Thu, 20 May 2010 15:21:12 GMT

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.

2 Left Joins To One Table?

mjt_childrens — Fri, 21 May 2010 04:25:42 GMT

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.

2 Left Joins To One Table?

Fri, 21 May 2010 07:35:47 GMT

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

2 Left Joins To One Table?

mjt_childrens — Sat, 22 May 2010 14:57:59 GMT

Thanks. I think I understand now.