topic Re: Filter for a Dimension in QlikView

Filter for a Dimension

Wed, 02 Oct 2013 12:23:47 GMT

Hi All

I am trying to out a filter onto a dimension where I only want to see a subset of a field.

I want to display the categories HAIR, ACCESSORIES and WATCH from a list of categories that are longer.

I cant get this to work. I have tried a few different methods but havent gotten a method to work yet. I have tried the only function where I have written Only({<Item.MainCategory={'HAIR,ACCESSORIES,WATCH'}Item.MainCategory) and this is not working. I get that there is an error in the expression.

I am stuck with this task at the moment. I dont know what how to write to add that filter onto the dimension. I am thankful for any help.

//Fredrik

Re: Filter for a Dimension

abeyphilip — Wed, 02 Oct 2013 19:59:40 GMT

Hi,

if(match(Item.MainCategory,'HAIR','ACCESSORIES','WATCH'), Item.MainCategory)

Regards,

Abey

Re: Filter for a Dimension

mikecrengland — Wed, 02 Oct 2013 20:07:46 GMT

I'm sure there's a better way, but you can do

If(Item.MainCategory = 'HAIR' OR Item.MainCategory = 'ACCESSORIES' OR Item.MainCategory = 'WATCH', Item.MainCategory)

Mike

www.fortunecookiebi.com

Re: Filter for a Dimension

stevedark — Wed, 02 Oct 2013 20:46:17 GMT

Hi Fredrik,

I would always recommend doing things like this in the load script, creating a separate field for the subset fields. The syntax would be something like:

LOAD

Category,

if(Category = 'HAIR' or

Category = 'ACCESSORIES' or

Category = 'WATCH', Category, null()) as [Subset Category],

[... rest of load statement ...]

If you would prefer just to do it in the dimension (as a Calculated Dimension) or a List Box (as an expression) the syntax is:

=aggr(maxstring({<Category={'HAIR','ACCESSORIES', 'WATCH'}>}Category ), Category)

Hope that helps,

Steve

http://www.quickintelligence.co.uk/

Re: Filter for a Dimension

Thu, 03 Oct 2013 07:28:48 GMT

Thank you. I didnt know you could write it with aggr and maxstring. I prefer to have it as an calculated dimension.

Re: Filter for a Dimension

Thu, 03 Oct 2013 07:32:43 GMT

Thank you for your input. I prefer to avoid writing the expression with an if clause when possible. The application is am working with is a very large and heavy application.

Re: Filter for a Dimension

Thu, 03 Oct 2013 07:33:01 GMT

Thank you for your input. I prefer to avoid writing the expression with an if clause when possible. The application is am working with is a very large and heavy application.

Re: Filter for a Dimension

stevedark — Thu, 03 Oct 2013 07:58:04 GMT

No worries. You mention that you have a large data set - the extra field will be slightly more efficient - but it would make your data model less tidy and could make selections a bit less obvious. The calculated dimension should work fine though.

- Steve