Remove duplicate rows in data table but give priority to certain columns?

Help Studio
, ,
1

Hello,

Curious if anyone can help me with this. I’ve been reading the forums for a few days now. I see there are a lot of posts about removing duplicate rows in a data table based on certain criteria, but I’m not exactly sure how to apply this to my situation. I am not at all familiar with LINQ.

Here is what I am trying to accomplish:

  1. Remove duplicate rows (but keep one) that have the same Account ID and if they have the same Result Code. Two V’s, keep one. Two M’s, keep one.
  2. Remove the row altogether from the data table if the Result Code is “N”.
  3. If the Account ID has a duplicate, keep the one with Result Code “V”. “V” has priority over “M”.
  4. Final data table should have the Account ID Tolowercase.
  5. It would also be great if the final output could have all the "V"s listed first, then "M"s below.

Thank you to anyone that can help!

image

3

Hi @Josh_James

Here is the activity which is used to remove the duplicate rows in the datatable. “Remove duplicate rows”.
image

For sorting the Result code column first “V” after “M” use the “Sort DataTable” activity. Keep the ascending in order option in properties of this activity.
image

It may helps you!! Regards.

4

Hello Mahesh, and thank you for the reply!

The activity to remove the duplicates doesn’t appear to prioritize “V” over “M”. Let’s say I have two of the same Account IDs, but they each have different result codes, V and M. I would want to keep the V and not the M. How would I do that?

5

Oh! Maybe if I do the sorting first, so the Vs are at the top, then remove duplicates. I just need to be sure all the Vs stay.

6

Hi @Josh_James ,

Could you maybe also check the below workflow :
DT_RemoveDuplicates.zip (9.8 KB)

Using GroupBy and Selecting the row which contains the V result code if present. Using the OrderByDescending and ThenByDescending for Sorting based on two columns and for Updating the values to Lowercase a For Each Row is being used.

Let us know if you are not able to get the Required Output.

1 Like
7

Hello Arpan and thank you for the reply!

This seems to have fixed it!! Thank you so very much!!!

1 Like
8

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.