topic Re: Checking if value exists among possible values in QlikView

Checking if value exists among possible values

Thu, 28 Feb 2013 23:03:49 GMT

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!

Re: Checking if value exists among possible values

Fri, 01 Mar 2013 02:05:03 GMT

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)

Re: Checking if value exists among possible values

Fri, 01 Mar 2013 18:48:56 GMT

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.

Re: Checking if value exists among possible values

swuehl — Fri, 01 Mar 2013 18:56:50 GMT

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

Re: Checking if value exists among possible values

Nicole-Smith — Fri, 01 Mar 2013 18:56:54 GMT

See attached.

Straight Table:

Dimension: Person

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

Re: Checking if value exists among possible values

Fri, 01 Mar 2013 19:22:51 GMT

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?

Re: Checking if value exists among possible values

swuehl — Fri, 01 Mar 2013 19:42:05 GMT

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)

Re: Checking if value exists among possible values

Fri, 01 Mar 2013 19:47:10 GMT

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.

Re: Checking if value exists among possible values

anguila — Fri, 16 Dec 2016 12:55:47 GMT

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

Re: Checking if value exists among possible values

baliyan_vinay — Tue, 17 Mar 2020 13:52:53 GMT

This solved one issue for me.

Thank you

Re: Checking if value exists among possible values

stantrolav — Thu, 27 Aug 2020 08:34:04 GMT

Bliliant:

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

Anguila thanks.