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!
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! ^_^