Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
mjt_childrens
New Contributor III

2 Left Joins To One Table?

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

Tags (1)
1 Solution

Accepted Solutions
Not applicable

2 Left Joins To One Table?

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

View solution in original post

5 Replies
Partner
Partner

2 Left Joins To One Table?

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

mjt_childrens
New Contributor III

2 Left Joins To One Table?

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
New Contributor III

2 Left Joins To One Table?

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.

Not applicable

2 Left Joins To One Table?

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

View solution in original post

mjt_childrens
New Contributor III

2 Left Joins To One Table?

Thanks. I think I understand now.