Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mikegauntlett
Contributor III
Contributor III

Optimizing the load from 4 load statements with where exists clause

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.

Labels (3)
1 Reply
rogerpegler
Creator II
Creator II

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.