Is there any way to compare 2 list with similar attribute and returns distinct record

Consider 2 lists with the same attributes,  say one is list A (Participants for Football) and another list B (Participants for Cricket). Ask here is, one participant can take part in only one sport.

Attributes within both the lists: Name, Age, Sport, Email.

List General is a list with all records from list A and distinct records from list B and list B is the special list.

How can I find distinct records in list B as compare with list A?
Here both lists are having 30 K or more records

For Example :

mvp_badge
MVP

Hello Amit,

If I understood correctly, you need all the values in List B that do not exist in List A. Do you have these values in the database, or in a file? Do they have a unique way to identify them?

Assuming that they're in the database and you have a way to identify them besides the name and age, you could select the records from List B and LEFT JOIN them with List A. Then in the WHERE clause you could exclude all records that do not have a match in List A.

If this is a file, you can process it and get those records into a set of two lists. Then you can match them in a Timer and commit batches of records to the database, in order to avoid timeouts from looping 30k+ records.

Hi Afonso,

Thanks for the reply.

I am working on files and just to elaborate more on the use case.

I have 2 files with 30k or more records and I have to create a third file with all the records from file 1 and unique from file 2 as compared to file 1. So the third file will have all the unique records and preference for duplicate records should be given to file 1 and not file 2. Apart from that, I have to display a unique record count for each file on UI so can't go with the timer.

If we consider an example from the image attached. Unique records count for list A is 5 whereas unique record count for list B is 2 as it has 2 records which are already in list A.

Here the number of files can increase from 2 to 5 in some extreme cases.

As of now, I have implemented it like, copy all the records from the first file to the general list and then compare each email address from file 2 with the general list & if it's unique append it to the general list.
But this is a lengthy or time-consuming process.

Is there any extension you can suggest which will have input as 2 different lists with similar structure and as a result, it will return get only uniques records?

mvp_badge
MVP


Thank you for the explanation, I believe I understand your use case a little better. I don't think there's an Extension that will do this comparison out-of-the-box.

If you're willing to build your own, you could consider creating a solution using LINQ. You could take two lists as input parameters into an action and then use something like Except in order to obtain a list of unique values. But I am unsure of the performance that you'll obtain with 30k+ records.

The Forge has an extension with some LINQ use cases, but I believe you can only use it to query one list at a time.

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.