Count Row Data Equals a Value

[Rhys18 (Rizky Albert Hartono)]

Hi,

Is it possible to count how many rows equal value in 1 column?

Example :
ColumnA
John
Adam
John
John
Adam
Alan

Expected Output result is :

If ColumnA Equals John then 3
If ColumnA Equals Adam then 2
If ColumnA Equals Alan then 1

[Anil_G (Anil Gorthi)]

@Rhys18

Create a datatable with 2 columns one for name and another for count

now use the below expression in assign

finaldt = dt.AsEnumerable.GroupBy(function(x) x(0).ToString).Select(function(x) x.LoadDataRow({x.Key,x.Count.ToString},False)).CopyToDataTable

finaldt each row will have a name and the corresponding count

or

If you need only for one value

then you can use filter datatable and get the count of the output table rows

cheers

[Rhys18 (Rizky Albert Hartono)]

In my case 1 column has more than 10 unique value. Using filter data table is not efficient for this case right?

[Anil_G (Anil Gorthi)]

@Rhys18

if you need count of only one unique value then go with filter datatable

if you need for all unique then use the first method and it will gvie you all unique names and the counts in datatable

cheers

[vrdabberu (Varunraj Dabberu)]

Hi @Rhys18

Try this:
=> Build Data Table

Output → InputDt

=> Use below syntaxes in Assign activity:

Assign activity -> valueCounts = InputDt.AsEnumerable().GroupBy(Function(row) row.Field(Of String)("ColumnA")).ToDictionary(Function(Group) Group.Key, Function(Group) Group.Count())

Assign activity -> countJohn = If(valueCounts.ContainsKey("John"), valueCounts("John"), 0)

Assign activity -> countAdam = If(valueCounts.ContainsKey("Adam"), valueCounts("Adam"), 0)

Assign activity -> countAlan = If(valueCounts.ContainsKey("Alan"), valueCounts("Alan"), 0)

XAML:
Sequence80.xaml (11.7 KB)

Regards

[Rhys18 (Rizky Albert Hartono)]

Hi,

Sorry I haven’t updated the Result. I use this in assign activity and the result is as expected:

int32 : dt.AsEnumerable.Where(Function (x) x(“Your Column Name”).ToString.Trim.Equals(“Value”)).Count

Thanks everyone!