Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
mikegauntlett
New 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.

1 Reply
rogerpegler
Contributor

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

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.