How to filter the data in this scenario.

[Anonymous]

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.

[Anonymous]

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.

[Anonymous]

That didnt work after adding the expression in the list box

[sunny_talwar]

Something like this:

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

[vishsaggi]

Is it still displays the data with test ?

[Anonymous]

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

I shall try this one Monday.

Thanks  for all your help.

[Anonymous]

Yes it was.

[Anonymous · Accepted solution]

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.

[sunny_talwar]

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.

[vishsaggi]

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

[Anonymous]

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.