Is it possible to have a statement in the script that excludes certain records except when that record rolls up to values referenced in a separate list in an Excel file?
I am modifying an app to exclude transactions where ((GrossSalesAmt)/(TargetGrossRevenue)) is <0.4 or >2.0 except when the transaction rolls up to a ProductId specified in the OR statement. Managing this ProductID-by-ProductID worked in the script until the number of ProductIDs that I needed to override the ((GrossSalesAmt)/(TargetGrossRevenue)) is <0.4 or >2.0 statement exceeded 172. Beyond that number, the code in the script changed from active (red and blue) to inactive (black) and would not load correctly (see in active code in the copy/paste below).
WHERE EXISTS(BillToCustomer_KEY) AND EXISTS(Plant_KEY) and EXISTS(BillToCustomerName)
AND ( ((GrossSalesAmt)/(TargetGrossRevenue))>'0.3999' and ((GrossSalesAmt)/(TargetGrossRevenue))<'2.01'
.........OR ProductId='68254' OR ProductId='71827' OR ProductId='68763' OR ProductId='67190' OR ProductId='68777' OR ProductId='88074' OR ProductId='73015' OR ProductId='128609' OR ProductId='198382' OR ProductId='179579' OR ProductId='194331' OR ProductId='88917' OR ProductId='71718' OR ProductId='179569' OR ProductId='7770C' OR ProductId='12509C' OR ProductId='14099C' OR ProductId='11840C' OR ProductId='14437C' OR ProductId='13540C' OR ProductId='14471C' OR ProductId='14209C' OR ProductId='14491C' OR ProductId='68932901' OR ProductId='13488C' OR ProductId='7534C' OR ProductId='7503C' OR ProductId='8875C' OR ProductId='14444C' OR ProductId='12239C' OR ProductId='9389C' OR ProductId='146072' OR ProductId='20240' OR ProductId='39052' OR ProductId='146467' OR ProductId='65598' OR ProductId='8979C' OR ProductId='7683C' OR ProductId='9227C' OR ProductId='3398C' OR ProductId='3455C' OR ProductId='1690C' OR ProductId='3459C' OR ProductId='55759' OR ProductId='48265' OR ProductId='58424' OR ProductId='48080' OR ProductId='48700' OR ProductId='101832'
OR ProductId='57973'));
I am now attempting to load an excel file with the full list of ProductIDs for the override (i.e. exclude transactions where ((GrossSalesAmt)/(TargetGrossRevenue)) is <0.4 or >2.0 except when the transaction rolls up to a ProductId specified in the excel file). What syntax should I use for the statement so that the ((GrossSalesAmt)/(TargetGrossRevenue)) is <0.4 or >2.0 statement is overridden and all records are returned if the ProductID is in the excel file/list?
WHERE (ProductId='56937') AND EXISTS(BillToCustomer_KEY) AND EXISTS(Plant_KEY) and EXISTS(BillToCustomerName)
AND ( ((GrossSalesAmt)/(TargetGrossRevenue))>'0.3999' and ((GrossSalesAmt)/(TargetGrossRevenue))<'2.01');
LOAD ProductId
FROM
[Exceptions Report Tracking Changes.xlsx]
(ooxml, embedded labels, table is [Exceptions Shifts])
WHERE exists (ProductId);
Thank you very much for your help!
