Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for
Did you mean:
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
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)

10 Replies
Not applicable
Author

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)

Not applicable
Author

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:

[Person, HairColor

Eric, Brown

Eric, 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.

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

MVP

See attached.

Straight Table:

Dimension: Person

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

Not applicable
Author

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?

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)

Not applicable
Author

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.

Partner - Creator

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)

Find an example attached.

Cheers,

David

Contributor III

This solved one issue for me.

Thank you

Community Browser