4 load statements with where exists, issues with linking

[mikegauntlett]

Hi Guys, 

I have a challenge where I need to load four data sets with linking using the where exists. 

so Far I have 

Product_load_Filter:
Load * Inline
[Product_Movex_Part_Number
ABC,
123,
12AC];
Product_Data:
LOAD
    Product_Id_Key,
    Product_URL,
    Product_Name,
    Product_ProductCode,
    Product_Description        
FROM [lib://CLM/REP_Product2.qvd]
(qvd)
where Exists(Product_Movex_Part_Number);

Opp_LI:
LOAD
    OPP_LI_Id_Key,
    OPP_LI_URL,
     OPP_LI_Quantity,
     OPP_LI_Opportunity_Id_Key,
    OPP_LI_Product_Id_Key as Product_Id_Key
    FROM [lib://CLM/REP_Opportunity_Line_Item.qvd]
(qvd);

OPP_load_Filter:
Load * Inline
[Opportunity_StageName,
Qualified,
Proposal,
Negotiation,
Order Won
];

Opp:
LOAD
 Opportunity_Id_Key as OPP_LI_Opportunity_Id_Key,
 Opportunity_StageName,
 Opportunity_Booking_Date,
 Opportunity_Quote_Id,
  Opportunity_Adjusted_Delivery_Terms
FROM [lib://CLM/REP_Opportunity.qvd]
(qvd)
where Exists(Opportunity_StageName,OPP_LI_Opportunity_Id_Key);

Quote:
LOAD
    Quote_Id_Key as Opportunity_Quote_Id,
    Quote_URL,
    Quote_Owner_Id_Key,
    Quote_BM_Is_Primary
    FROM [lib://CLM/REP_BM_Quote.qvd]
(qvd)
where [Quote_BM_Is_Primary]='True';

How can I achieve effectively a left join but without the left join. 

To get 

Opp_Li linked to Products where the Product Key exists

Opp linked to Opp_Li where the  OPP_LI_Opportunity_Id_Key

and 

Quote linked to Opp where the Opportunity_Quote_Id exists. 

Hope this makes sense

Any help is greatly appreciated

[JordyWegman]

Hi Mike,

Try this:

Product_load_Filter:
Load * Inline[
Product_Movex_Part_Number
ABC,
123,
12AC
];


Product_Data:
LOAD
    Product_Id_Key,
    Product_URL,
    Product_Name,
    Product_ProductCode,
    Product_Description        
FROM [lib://CLM/REP_Product2.qvd]
(qvd)
where Exists([WhichFieldInThisTableIsThis??],Product_Movex_Part_Number);
// You need to specify your field in the first part from the table Product_Data that is the same as Product_Movex_Part_Number

Opp_LI:
LOAD
    OPP_LI_Id_Key,
    OPP_LI_URL,
     OPP_LI_Quantity,
     OPP_LI_Opportunity_Id_Key,
    OPP_LI_Product_Id_Key
    FROM [lib://CLM/REP_Opportunity_Line_Item.qvd]
(qvd)
Where Exists (OPP_LI_Product_Id_Key,Product_Id_Key)
; //Renaming doesn't help because that is done after reading the table. For now it has the old name. Now you will filter on Product_Id_Key.

OPP_load_Filter:
Load * Inline[
Opportunity_StageName,
Qualified,
Proposal,
Negotiation,
Order Won
];

Opp:
LOAD
 Opportunity_Id_Key as OPP_LI_Opportunity_Id_Key,
 Opportunity_StageName,
 Opportunity_Booking_Date,
 Opportunity_Quote_Id,
  Opportunity_Adjusted_Delivery_Terms
FROM [lib://CLM/REP_Opportunity.qvd]
(qvd)
where Exists(Opportunity_StageName);

Quote:
LOAD
    Quote_Id_Key as Opportunity_Quote_Id,
    Quote_URL,
    Quote_Owner_Id_Key,
    Quote_BM_Is_Primary
    FROM [lib://CLM/REP_BM_Quote.qvd]
(qvd)
where [Quote_BM_Is_Primary]='True';

Jordy

Climber

[mikegauntlett]

Hi Jordy,
Thanks for the response, when i run the script for this it states it cannot find the Product Id key field. When it comes to load the Opp LI data?

Any Ideas