N number of Random Records per Unique Combination (Group/Key)

I am working on developing a system health check tool to validate that 4 different systems are in sync. To do that, I need to create a sample dataset of random N number of records for every unique group/key/combination from a main data set everyday. All 4 systems will be checked for records from this sample dataset and any differences will be highlighted using conditional formatting.

For example, there will be a report in excel (main data set) with the following fields (and some more) and thousands of rows. Let’s say I want 5 records for each unique combination (group) of the fields [Client-Contractor-Distribution Center-Service Level-Alert Value-Status]. Reference Number is the column with unique ID’s for which we need the 5 records.

I need help with creating a datatable with the above criteria.

enter image description here

Are you meaning grouping the data by the 5 cols Client-Contractor-Distribution Center-Service Level-Alert Value-Status?

for grouping data have a look here:

In case of our understanding from above is correct we can also provide an effort reducing the effort for constructing the 5 col group keys. So let us know

ok, this looks like Group By on 5 Key Cols
had you checked the provided Link?

With this approach

extended to the 5 cols you have an option for doing grouping without LINQ
otherwise set up an LINQ or LINQ Hybrid (combined with essential activities) approach

Let us know in case of you need further help

Hi @ppr , Thanks for the quick response.
However, I don’t really understand the guide you have posted.
I am just a beginner at RPA and UiPath.
And I have never even heard of LINQ.

But from what I could figure out reading the example, the grouping is being used to group by key columns and then the other columns are being aggregated.

My problem is that I don’t need an aggregation.
I need 5 random records per 6 key column group in the as is state as individual records.

we did also shared a non LINQ option and did mention this

aggregation / gropu member processing can be everything like:
Sum, AVG, Making seperate Datatable from Group Members, …

As you mentioned the colored blocks, so you are also interested on a particular processing of the group members

So we do understand your requirement like:
in a datatable with a lot of rows following is to do:

  • group datarows by the values of the columns
    [Client-Contractor-Distribution Center-Service Level-Alert Value-Status]

  • pick up from each group members randomly 5 records

  • return it as datatable.

Once you will confirm our understanding we will setup a suggestion, with the focus to get handled also with less UiPath knowledge. Fine for you?

This exactly the requirement!
This datatable will then be used to scrape data from different systems but I will be able to manage that.

I have attached a sample input file for reference. With 2 sheets Input and Sample of desired output.
SampleData.xlsx (36.2 KB)

ok, give us some little time

@ppr , I have also attached a Sample Data file for you ease and reference.