Announcements
The way to achieve your own success is the willingness to help somebody else. Go for it!
cancel
Showing results for
Did you mean:
Specialist II

## Count

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

Labels (1)
• ### General Question

2 Solutions

Accepted Solutions

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

MVP

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)``

3 Replies

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

MVP

May be using substringcount(), like:

MVP

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)``

Tags
Community Browser