Dear reader,
I have a table 1 containing 100 different names in one column. I also have “standard” table 2 with 20 different “allowed” names in one column.
I would like to perform a robot which compares the names in table 2 with table 1, and gives me a new table 3 with names which - according to table 2 - are not written in table 1.
Who can maybe help me with my question?
Best, Matt
Hello.
I will suggest you use LINQ coding which let’s you instantly filter a table on criteria.
This is how I would do it:
table1.AsEnumerable.Where(Function(r) not table2.AsEnumerable.Select(Function(r2) r2(0).ToString.Trim.ToUpper).ToArray.Any(Function(x) x=r(0).ToString.Trim.ToUpper) ).CopyToDataTable
What I am doing in that code is, creating an array of the items in table2 with allowed names, and using .Any() to check the array and compare the values in table1. The .Where() goes through each item in table1, using that comparison, then .CopyToDataTable to create a new table. I added “not” infront of the comparison so it takes rows that are not contained in table2’s array of items. (also, I used .ToUpper so the tables don’t require certain case on the letters.)
I hope that is clear.
Disclaimer: I did not test the LINQ and am going from memory, so sorry for any mistakes.
There are other ways to accomplish this with loops too, like a combination of LINQ and ForEach/IF or a ForEach/IF embedded in a ForEach, but sometimes it all clutters your code and is slower.
Regards.
Hello,
Thank you for your quick reply. I am not an experienced developer, so your solution would for me be too complicated.
As you said, there is also a solution available by using a ForEach/IF embedded in a ForEach. If you could help, how would this solution look like? The speed of my robot is not an issue.
Maybe this helps to give some more information. When I want to validate table 1 with 1 condition like Name = John, for me this is not a problem.
However, now I want to validate table 1 with a condition which contains 20 different names in table 2, I don’t know how this would look like.
My question could also be: How to use a condition which contains more than one value and is located in a separate file and column?
Best, Matt
Just doing a quick answer here as showing examples of embedded ForEaches and whatnot takes longer. But, if you still don’ t understand, let me know, and I can get back to you again at a later time.
Here is what it would look like:
Assign table3 = table1.Clone // creates a blank table with same columns to add rows to
For each row1 In table1 // For each Data Row activity OR generic For each activity with TypeArgument as Data Row type
For each row2 In table2 // loop through 2nd table to compare value with 1st table
If row1(0).ToString.Trim.ToUpper = row2(0).ToString.Trim.ToUpper // compare value in 1st column of each table --- Change 0 to other index or columnname (as String) if desired
Add Data Row // place row1 in the Data Row property and table3 in Data Table --- Adds row to table
Write CSV or Write Range // Output table3
I hope the above is clear. Also read the comments in each line for description.
Regards.
Dear ClaytonM,
Yes, this helps a lot. Thank you so much.
However, the condition you send:
row1(0).ToString.Trim.ToUpper = row2(0).ToString.Trim.ToUpper
gives me an error. I added my workflow to this topic, I think I made a small mistake in the “get row item” in the “For Each 1” or “For Each 2”.
Is it possible for you to have a quick look? Again, I really appreciate your help.
Best, Matt
Main.xaml (18.2 KB)
Standardtabel.xlsx (8.4 KB)
Names.xlsx (8.4 KB)
Dear ClaytonM,
I gave unique rownames to the rows in the ForEach in datatable, however the condition you send me still give an error. I also tried to put the columnnames in “” but this also didn’t help.
I added this snapshot in order to explain my error. Is it possible you could help me with a solution?
Best, Matt
Hello.
You had a few errors.
You want to loop through the Namestable and For each row, loop through the Standardtable. Therefore you need to embed the For eaches, so you can loop through each row in Standardtable for each row in Names (if that makes sense. And, use the If inside the second for each to compare the rows from both tables.
It will look like this:
Also you need to use a String variable type for the Names and NamesStandard variables (you used GenericValue). And, those 2 variables should be the column name that you want to compare in both tables respectfully.
For example: row1(“column1”).ToString and row2(“column2”).ToString
or row1(Names).ToString.Trim.ToUpper = row2(NamesStandardtable).ToString.Trim.ToUpper
Here is your workflow with those changes: Main (11).xaml (15.9 KB)
Regards.
Dear Clayton,
Thank your for extensive reply. This sounds very good, for me it is also much more logic than my first workflow. I compared the differences you made.
However, when I run the workflow by using a write line on Names.Tostring, I get the following output:
Do you maybe know how to resolve this?
Best, Matt
Sure, that is usually a simple fix.
It means the variable you are using is empty and needs a value at the activity where you are using it. However, I can’t tell you how to fix it without seeing an image of where you are using the variable that caused the issue.
Regards.
Let me make a guess of what the issue is though.
When I gave you the .xaml file back to you, your column variables, Names and Namesstandardtabel (in the variable section), should have a string in the default value.
columnname1 and 2 will be from the .xlsx files of the columns you are reading.
Hope that was the problem.
Regards.
Hi Clayton,
For me this is a tough one. I have values in the outputline, however these are the columnnames. I think the result is almost there!
I made some snapshots and also attached the files, if it’s possible maybe you could have a quick look.
Best, Matt
Main 2.xaml (16.3 KB)
Names.xlsx (8.4 KB)
Standardtabel.xlsx (8.4 KB)
Hi.
Looking over it, it looks correct. Your output in your screenshot looks correct, also. It appears to be working for you.
Are you still getting any errors?
Let me know.
Thanks.
Hi Clayton,
Yes, the error is that I receive the columnnames in the output instead of the values of the relevant rows (the names).
I expect to see the names of my table 1 which do not popup in my table 2 in the output.
See the snapshot below:
Best, Matt
Yes, to output the items in the tables, you need to reference the row variables which is row1 or row2 in your case.
row2(NamesStandardtabel).ToString
or
row1(Names).ToString
Regards.
Dear Clayton,
Thanks, this is going very good. However, I only want to see the names from the NamesTable that do not pop-up in the NamesStandardTable. (so only the names of Chris and Sophie once in the example files)
Now I see the names from all the rows from both tables. Do you have an idea how such a condition would look like? I added the output to this topic in a snapshot.
Best, Matt
You probably want to output the values in row1 when a match is found.
So place the Write Line on the True side (because a match is found) and use:
row1.Item(Names).ToString
Dear Clayton,
I should also expect that that’s true, but the output line gave me too much and not correct data.
To make the robot a bit faster, I transformed the data into a CSV-file. Your code is very useful, it kept me thinking and I did the following:
Now it works perfect. Thanks for all your attention and help.
Best, Matt
ashokkarale
anjani_priya
Dheerendra_vishwakarma
Parvathy
Aakash_Singh_Rawat
Luis_Fernando
bjorn2390
neco
pere
Shiva_Nikhil