Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to count only the items that contains a specific word

Hi Everybody.

In a past discussion i asked about how to count only the items that contains a word, the question was resolved usin the expression count(if(([Estado])='closed',[Estado])), this expression allows count in the column Estado only the cells that contains "closed".

Now i want to make the same, but in this new case the cell contains more text not only one word, how can i do the same expression with the sentence contains????

1 Solution

Accepted Solutions
Not applicable
Author

= sum(   if( SubStringCount( Estado, 'ERROR') , 1 , 0)  )  ?

JJ

View solution in original post

9 Replies
its_anandrjs

Hi,

You have to use

count(if(([Estado] ='closed'  and [Estado] ='Second word' and [Estado] ='Third word'  ,[Estado]))

Like ways

Rgds

Anand

Not applicable
Author

sorry for my english, i think that you dont undesrtand my question.

I'm going to explain you with an example:

Estado
Prueba, Reset, Equis, Rotos, ERROR
Prueba, Reset.
Rotos, ERROR
Equis

I got the column called "Estado", and i want to count only the fields that contains the word "ERROR", in this example the result will be 2.

I hope this can helps.

Not applicable
Author

= sum(   if( SubStringCount( Estado, 'ERROR') , 1 , 0)  )  ?

JJ

its_anandrjs

Hi,

Ok use this expression

=Count( If( Right ( Estado, 5 ) = 'ERROR', Estado ) )

the result wil be 2

Rgds

Anand

Not applicable
Author

Thanks a lot for you answer.

=Count( If( Right ( Estado, 5 ) = 'ERROR', Estado ) ).

I use your suggestion, and the result was 7, but it should be 8.

May you explain me what is the purpose of the right command? and why do you put a 5 after Estado.

Sorry for the question but i'm only a begginer.

BR

Not applicable
Author

Thanks a lot, it works.

Not applicable
Author

@kramelot - the Right Function with the 5 means the 5 most right characters (ERROR is 5 characters). This solution would have worked if ERROR was always the last entry in the field AND always spelt the same with no trailing spaces.

Is ERROR always in upper case (i.e. Human Entered or Electronic)? Reason I am asking is it is searching a literal - meaning 'Error' would not pick up for instance.

If it is human entered you can add an Upper function - something like:

sum(   if( SubStringCount( Upper(Estado), 'ERROR') , 1 , 0)  )

its_anandrjs

Hi krmelot,

I think the ERROR in your field Estado is not in correct lenght thats why it give wrong result Right( Estado, 5 ) means from right in the field Estado it identify the ERROR but if it is only in right and the word is ERROR.

Rgds

Anand

Not applicable
Author

I got by doing the following way

cout({<[Estado]={"*ERROR*"}>} [Estado])

* I am using a translator