hounddomain 2 Posted July 30, 2015 Report Share Posted July 30, 2015 Hi guys Hoping someone can help me crack this issue. I've got a data set that looks like this: Col1 Col2Peter BNEPeter SYDPaul BNEJohn BNEAlbert MELTina ABTPeter POL I can have this as either two lists, or a table. I'm trying to manipulate the data in ubot so I end up with the following: Col1 Col2Peter BNEPaul BNEJohn BNEAlbert MELTina ABT The data aligns in rows. I want to basically look at the list in Col1, and if I have a duplicate of it further down (but not up) I want to delete that record and the corresponding record in Col2 for that row. Basically I only want the first unique value in Col1 and the corresponding Col2 data. I've been playing around for quite a few hours with this, but I can't crack it. I thought I was onto something by splitting it into lists and using a series of nested if/then loops but I end up with errors. Any suggestions? I'm happy to use regex but frankly I'm pretty new at regex - I can input it and make it do basic stuff, but working out the actual string is still something I'm trying to learn. Appreciate any help hounddomain 1 Quote Link to post Share on other sites
the_way 52 Posted July 30, 2015 Report Share Posted July 30, 2015 Easy one..... First thing is you want to create a loop that goes through the table, sets a variable to the first name on the table, and then does a table search to return if any other of the names are found. Increment a number to check each row... If a duplicate name is not found....add to new table (keep it clean) If a duplicate is found...return row...you will need a loop while table row increment is <= total table rows. (something like that)delete duplicate rowonce the loop while breaks...add to new table delete row from table make sure you write your code functions into defines first, and then write your functionality outside of your defines, so when you run the code, you can see what's happening. This is the easiest way, then you will be able to move things about, and understand whats going on more. If you really want to understand your code inside out, do what i do, which is use a define even if its only for one "set". Everything can have a name that helps identify what it is doing, and working like this is the only way, when you want to save time and have a easily understandable program when you have to go back into it after a while. 1 Quote Link to post Share on other sites
hounddomain 2 Posted August 2, 2015 Author Report Share Posted August 2, 2015 and then does a table search to return if any other of the names are found. So, I use the original data set, and I've set my variable name to #row_content. I've added 'Peter' to the variable and I've run the following command: alert($plugin function("TableCommands.dll", "$table search", &table, #row_content, "Row Index")) It returns 0, because row zero is the first row where 'Peter' is found, but it's not the only row where that value is found. I need it to return the duplicate row numbers after the first find in a way that I can then use to subsequently manage (eg. delete those duplicate row numbers). May I please have some guidance on how I should best do this? I had a read through the $table search function, but it looks like it's only setup to return either row or colum value, and only the first one it finds chronologically. Quote Link to post Share on other sites
the_way 52 Posted August 2, 2015 Report Share Posted August 2, 2015 Give me 5 mins, i'll post the code to help you here... 1 Quote Link to post Share on other sites
the_way 52 Posted August 2, 2015 Report Share Posted August 2, 2015 define create_table_from_file { create table from file("C:\\Users\\pc\\Desktop\\Untitled 1.csv",&table) } define assign_first_row_variables { set(#name,$table cell(&table,0,0),"Global") set(#data,$table cell(&table,0,1),"Global") } define delete_first_row_from_table { plugin command("TableCommands.dll", "delete from table", &table, "Row", 0) } define search_table_for_name_variable { set(#name_match,$plugin function("TableCommands.dll", "$table search", &table, #name, "Row Index"),"Global") wait(2) } define delete_matching_row { plugin command("TableCommands.dll", "delete from table", &table, "Row", #name_match) } define set_row_increment_to_0 { set(#row_increment,0,"Global") } define assign_variables_to_clean_table { set table cell(&clean_table,#row_increment,0,#name) set table cell(&clean_table,#row_increment,1,#data) } divider create_table_from_file() set_row_increment_to_0() divider comment("loop while total table rows > 0") loop while($comparison($table total rows(&table),"> Greater than",0)) { assign_first_row_variables() delete_first_row_from_table() divider assign_variables_to_clean_table() search_table_for_name_variable() divider comment("loop while #name_match != -1") loop while($comparison(#name_match,"!=","-1")) { comment("if name_match = -1") if($comparison(#name_match,"= Equals","-1")) { then { } else { delete_matching_row() } } search_table_for_name_variable() } increment(#row_increment) } Tested it a couple of times and it does just what you need. Obviously adjust it as you need. 2 Quote Link to post Share on other sites
hounddomain 2 Posted August 2, 2015 Author Report Share Posted August 2, 2015 Tested it a couple of times and it does just what you need. Obviously adjust it as you need. Thankyou! I really appreciate the time you spent helping me out. I'll look over it and see where I was going wrong. Lots for me to learn obviously 1 Quote Link to post Share on other sites
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.