Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QLIKWORLD LIVE! MAY 16 - 19TH, EARLY BIRD DISCOUNTS! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
aguirre
Contributor III
Contributor III

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

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

RequestTypeRequest
Request 1120
Request 2140
Request 3140
Request 4125
Request 5140

 

tab1

DescriptionTypeRequest
descr 1120
descr 2125
descr 3140
descr 4150
descr 5160

 

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?

 

Labels (1)
4 Replies
yassinemhadhbi
Creator II
Creator II

Good morning 

HAve you tried : 

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

Best Regards
Yassine Mhadhbi
aguirre
Contributor III
Contributor III
Author

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
Creator II
Creator II

yassinemhadhbi_0-1616754021127.png

 
Best Regards
Yassine Mhadhbi
eddie_wagt
Partner
Partner

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)