0

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!

1 Answers1

0

After hours of research, here is what I learned.

I can iterate on each item of the datatable by using FOR EACH ROW Activity.

So for every row item of my dtQuery, I add ASSIGN Activity that looks like this:

row(2) = [item i want to add]

But that doesn't answer my question. I want to know the count of each unique item with 2 criteria - They are same DATE and ERROR.

Maybe I can code directly on the Excel File?

So I researched for Excel Formula that looks like "Select Distinct Col1....etc."

I found this video tutorial, hope it might help: Countif

But its only for a single criterion, so I found this: Countifs

So to wrap it up, For Each Row Image

1.) I loop inside dtQuery using For Each Row Activity

2.) Inside loop, I 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 & ")"

Hope this help others who will be stumbling upon the same problem. Happy Automating! ^_^

  • Hi One more way to Use grouping of Date via linq too. :) that will give you output like:- DT.Select.AsEnumerable().GroupBy(Function(r) r.Field(Of String)("DATE")).[Select](function(g) g) it will return you result as Igrouping then you can use Foreach for further process – Aksh Feb 28 '19 at 10:23
  • @Aksh, thanks to that! I'll note it and use it on my next journey! ^_^ –  Mar 01 '19 at 00:31
  • I have shared this longback in Uipath community forum too. you can look over there. if not then i can share you too :) if needed – Aksh Mar 01 '19 at 08:18