I have 2 tables without primary keys. I am trying to join them based on 7 different columns being equal to each other. There are more columns in dt2 than there are in dt1
So it’d be similar to:
Select dt1.*, dt2.*
Join dt2 on dt1.col1-7 = dt2.col1-7
Anyone have an idea how to do this in UiPath? I tried using merge table activity, but that just appended the rows (similar to union all) rather than joining the tables.
yes they do - I actually got the 7 by doing a group by function. I’m now trying to bring the results back to the original datatable but with the matching extra columns
dt1 has 9 columns. dt2 has 29 columns. 7 columns are shared between the two tables. I wanted to join on the dt1 and dt2 on those shared columns where dt1 = dt2
So the new dt would have 31 columns total (7 shared, 2 from dt1, 22 from dt2)
Understood, don’t you think you will end up having duplicate column names which is not allowed in DataTable?
Off the top of my head, you could do something like below liqn queries will give you 2 datatables. Then you can do nested for each and merge respective rows and pass it into a new DataTable.
(From a In dt1.AsEnumerable() Join b In dt2.AsEnumerable() On a("c1").ToString() Equals b("c1").ToString() and a("c2").ToString() Equals b("c2").ToString() and a("c3").ToString() Equals b("c3").ToString() and a("c4").ToString() Equals b("c4").ToString() and a("c5").ToString() Equals b("c5").ToString() and a("c6").ToString() Equals b("c6").ToString() and a("c7").ToString() Equals b("c7").ToString() select a}).CopyToDataTable
(From a In dt1.AsEnumerable() Join b In dt2.AsEnumerable() On a("c1").ToString() Equals b("c1").ToString() and a("c2").ToString() Equals b("c2").ToString() and a("c3").ToString() Equals b("c3").ToString() and a("c4").ToString() Equals b("c4").ToString() and a("c5").ToString() Equals b("c5").ToString() and a("c6").ToString() Equals b("c6").ToString() and a("c7").ToString() Equals b("c7").ToString() select b}).CopyToDataTable
I’m very new to linq, so I’m not sure. I know in SQL it wouldn’t be an issue as there wouldn’t be any of the same column names because I’m joining on any columns that have the same name.
In your examples I’m assuming “c1” “c2” etc are the column names?
And it seems like I could just do one of the From statements, right? I’m not sure what the string.join at the beginning is for, or the multiple From a in dt1… statements are for?
Yes, “c1”,“c2” are column names and you need to replace them with your columns. You have to use both Linq statements, 1st one returns rows from dt1 and 2nd one retruns rows from dt2. There is also a way to get both records using single query (select new {a,b}), but then the output will be IEnumerable(DataRow), it will be a bit more complicated to extract data.
There must be a better way, using String.Join I’m concatenating common rows from both the tables, this needs to be done within a nested for each loop of both data tables above. After concatenating you need to pass that into new datatable (using Add Data Row).
To avoid duplicate column names, build a database with 31 unique columns before passing the rows. I need to check if there is an easy way to concatenate data table columns (perhaps by cloning).
This is my idea, there should definitely be a better way. Hopefully it will point you in a direction until someone comes up with a better idea. Even I will think about it again. Also, I’m not sure what impact it will have on other data types. Good luck.