How to Get Distinct Value of DataTable and Append New Column with the Count of each distinct value returned [UIPath][VB.Net]?

Question

I am a newbie in UIPath. I have a DataTable with these headers:

1.) Date

2.) Error

I want to extract a Distinct Date for every error, and use this code:

dtQuery = ExtractDataTable.DefaultView.ToTable(True,{"Date","Error"})

With this, I get my desired result. My problem is how can I append (a new Column, "Count") EACH COUNT of DISTINCT VALUES given? For Example:

DATE       |  ERROR               |  COUNT

2/27/2019  |  Admin Query String  |  

2/27/2019  |  404 Shield          |  

2/26/2019  |  404 Shield          |  

2/25/2019  |  404 Shield          |  

2/25/2019  |  Admin Query String  |  

I tried to use ADD DATA COLUMN ACTIVITY with these properties:

Column Name = "COUNT"

Data Table = dtQuery

DefaultValue = ExtractDataTable.DefaultView.ToTable(True,{"Date","Error"}).Rows.Count

But by using this, it gives me this:

DATE       |  ERROR               |  COUNT

2/27/2019  |  Admin Query String  |  5

2/27/2019  |  404 Shield          |  5

2/26/2019  |  404 Shield          |  5

2/25/2019  |  404 Shield          |  5

2/25/2019  |  Admin Query String  |  5

Thanks in advance! Happy coding!

Answer 1

Hi, so what you need to do is to use ' For Each Row'

1.)  loop inside dtQuery using For Each Row Activity

2.) Inside the loop, add Assign Activity with this code

row(2) = "=COUNTIFS('LookUp Sheet'!B:B,'Result Sheet'!A" & indexerRow + 2 & ",'LookUp Sheet'!D:D,'Result Sheet'!B" & indexerRow + 2 & ")"