topic Excluding the value from a dimension in QlikView

Excluding the value from a dimension

Thu, 20 Jan 2011 12:18:14 GMT

Imagine North,East,South and West are the area and A,B,C and are the groups which are there in each of the area. What I want to do is I want to exclude B group only from the East area. Rest of the area can have all the groups. I want to do this filteration at the dimension level.

Could you please help me out!

I wrote the below code to to exclude East from the Area. I don't know what is the code I have to write to exclude B Group only in East Area

=if(Area<>'East',Area)

----------------- Data for your understanding -----------------

Area

North

East

South

West

Group

Excluding the value from a dimension

Thu, 20 Jan 2011 12:22:41 GMT

load *

where Group <>'B';

Excluding the value from a dimension

Thu, 20 Jan 2011 12:25:27 GMT

Thanks Surender

But I don't want to do that at script level. Do you have anyother suggestion other than doing at the script level.

Excluding the value from a dimension

Thu, 20 Jan 2011 12:35:40 GMT

if you want to do it in the chart level u should use

if(Group <>'B',Area) as dimension. Also make sure u supress null values for this dimension on the chart.

Excluding the value from a dimension

Thu, 20 Jan 2011 12:47:15 GMT

I already tried that but it is incorrect.

The code which you have given will exclude Group:B from all the Areas. Where as I want to exclude Group:B only from East(Area).

Its tricky right!!!

Excluding the value from a dimension

Thu, 20 Jan 2011 12:55:31 GMT

Please use

if(Group <>'B' and Area <>'East' ,Area)

here the codition has to fullfill both the conditions and will produce the desired output.

Please do le me know if it helps

Excluding the value from a dimension

Thu, 20 Jan 2011 13:01:06 GMT

Sorry Surender you are wrong again.

Area<>East will exlude East from the Area. Where as I don't want to do that.

I just want to Exclude Group B one and only from Group called East. In short A,C,D groups can be there in East but B should not be there in East(Only in East).

Very tricky isn't? I am also trying this from long time

Excluding the value from a dimension

Thu, 20 Jan 2011 13:03:33 GMT

Pls send me the qv doc..

Excluding the value from a dimension

Thu, 20 Jan 2011 13:33:16 GMT

Hi Surender

As requested attached you the doc. Hope it will be easy now for you.

Excluding the value from a dimension

Thu, 20 Jan 2011 14:07:50 GMT

Hi,

The following expression should resolve the issue:


=IF(Area='East' AND Group='B',Null(),Group)

The document is also attached.

Cheers,

Haneesh

Excluding the value from a dimension

Thu, 20 Jan 2011 14:21:02 GMT

Hi,

What Haneesh suggested is the right method. Please find attached the QVW file again.

Regards,

Sajeevan

Excluding the value from a dimension

Thu, 20 Jan 2011 14:49:48 GMT

Thanks Haneesh

But could you please explain what does that code does. What does that Null() do in the if condition?

Excluding the value from a dimension

Thu, 20 Jan 2011 14:55:32 GMT

The function null() returns a null value. When the condition is satisfied (Area='East' AND Group='B'), then a null value will be returned, else the group value will be returned. So, in this case when the condition is satisfied we get a null value and the chart will suppress this null value.

Hope this explains the expression clearly.

Excluding the value from a dimension

Thu, 20 Jan 2011 14:58:39 GMT

Hi Haneesh,

Understood now!!!

If Area='East' and Group='B' then it will be Null() or else it wil display the group.

Many thanks to you and Sajeevan too.