Search code examples
datatableuipathuipath-studio

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


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!


Solution

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