Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
mikegauntlett
New Contributor III

4 load statements with where exists, issues with linking

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

2 Replies
Highlighted
Partner
Partner

Re: 4 load statements with where exists, issues with linking

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

Work smarter, not harder
mikegauntlett
New Contributor III

Re: 4 load statements with where exists, issues with linking

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