Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
mikegauntlett
Contributor III
Contributor III

Complicated, matching/ finding a set value and using mode

Hi All, 

In short trying to use the below to look at Lines (of an order) and return a yes no. 

OF:
LOAD
    OF_Id_Key_Opportunity
FROM [lib://Finance/OF_Suppliment.qvd]
(qvd);


Left Join (OF)
LOAD Distinct
OPP_LI_Opportunity_Id_Key as OF_Id_Key_Opportunity,
OPP_LI_Product_Id_Key as Product_Id_Key,
OPP_LI_Description
FROM [lib://CLM/REP_Opportunity_Line_Item.qvd]
(qvd);

Cables_Load_Filter:
load * Inline
 [Product_Movex_Part_Number,
1518265];

Cables_Products:
LOAD
Product_Id_Key,
Product_Movex_Part_Number,
Product_Description
FROM [lib://CLM/REP_Product2.qvd]
(qvd)
Where Exists(Product_Movex_Part_Number);

Left Join (OF)
LOAD
     Product_Id_Key,
	Mode('Yes') as OF_Cables
RESIDENT Cables_Products
Group by Product_Id_Key;
drop table Cables_Load_Filter,Cables_Products;

 

It used to work fantastically for multiple iterations looking at other product variances as well. but I had to switch the data around and now it does not return the OF_Cables field. 

Id love to explore better options but currently i am up against a urgent deadline and need the example above to work. 

The old version is below which worked. 

left join(OF)
LOAD Distinct
Quote_BM_Opportunity_Id_Key as OF_Id_Key_Opportunity,
Quote_Id_Key as BM_Quote_Prod_Quote_Id_Key,
Quote_Name,
Quote_BM_Description,
Quote_BM_Is_Primary
FROM [lib://CLM/REP_BM_Quote.qvd]
(qvd)
where Quote_BM_Is_Primary = 'true';

Cables_Load_Filter:
load * Inline
 [Quote_Prod_Name,
1518265];

Cables_Products:
LOAD
BM_Quote_Prod_Quote_Id_Key,
BM_Quote_Prod_Description
FROM [lib://CLM/REP_BM_Quote_Product.qvd]
(qvd)
Where Exists(Quote_Prod_Name);

left join(OF)
LOAD
     BM_Quote_Prod_Quote_Id_Key,
	Mode('Yes') as OF_Cables
RESIDENT Cables_Products
Group by BM_Quote_Prod_Quote_Id_Key;

drop table Cables_Load_Filter,Cables_Products;
Labels (1)
0 Replies