Count the number of occurrence of a field value in a table

[aguirre]

Hello,

I would like to show in a Text Object how many occurrence of TypeRequest=140 there are in table tab2

I have two tables:tab2

Request	TypeRequest
Request 1	120
Request 2	140
Request 3	140
Request 4	125
Request 5	140

tab1

Description	TypeRequest
descr 1	120
descr 2	125
descr 3	140
descr 4	150
descr 5	160

I have tried the following formulas but it seeems the result come from tab1:

=sum(if([TypeRequest]=140,1,0))

=Count(if (TypeRequest=140, 1))

=Count({$<[TypeRequest]={140}>} TypeRequest)

A workaround I found is  loading in tab2 another TypeRequest field with another name   and base my formula on it

ex.  =Count(if (TypeRequestNew=140, 1))

But maybe I can use an identifier for tab2 in my formula?

[yassinemhadhbi]

Good morning 

HAve you tried : 

=Count({$<[TypeRequest]={140},Request={'*'}>} TypeRequest)

[aguirre]

Thanks, sorry it doesn't work.

I accepted your answer but I forgot to change the field name.

So your solution is not working with my tests.

[yassinemhadhbi]

[eddie_wagt]

If you specifically want to count TypeRequest from tab2 then you should aggregate it to a dimension within tab2 like =aggr(count({$<TypeRequest={140}>}TypeRequest),Description). 

It's never good to count a keyfield so I advice you to load TypeRequest twice, one time as a keyfield and second time as alias. 

Example:

Tab2:
LOAD *
, TypeRequest as Value2
;
LOAD * INLINE [
Description TypeRequest
descr 1 120
descr 2 125
descr 3 140
descr 4 150
descr 5 160
] (delimiter is ' ')
;

If you do this than you can easily count like:

=count( {$<TypeRequest={'140'}>} Value1)