Duplicate names\values

[Jeeru_venkat_Rao]

Hello friends,

I have excel data of healthcare.
Sometimes 1 patient visit 2 times in specific appointment date.
11/15- Pain
11/15- Knee

So, my requirement is if patient comes 2 times and whose treatment is equal to “Knee” then this line of item only should be read by bot.

See below screenshot. I highlighted 2 examples and data will not come in sequence.

Pls help.

[Sudeen_Raj_Shetty]

Hi @Jeeru_venkat_Rao

You can try this linq
dt_Output=dt_Input.AsEnumerable() _
.GroupBy(Function(row) New With {
Key .PatientName = row(“Patient Name”).ToString(),
Key .AppointmentDate = row(“Appointment Date”).ToString()
}) _
.select(Function(x) x.OrderBy(Function(z) If(z(“Visit Type”).ToString() = “Knee”, 0, 1)).ThenBy(Function(z) z(“Visit Type”))(0)) _
.CopyToDataTable()

Hope this helps!

[Anil_G (Anil Gorthi)]

@Jeeru_venkat_Rao

Please try this

Dt =Dt.AsEnumerable.GroupBy(function(x) x("Name").ToString).Where(function(x) x.Count>2 AndAlso x.Any(function(y) y("Type").ToString.Equals("Knee"))).Select(function(x) x.First(function(y) y("Type").ToString.Equals("Knee"))).CopyToDatatable

Cheers

[Chaitanya_Doneparthi]

Hey @Jeeru_venkat_Rao,

To achieve your requirement, you can use LINQ to filter the Excel data for patients who visited twice on the same appointment date, and whose treatment includes “Knee.” Here’s how you can implement it in UiPath:

Solution using LINQ :

1. Read Excel Data:

• Use the Read Range activity to load your Excel data into a DataTable (let’s call it dtPatients).

2. Add an Input for Treatment Type:

• Use an Input Dialog or Assign activity to specify the treatment type (e.g., treatmentType = "Knee").

3. Use LINQ to Filter Data:

vb

Copy code

Dim treatmentType As String = "Knee" ' Replace this with your dynamic input if needed

Dim result = (From row In dtPatients.AsEnumerable()
              Group row By Patient = row.Field(Of String)("Patient"), AppointmentDate = row.Field(Of Date)("Appointment Date") Into grp = Group
              Where grp.Count() > 1 AndAlso grp.Any(Function(r) r.Field(Of String)("Treatment").ToLower() = treatmentType.ToLower())
              From item In grp
              Where item.Field(Of String)("Treatment").ToLower() = treatmentType.ToLower()
              Select item).CopyToDataTable()

4. Explanation:

• Treatment Type as a Variable: The treatment type (treatmentType) can be set dynamically.
• ToLower(): Ensures case-insensitive comparison.
• Group By: Groups by “Patient” and “Appointment Date”.
• Count: Checks for multiple visits on the same date.
• Any: Looks for any row with the specified treatment type.
• CopyToDataTable: Converts the result back to a DataTable.

5. Write Data Back to Excel (Optional):

• Use the Write Range activity to write result to a new Excel sheet.

Additional Notes:

• Ensure your column names in the LINQ query match exactly as in your Excel file.
• If dtPatients has inconsistent data types, you might need to adjust the .Field(Of String) or .Field(Of Date) methods.

This solution is flexible and allows you to specify any treatment type without changing the code. If you have more questions, feel free to ask!

Cheers!