Count the number of time specific text appears in either of two fields

[PeterG]

New to Qlik and struggling with this one.

I have two columns (Column B & C) below, one or the other column may have a text entry that I want to use as a filter and/or in a count expression .  I want to be able to include the rows or count if the text entry starts with an 'E' , is in either of these two columns.

Tried a few different syntaxes in expression editor, but cannot seem to get this right.

Column1 (CustomerCode)	Column 2	Column 3	Desired Result
asg1234	E-1234	ZNRT	Include
etb5467	ert586	truuu	Include
ury4567	tyrber	wet876	dont include
agb1234	yrtu78	e.4568	Include
web1234	E-3456	e9875	Include
red456	urtyg	ou89uy	Dont include

[ManuelRühl · Accepted solution]

This works for me:

=IF(ISNULL(AGGR(COUNT({<Column2={"e*"}>+<Column3={"e*"}>}Column1),Column1)),'Dont Include','Include')

👍

[PeterG · Accepted solution]

Hi Mruehl,

firstly thanks so much for responding.  When I use your expression, where I replaced the result of the if to '0','1')

=IF(ISNULL(AGGR(COUNT({<Column2={"e*"}>+<Column3={"e*"}>}Column1),Column1)),'0','1')

I get an "OK " in the expression editor ,but the result in my visualisation (a KPI) is a 0 (Zero), I was expecting several thousand (as that is how may rows in the dataset have those columns with a code starting with e. 

I guess I need to wrap this into a count somehow so that those rows where the above result is equal to 1, get counted?   have tried several connotations but to no avail.

Thanks again 

[marcus_sommer · Accepted solution]

You may try it with:

sum(AGGR(COUNT({<Column2={"e*"}>+<Column3={"e*"}>}Column1),Column1))

[PeterG]

Hi Marcus,

Many thanks for taking time to respond, this worked for the count I needed.

Very much appreciated