Data Manipulation - Help Needed Please

[hounddomain 2]

Hi guys

 

Hoping someone can help me crack this issue.

 

I've got a data set that looks like this:

 

Col1                      Col2

Peter                     BNE

Peter                     SYD

Paul                      BNE

John                     BNE

Albert                   MEL

Tina                      ABT

Peter                    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                      Col2

Peter                     BNE

Paul                      BNE

John                     BNE

Albert                   MEL

Tina                      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

[the_way 52]

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 row

once 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.

[hounddomain 2]

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.

[the_way 52]

Give me 5 mins, i'll post the code to help you here...

[the_way 52]

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.

[hounddomain 2]

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