topic Filtering based on a lookup value in QlikView

Filtering based on a lookup value

Tue, 21 Feb 2017 17:18:27 GMT

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

Re: Filtering based on a lookup value

petter — Tue, 21 Feb 2017 20:46:09 GMT

‌Your expression for for sales should be:

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

Re: Filtering based on a lookup value

el_aprendiz111 — Tue, 21 Feb 2017 21:01:27 GMT

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);

Re: Filtering based on a lookup value

Wed, 22 Feb 2017 19:57:10 GMT

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

Re: Filtering based on a lookup value

Wed, 22 Feb 2017 19:58:52 GMT

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.

Re: Filtering based on a lookup value

el_aprendiz111 — Wed, 22 Feb 2017 20:26:55 GMT

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;