Join activity not working, possible to merge rows based on a single value?

Help Studio
1

I am working on a project where two separate data tables need to be joined together based on a common value, however for some reason some of the entries are not combining into one row. For example I have something like this in one table:

Username FirstName LastName Location
Username1 FirstName1 LastName1 Location1
Username2 FirstName2 LastName2 Location2
Username3 FirstName3 LastName3 Location3
Username4 FirstName4 LastName4 Location4
Username5 FirstName5 LastName5 Location5

And this in another:

Teams Username LastName FirstName
Team1 Username1 LastName1 FirstName1
Team2 Username2 LastName2 FirstName2
Team3 Username3 LastName3 FirstName3
Team4 Username4 LastName4 FirstName4

I join them using the username as the common value, and while some are joining together correctly some are stuck in different rows like so:

Teams Username LastName FirstName Username_1 LastName_1 FirstName_1 Location
Team1 Username1 LastName1 FirstName1 Username1 LastName1 FirstName1 Location1
Team2 Username2 LastName2 FirstName2
Username2 LastName2 FirstName2 Location2

From what I understand the ones with a common username should join together, however they are not.
So in short, what would be the easiest way to merge these remaining rows together using the common value?

2

@Garang

may be there are extra special characters or spaces which are causing the issue

you can first clean the data and then join

dt.AsEnumerable.ToList.ForEach(function(x) x("Username")=x("Username").ToString.Trim)

use this in invoke code send each datatble as in/out argument and then use the same line for both datatable and then join

this will clear any extra spaces

cheers

3

I checked both columns and couldn’t find any differences in usernames… no spaces at the beginning or end. I tried inputting that exact code in the invoke code but get an error (I also set the datatable as in/out datatable variable as equal to the dt in the query).

4

@Garang

not sure of what error you got

but coming to no difference to verify after join pause / use breakpoint and check the values from the locals panel

cheers

5

I actually used a vlookup of exports of the two data tables and confirmed the username is the same, the join activity is just not combing for some reason. Is it possible to use LINQ or something similar to combine the two data tables?

6

@Garang

Please check

Cheers