Count

[qv_testing]

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, @tresesco  - can you please take a look 

Thanks in Advance!

[Kushal_Chawda · Accepted solution]

@qv_testing  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)

[tresesco]

May be using substringcount(), like:

[MarcoWedel · Accepted solution]

You could also use expressions like

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

or

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)