topic Re: How to filter the data in this scenario. in QlikView

How to filter the data in this scenario.

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi ,

I have a created a pivot table with the required dimensions.

scenario:

If I select the organisation the pivot table should display the 'group name' , 'service type', 'month/year' and the 'value'.

but as you can see in the pic below for every organisation there are service names including 'test team' or 'test service' which is related to the respective 'service id's'

My requirement is the pivot table should only display the contents of all the service names except Test Team or Test service into the Group name in pivot table.

I am attaching the pic for your reference.

any help is highly appreicated.

Re: How to filter the data in this scenario.

sunny_talwar — Fri, 27 Jan 2017 18:09:33 GMT

test team and test service are associated with 2 and 3 service id? If yes, you can do this:

Sum({<service_id -= {2, 3}>} Measure)

Sum({<service_name -= {*Test*}>} Measure)

Re: How to filter the data in this scenario.

vishsaggi — Fri, 27 Jan 2017 18:11:00 GMT

Can you do a restriction on Service name like in your list box expression

Re: How to filter the data in this scenario.

Anonymous — Fri, 27 Jan 2017 18:14:00 GMT

No They are not associated.

Perhaps i am thinking to use your old where clause to avoid getting from the database into Qlikview.

What do you say? in that case all the service names will be live ones not the test.

Re: How to filter the data in this scenario.

sunny_talwar — Fri, 27 Jan 2017 18:17:22 GMT

You sure can and should if you don't need the tests

Re: How to filter the data in this scenario.

Anonymous — Fri, 27 Jan 2017 18:26:18 GMT

ok.

It is throwing error like this.

LOAD id as service_id,

name as "service_name"

Where service_name not in ('Test Team ', 'Test service');

Re: How to filter the data in this scenario.

sunny_talwar — Fri, 27 Jan 2017 18:28:21 GMT

it seems that the field name is called something else in the raw data. What is it called?

Re: How to filter the data in this scenario.

Anonymous — Fri, 27 Jan 2017 18:31:41 GMT

it is called name but I am using name as 'service_name'

Does it throw error?

Re: How to filter the data in this scenario.

Anonymous — Fri, 27 Jan 2017 18:36:15 GMT

It is loading the data without errors but it is not filtering the Test Team or Test Service.

Re: How to filter the data in this scenario.

sunny_talwar — Fri, 27 Jan 2017 18:37:20 GMT

Because you might need wildcard search using Like and % because the description is not Test Team... it is amsjadhTest Team or something along those lines, right?

Re: How to filter the data in this scenario.

Anonymous — Fri, 27 Jan 2017 18:46:46 GMT

Sorry i didnt understand propely.

I am trying to use like this .

Where name not in ('*Test Team *','*Test Service*');

Correct me if i am wrong.

Re: How to filter the data in this scenario.

Anonymous — Fri, 27 Jan 2017 18:47:13 GMT

That didnt work after adding the expression in the list box

Re: How to filter the data in this scenario.

sunny_talwar — Fri, 27 Jan 2017 18:48:23 GMT

Something like this:

Where name not like '%Test Team%' and name not like '%Test Service%';

Re: How to filter the data in this scenario.

vishsaggi — Fri, 27 Jan 2017 19:14:53 GMT

Is it still displays the data with test ?

Re: How to filter the data in this scenario.

Anonymous — Fri, 27 Jan 2017 20:01:34 GMT

Hi sunny , I had leave the office for the day .

I shall try this one Monday.

Thanks for all your help.

Re: How to filter the data in this scenario.

Anonymous — Fri, 27 Jan 2017 20:03:34 GMT

Yes it was.

Re: How to filter the data in this scenario.

Anonymous — Fri, 27 Jan 2017 20:56:04 GMT

Hi sunny,

I have used something like this as it not Like operator was throwing error.

It is working fine now in the sample data at home.

''Where not WildMatch(name,'va*','sh*');

I shall try this at work on monday.

Re: How to filter the data in this scenario.

sunny_talwar — Fri, 27 Jan 2017 21:41:36 GMT

Just know that WildMatch works with QlikView's syntax where as Like can work with both QlikView and SQL. Not sure if you are doing this within the Select Statement or Load statement.

Re: How to filter the data in this scenario.

vishsaggi — Fri, 27 Jan 2017 21:46:42 GMT

Probably follow what sunny mentioned like filtering them in SQL Script.

Re: How to filter the data in this scenario.

Anonymous — Fri, 27 Jan 2017 22:15:26 GMT

Ok! thanks sunny .

I thought my logic works but with the sample data it is not behaving what i am expected to see on the pivot table.

Let me share the sample

Here you can see in the table I have chosen the organisation 'birmingham' and it is working fine in filtering the names accordingly in the load statement .But it it not deleting the records in the pivot table which is not required as service id's are still selected.

Dont know how to crack this.