Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
bijoylalu
Not applicable

How to check for a Value in a column while filtering

Hi,

         How can I check whether a value exist in a column while filtering the same column??

          For instance, in my current table I have a 'GROUP' column with distinct values (G1 to G8). I have visualized a graph for which all groups are not applicable, created a filter for GROUP. If G1 is the only group which is applicable to the graph, and in filter if I am selecting G1, graph should be shown and if G1 is not selected in the filter, a message 'Graph not relevant' should be shown in the KPI I created near the graph.

         I have created an expression measure for the KPI as shown below which is working for my requirement if I am selecting only one value in the filter.

                               if(GROUP like 'G1','-','Graph not relevant')

         But if I am selecting any more than one value including 'G1', the value passing to KPI is 'Graph not relevant'. Is there any method to check whether the filter contains value 'G1', so that the number of values selected in the filter will not effect the condition check.

        Please suggest, Thanks in advance.

1 Solution

Accepted Solutions
Gysbert_Wassenaar
Not applicable

Re: How to check for a Value in a column while filtering

By filter you mean selection, right? Then you can test with =if(substringcount(getfieldselections(GROUP, ',', 99),'G1'),'Match', 'No match')


talk is cheap, supply exceeds demand
10 Replies
Gysbert_Wassenaar
Not applicable

Re: How to check for a Value in a column while filtering

By filter you mean selection, right? Then you can test with =if(substringcount(getfieldselections(GROUP, ',', 99),'G1'),'Match', 'No match')


talk is cheap, supply exceeds demand
sunny_talwar
Not applicable

Re: How to check for a Value in a column while filtering

Try this instead:

If(SubStringCount(Concat(DISTINCT '|' & GROUP & '|', ',"), 'G1') = 1, '-', 'Graph not relevant')

or

If(SubStringCount('|' & GetFieldSelections(GROUP, '|,|') & '|', 'G1') = 1, '-', 'Graph not relevant')

bijoylalu
Not applicable

Re: How to check for a Value in a column while filtering

Thanks Gysbert for the quick response, appreciated.

Its working fine, but for my understanding what if 'G1', 'G2'(two or more groups) are relevant for the graph, how can we acheive the same for the above logic??

sunny_talwar
Not applicable

Re: How to check for a Value in a column while filtering

Add two statements separated by and

If(SubStringCount(Concat(DISTINCT '|' & GROUP & '|', ',"), 'G1') = 1 and

   SubStringCount(Concat(DISTINCT '|' & GROUP & '|', ',"), 'G2') = 1, '-', 'Graph not relevant')

or

If(SubStringCount('|' & GetFieldSelections(GROUP, '|,|') & '|', 'G1') = 1 and

   SubStringCount('|' & GetFieldSelections(GROUP, '|,|') & '|', 'G2') = 1 = 1, '-', 'Graph not relevant')

sunny_talwar
Not applicable

Re: How to check for a Value in a column while filtering

And if it is either G1 or G2 condition, then may be this

If(SubStringCount(Concat(DISTINCT '|' & GROUP & '|', ',"), 'G1') = 1 or

  SubStringCount(Concat(DISTINCT '|' & GROUP & '|', ',"), 'G2') = 1, '-', 'Graph not relevant')

or

If(SubStringCount('|' & GetFieldSelections(GROUP, '|,|') & '|', 'G1') = 1 or

  SubStringCount('|' & GetFieldSelections(GROUP, '|,|') & '|', 'G2') = 1 = 1, '-', 'Graph not relevant')

bijoylalu
Not applicable

Re: How to check for a Value in a column while filtering

Thank you Sunny, it worked for me, taken the implementation logic from your script

Sunny T wrote:

And if it is either G1 or G2 condition, then may be this

If(SubStringCount(Concat(DISTINCT '|' & GROUP & '|', ',"), 'G1') = 1 or

  SubStringCount(Concat(DISTINCT '|' & GROUP & '|', ',"), 'G2') = 1, '-', 'Graph not relevant')

or

If(SubStringCount('|' & GetFieldSelections(GROUP, '|,|') & '|', 'G1') = 1 or

  SubStringCount('|' & GetFieldSelections(GROUP, '|,|') & '|', 'G2') = 1 = 1, '-', 'Graph not relevant')

bijoylalu
Not applicable

Re: How to check for a Value in a column while filtering

Hi Sunny,

          I have one more doubt coming up, how can I hide the message 'Graph not relevant' if i am not making any selection on the 'GROUP'(as by default all values of the group (G1 - G8) will be part of the dataset) ?

bijoylalu
Not applicable

Re: How to check for a Value in a column while filtering

Hi,

          I have one more doubt coming up, how can I hide the message 'Graph not relevant' if i am not making any selection on the 'GROUP'(as by default all values of the group (G1 - G8) will be part of the dataset) ?

sunny_talwar
Not applicable

Re: How to check for a Value in a column while filtering

May be this:

If(GetSelectedCount(GROUP) = 0, ' ',

If(SubStringCount(Concat(DISTINCT '|' & GROUP & '|', ',"), 'G1') = 1 or

  SubStringCount(Concat(DISTINCT '|' & GROUP & '|', ',"), 'G2') = 1, '-', 'Graph not relevant'))