How to know if columns contains value and Join?

Help
1

¡Hi!

I want to join 2 datatables but my cells cant be equals, maybe can contains part of other cell, for example

DataTable 1
image

DataTable 2
image

As you can see, in my second datatable the cell A2 and B3 contains the cell in first datatable A2 and A3 i want to generate my output like

image

¿How can i use Join for do this? Bless!

2

@inf_L it looks like you are just adding all of the data from Table2 into Table1 is that correct? There isn’t any common information you are JOINing on? Usually if you are using a JOIN function you’d join on a unique identifier.

Will both datatables always have the same number of rows? If not, how do you want that handled?

3

U cant suppose than in my second datatable all the columns can be “ID” too, now, dont will have the same number of rows, for that reason i just want to get all the matched rows of datatable 1 with 2

4

So table2 will have a column labeled ID and will be used to join on the ID field from table1?

5

Can be a option, but, as you can see, when i use Join with “Column0 = Column1” will dont have match because is not equals, but i we look that cell, the cell can contains the value in the string.

For example

“11” | “F11”

My second Cell contains “11” in “F11”

6

Ok i think I understand. In table2, if it is F11 you want it to match 11 from table1.

If that is the case, then you have to do it in 2 parts. First, you have to clean up the table2 data, then you have to do the join. To clean the data, add another column in table2 datatable called “ID” (or whatever you want to call it). For each row in table2, assign row.item(“ID”) = Regex.Match(row.item(“Text1”).ToString,“\d+”).Value

Now you have should have a matching identifier between the two tables.

Next, use the Join Data Tables activity and use the join wizard. put in table1 and table2 and output to a new datatable (i’ll call OutputTable). It doesn’t matter which input goes where because you want to do an Inner join type. Then change the bottom portion so table1 “ID” = table2 “ID”

Now you have a joined table. However, for some reason the join activity includes the joined field in both tables, so you’ll want to delete the extra field before writing to excel. Use the Remove Data Column activity to delete the newly created “ID_1” field from OutputTable.

Finally, write it back into excel using the Write Range activity & you’re all done!

1 Like
7

¿How works this function?

assign row.item(“ID”) = Regex.Match(row.item(0).ToString,"\d+").Value

I dont get value in “ID”

EDIT:

Forget it, i understood, but what happens if my value exists in the second column? how i will know it?

8

@inf_L You are right, as written it is only checking item(0) which is the first column. If it is the first or the second column, then you should use an if statement to check the first column, then the second column. It would be as follows

assign row.item("ID") = If(Regex.IsMatch(row.item(0).ToString,"\d+"),Regex.Match(row.item(0).ToString,"\d+"),Regex.Match(row.item(1).ToString,"\d+"))

This first checks to see if it finds a regex match in the first column. If it finds a match, it assigns that string as the ID. If it doesn’t, it will assign the second column match as the ID. Note that this solution assumes that your match will be found. If it can’t find a match in either column, it will through an error (can be handled through a Try-Catch)

9

@inf_L I think I misunderstood. The solution I provided won’t work. It will pull out any numbers it finds, so in your example from the first post it would not work for the second row. It would think the ID of table2 = " 032" instead of “11”.

I am not sure how to write a linq query to do what you’re asking unfortunately. I’m sure it could be done but I’m just not sure how. I would probably resort to iterating through each row and matching with nested ifs instead. So it would instead be something like this:

Assign rowIndex = 0
For each row in table2
  Assign IDNum = Table2.Rows(rowIndex).Item("ID").ToString
  Assign TempNum1 = Regex.Match(row.item(0).ToString,"\d+").Value
  If TempNum1 = IDNum Then
    Assign row.item("ID") = TempNum1
  Else Assign TempNum2 = Regex.Match(row.item(1).ToString,"\d+").Value
    If TempNum2 = IDNum Then
      Assign row.item("ID") = TempNum2
    Else Assign TempNum3 = Regex.Match(row.item(2).ToString,"\d+").Value
      If TempNum3 = IDNum Then
        Assign row.item("ID") = TempNum3
      End if
    End if 
  End if
Assign rowIndex = rowIndex + 1
Next row

You’d probably want to put some error handling on TempNum1,2, and 3 so if it doesn’t find a Regex match it will assign it to string.empty