Filtering based on a lookup value

[#1]

HI i have the following dataset ( subset of a much larger dataset).

As you can see the business area is simply brought in via a lookup.

But if for instance I want to get exclude all sales where the business area is equal to either 'finance' or 'marketing' how would I go about doing this without excluding on the corresponding sales_id ?

Se effectively I want to be left with only 3 rows of data with total sales of 18000

[petter]

‌Your expression for for sales should be:

Sum( {<[business area]-={'marketing','finance'}>} sales_amount)

[el_aprendiz111]

Hi,

from the script

LOAD * Inline
[
sales_amount,region ,city ,Sales ID
5000,usa,florida,1
1000,usa,chicago,2
6000,usa,arizona,3
4000,canada,toronto ,4
7000,brazil ,rio ,5

]
Where not WildMatch([Sales ID],2,4);

[#4]

Thanks Petter, but I need to do this in script since it is referenced through out a number of views / places in the dashboard

[#5]

Thanks ferfer - Unfortunately as mentioned in the post this is just a small subset of a huge dataset so for me to filter by sales ID simply isnt possible since I dont have a list of all the Sales IDs to exclude, only the business areas.

[el_aprendiz111]

Hi Shibli

try with a list from excel:

//****************************************************************************************************
//****************************************************************************************************
Directory;
[FILTER]:
LOAD [Sales ID] FROM [..\..\..\..\..\..\Downloads\test.xlsx] (ooxml, embedded labels, table is SalesID);

[PARAMETERS]:
LOAD Concat([Sales ID] & ',') AS [Sales ID] Resident FILTER;

LET v_filters = trim(Peek('Sales ID'));
//****************************************************************************************************
//****************************************************************************************************

DATA:
LOAD * Inline
[
sales_amount,region ,city ,Sales ID
5000,usa,florida,1
1000,usa,chicago,2
6000,usa,arizona,3
4000,canada,toronto ,4
7000,brazil ,rio ,5
]
Where not WildMatch([Sales ID],$(v_filters));

DROP Table FILTER;