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 recordsFor Example :
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?
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.