Help! Join two data tables where contains?

Help
, , ,
1

Hi,
I’m having trouble joining two data tables where the matching fields are entered in comma separated text.
For example:

Dt1:
Name | Area
Adam | London
Adam | Manchester
Lucy | Liverpool

DT2
Name | Areas Covered | Job count
Adam |London, Manchester | 5
Lucy | Liverpool | 8

I’ve tried the UiPath Join activity, but because the field contains other data, it doesn’t match.
I’ve also tried a linq enumerable, but that doesn’t work either as UiPath throws an error when i change ‘Equals’ to contains:
(From DT1 In dt1.AsEnumerable() Join DT2 In dt2.AsEnumerable() On DT1(1).ToString() contains(DT2(1).ToString()) Select DT2).ToArray().CopyToDatatable

The error shown is ‘Equals Expected’

Is there any other way to do this?
Thanks

2

Hello

Does this help?

3

Hi,
Thanks for your reply & link.
The challenge i currently have is to perform an inner join, where two columns have similar values.
The video is more of an outer join, more so joining two data tables where there isn’t a common column.

Thanks though

1 Like
4

Hey PJ

There will be someone who has the answer I am sure on the forums.

If not, @AndersJensen is a datatable guru and will likely have a solution :smiley:

Cheers

Steve

5

Thanks @Steven_McKeering
In the second video theres a 1:1 match with the IDs - my challenge is where the data table cell contains a match, but it doesn’t equal it, so uipath treats it as false & disregards.

So the column Area = ‘London’ Whereas the column Areas Covered = ‘London, Manchester’
So the cell with ‘London’ doesn’t equal the cell with ‘London, Manchester’ as it contains the string rather than equalling the value.

1 Like
6

@PJ_17
give a try on:

(From DT1 In dt1.AsEnumerable()
From DT2 In dt2.AsEnumerable()
Where DT1(1).ToString().contains(DT2(1).ToString())
Select DT2).ToArray().CopyToDatatable

1 Like
7

Perfect!
Thank you very much

8

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.