I have an Excel sheet which I have imported as a datatable. I want to filter that datatable to just show records for the first 5 unique references in a particular column - e.g.Company #1, Company #2, Company #3. There may be multiple rows per Company, and I want everything for the first 5 companies. The numbers may change every time the Excel is run through the bot, and are not sequential.
Is there a straightforward way of doing this that I’m not seeing?
getting the first 5 Company names you can do following:
arrNames = YourDataTableVar.AsEnumerable.Select(Function (x) x(ColNameOrIndex).toString.Trim).Distinct().Take(5).ToArray
Filtering the datatable with this 5 Company Names we can do e.g. with LINQ
(From d in YourDataTableVar.AsEnumerable Where arrNames.Contains(d(ColNameOrIndex).toString.Trim) Select d).CopyToDataTable
This is one of many options we can do the case
Safe to say I would have been a long time figuring that out myself! Thanks Peter, will give this a try
Just trying this - tried to test the first portion by logging the arrNames output and am getting a blank String array.
I’ve done an assign activity with arrNames=dtBookings.AsEnumerable.Select(Function (x) x(0).ToString.Trim).Distinct().Take(5).ToArray
Output is System.String
Apologies if I’ve missed something obvious here!
statement looks fione on the first view.
- set a breakpoint on the activity using this statement
- debug and get paused
- enter the statement into the immediate panel / wathc panel and check the result
Thanks Peter. I’m running 2018.4.4 so I don’t have the full range of Debug functions. I’ve tried stepping through it in Execute and checked the logs, but not seeing any errors, just getting a blank String array output