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
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
Erwin 't Hoen
Have you taken a look at the List Operation activity?
Options of Intersect, Subtract or Equals may be helpful here.