topic Re: Count in App Development

Count

Nagaraju_KCS — Mon, 05 Jun 2023 14:16:18 GMT

Hello Experts,

I have two tables and after some transformations my data looks like below

Here scenario is, I have to split and count the data Covered field and that should be associated with countries with first table.

Let say If I take count of regions

USA - we have 4 values, but one value is not associated with USA (so count should be 3)

NZ- count should be 2

Region	Covered	Count
USA	USA1,USA3,USA4,NZ1	3
NZ	NZ1,NZ2	2

@marcus_sommer, @Kushal_Chawda, @MarcoWedel, @tresB - can you please take a look

Thanks in Advance!

Re: Count

Kushal_Chawda — Mon, 05 Jun 2023 14:49:00 GMT

@Nagaraju_KCS try below expression. Assuming both tables are connected on Region and Covered is comma seprated field already available in the table.

=Count(distinct {<region_id ={"=index(Covered,region_id)"}>}region_id)

Re: Count

tresB — Tue, 06 Jun 2023 04:39:54 GMT

May be using substringcount(), like:

Re: Count

MarcoWedel — Tue, 06 Jun 2023 20:00:54 GMT

You could also use expressions like

-Sum(Covered like '*'&Region_ID&'*')

Sum(Sign(Index(Covered,Region_ID)))

To avoid false positive matches for Covered values containing Region_IDs as substring (e.g. "USA1" <-> "USA11" ), you could extend the expressions to something like:

-Sum(','&Covered&',' like '*,'&Region_ID&',*')

or in the case of Kushal's solution

Count(DISTINCT {<Region_ID ={"=Index(','&Covered&',',','&Region_ID&',')"}>} Region_ID)