Complicated, matching/ finding a set value and using mode

mikegauntlett — Sat, 16 Nov 2024 19:53:14 GMT

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;

topic Complicated, matching/ finding a set value and using mode in App Development

Complicated, matching/ finding a set value and using mode