Optimizing the load from 4 load statements with where exists clause

mikegauntlett — Sat, 16 Nov 2024 20:43:19 GMT

Hi Guys,

So I have four load scripts set up like this,

Product_load_Filter: Load * Inline [Product_Movex_Part_Number XRT 0003, XRT 0013, XRT 0402, XRT 0412, XRT 0511]; 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_Product_Id_Key as Product_Id_Key, OPP_LI_Quantity 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_URL, Opportunity_Close_Date, Opportunity_Booking_Date, Opportunity_Territory_Id_Key, Opportunity_Created_Date FROM [lib://CLM/REP_Opportunity.qvd] (qvd) where Exists(Opportunity_StageName); Quote: LOAD Quote_Id_Key as Opportunity_Quote_Id, Quote_BM_Description FROM [lib://CLM/REP_BM_Quote.qvd] (qvd); drop table Product_load_Filter,OPP_load_Filter;

The challenge i am facing is getting the linking correct and optimized.

I need to try and achieve the following.

Opp_Li linked to Product where the product ID key exists.

Opp Linked to Opp_Li where OPP_LI_Opportunity_Id_Key exists.

Quote linked to Opp where Opportunity_Quote_Id exists.

Any Help is greatly appreciated.

Re: Optimizing the load from 4 load statements with where exists clause

rogerpegler — Thu, 06 Jun 2019 03:24:42 GMT

Using the example of linking Opp_LI to Product:

Inner Join (Opp_LI)

Load Distinct Product_ID_Key Resident Product_Data;

Using Keep instead of Join may give you other options worth considering.

topic Re: Optimizing the load from 4 load statements with where exists clause in App Development

Optimizing the load from 4 load statements with where exists clause

Re: Optimizing the load from 4 load statements with where exists clause