Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Checking if value exists among possible values

Hey guys. I've got a chart I'm having trouble with. So I have a table box that displays a number of records. There's one field that has a many to one relationship to each row in the table. So any record could have multiple possible values for this field. Let's say the record is Eric, and in the field HairColor, are the possible values Brown and Gray. I want my table chart to display the record Eric if Brown is one of the possible selected values for HairColor. So I could create a calculated dimension like this:

=if(Match(HairColor,'Brown'), true(),false())

What this does, however, is create two rows for Eric, one in which the calculated dimension is true (where HairColor=Brown), and one where it is false (where HairColor=Gray). This isn't what I want. I want one row for Eric, that displays True, since Brown is one of the possible values.

Any ideas?

Thanks!

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

In a straight table, it should do this by default settings (presentation tab in properties, suppress zero values).

Ah, this only works, if all expressions return zero for that line. So, if you have multiple expressions, use the above expression labelled Filter, then for all other expressions, write something like

= if( "Filter", YOUREXPRESSION , 0)

View solution in original post

10 Replies
Highlighted
Not applicable

You could either setup a calculated dimension like this:

=if(Color='Brown',Color)

Then specify to Supress when the value of the dimension Is Null.

Or you can use Set Analysis in whatever expressions are in your table to sum or count or whatever and filter where only Brown hair color is aggregated or shown.. for example:

count({$<Color={'Brown'}>} distinct Name)

Highlighted
Not applicable

Hmm.. I don't think my original example was accurate for what I'm trying to do. Let me try a slightly different one.

Let's say I have a table like this:

load * inline

[Person, HairColor

Eric, Brown

Eric, Gray

Dad, Gray

Sister, Brown

Mom, Gray

Mom, Blonde];

Now in my chart, I want to exclude any Person that has Gray hair. So the only row that should appear would be for Sister. Eric, Dad, and Mom should not appear, since Gray is a possible value for them.

Thanks again for the help.

Highlighted
MVP
MVP

Try a set expression with the e() function, something like

=count( {<Person = e({<HairColor ={Gray}>} ) >}  Person)

(e.g. in a chart with dimension Person).

Hope this helps,

Stefan

Highlighted

See attached.

Straight Table:

Dimension: Person

Expression: if(aggr(concat(HairColor, ','), Person) like 'Gray', NULL(), HairColor)

Highlighted
Not applicable

Ok so, I tried that and it seemed to be on the right track, but it doesn't actually remove the row, it just displays the count as 0 for the rows that I'd like to remove. I tried checking the Supress Zero Values checkbox, but that didn't do anything. Is there a way to make it remove the row if the count is zero?

Highlighted
MVP
MVP

In a straight table, it should do this by default settings (presentation tab in properties, suppress zero values).

Ah, this only works, if all expressions return zero for that line. So, if you have multiple expressions, use the above expression labelled Filter, then for all other expressions, write something like

= if( "Filter", YOUREXPRESSION , 0)

View solution in original post

Highlighted
Not applicable

Ahh so Supress Zero Values only works when all expressions for that row are zero? I didn't know that.

Very strange solution, but it appears to work.

Highlighted
Partner
Partner

Another option, if you want to check if a value is selected or possible, is to use the intersection in set analysis:

table:

Load * inline [

Values

a

b

c

d

e

];

And then, intersect current selection with the value we want to test:

count(distinct {$ * $<Values={'a'}>} Values)

Find an example attached.

Cheers,

David

Highlighted
Contributor III
Contributor III

This solved one issue for me. 

Thank you