Deleting duplicated records and matching afterwards

Hi all,  I have a data set, as the picture below. So now, I try to have two tables, one with only unique 'Trial' records and one only with 'C2O' records. Is there an easy way to delete duplicates? In that example if a 'Trial' record gets recorded twice with the same 'MachineID', I want to to have it only once recorded in the new table.   After that I want to match these two tables with 'MachineID'. Sometimes a trial and C2O row have the same MachineID and I want exactly know how many matches there are. I tried to solve it with a Microflow, with the Xpath command: [ProductType='C2O' and MachineID!=NULL] for the one retrieve action and [ProductType='Trial' and MachineID!=NULL] for the other one , but I get wrong results (See Microflow in the picture). Hope someone can help me with this. Thank you!!  Jan  
2 answers

If you want to split the dataset into 2 tables, one for C2O and one for Trial and remove duplicates you could trythese steps:

  • Retrieve all your trial records from the dataset.
  • Loop over the retrieved records and before creating the new trial record try to retrieve the record from the trial table first if found perform a continu action in the loop else create the trial record in the trial table
  • Perform the same steps but now for the C2O records
  • You now have 2 tables with unique trial and C2O records
  • Retrieve both tables records so that you have 2 lists of records (or create these while looping for efficiency)
  • Loop over one of the 2 lists and perform a list operation to find the machineID of the iterator in the other list
  • If found in the list add this to a new list
  • Based on this new list you now know which records (machineIDs) are available in both trial and C2O lists and you can perfom actions based on this new list.


I hope this will help you further in achieving the desired functionality


Have you taken a look at the List Operation activity?

Options of Intersect, Subtract or Equals may be helpful here.