Exclude data except when data is referenced in separate list in an Excel file

Tue, 30 Dec 2014 18:13:47 GMT

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!

Re: Exclude data except when data is referenced in separate list in an Excel file

Tue, 30 Dec 2014 20:30:31 GMT

Hi there,

You can read all the Prod Ids from Excel and store it in a variable. Then use that variable to filter out the data from you table.

Please see attached

topic Exclude data except when data is referenced in separate list in an Excel file in QlikView

Exclude data except when data is referenced in separate list in an Excel file

Re: Exclude data except when data is referenced in separate list in an Excel file