Qlik Community

App Development

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

Announcements
WE ARE LISTENING! New Navigation for Qlik Community, Sept. 26: TELL ME MORE
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 (5)
0 Replies