Compare two datatable based on common column if match found then update values from other datatable to specific column in first datatable values

[Mansi_Mhatre]

Hi,
I want to compare two datatables based on Emp-Id from both datatable and if match found then want to update “EmpName” and “role” column from Dt1 with matched column “Name” and “Role” from Dt2 else update columns as “NA” in Dt1.

For example:
Dt1-

EmpId	EmpDesignation	EmpGroup	EmpName	Role
1	HR	A
2	HR	B
3	TL	C
4	Dev	A
5	TL	K
6	HR	A
7	TL	C
8	HR	D
9	TL	A
10	Dev	B

DT2:

ID	Name	Role
1	ABC	L
4	XYZ	A
7	NNO	A
9	MMT	L

Required output:

EmpId	EmpDesignation	EmpGroup	EmpName	Role
1	HR	A	ABC	L
2	HR	B	NA	NA
3	TL	C	NA	NA
4	Dev	A	XYZ	A
5	TL	K	NA	NA
6	HR	A	NA	NA
7	TL	C	NNO	A
8	HR	D	NA	NA
9	TL	A	MMT	L
10	Dev	B	NA	NA

[PrankurJoshi]

Hi @Mansi_Mhatre

You can do below steps

1. Apply for each loop on DT1
2. Take filter datatable activity, filter based on ID and Role
3. If filter result has rows then update the DT1 columns by assign- activity
   Assign - CurrentRow(“ColumnName”)=DT_FilterResult.Rows(0)(“ColumnName”).ToString
4. After loops ends DT1 would be updated as per the requirement

Thanks,
prankur

[ppr (Peter Preuss)]

you can produce the output with:

• join datatable activity - left join
• LINQ: left join, Any check, Where filter Approach

[Mansi_Mhatre]

Sry but data can be more than 70000-80000. So looping is not idle for this scenario.

[Mansi_Mhatre]

Can you provide queries for both to check

[PrankurJoshi]

Hi @Mansi_Mhatre

You can try this code, drag invoke code activity select language as C#

DT2.AsEnumerable().ToList().ForEach(m=>{
DT1.AsEnumerable().Where(r=> m.Field<String>("ID")==r.Field<String>("EmpID")).ToList().ForEach(s=>{
	s.SetField<String>("EmpName",m.Field<String>("Name"));
	s.SetField<String>("EmpRole",m.Field<String>("Role"));
});

});

Thanks,
Prankur

[ppr (Peter Preuss)]

prepare an empty target datatable with build datatable, configure the cols:
EmpId EmpDesignation EmpGroup EmpName Role
use var name dtTarget for this

or clone dt1

Assign Activity
LHS: dtTarget
RHS

(From d1 In dt1.AsEnumerable
Group Join d2 In dt2.AsEnumerable 
On d1("EmpId").toString.Trim Equals d2("ID").toString.Trim  Into gj=Group
From g In gj.DefaultIfEmpty
Let ra2 = If(isNothing(g), New Object(){"NA","NA"}, g.ItemArray.Skip(1).toArray)
Let ra = d1.ItemArray.Take(3).Concat(ra2).ToArray
Select r = dtTarget.Rows.Add(ra)).CopyToDataTable

Kindly note: LINQ is written for the given datatable structure from above

[Ramya_K]

Hi @Mansi_Mhatre,

this LINQ query can be used to achieve your desired result.

Use assign activity for the below query.

(From a In dt1.AsEnumerable
Group Join b In dt2.AsEnumerable On a(0) Equals b(0) Into grp = Group
From g In grp.DefaultIfEmpty
Select row = {a(0), a(1) ,a(2),If(isNothing(g), “NA”, g(1)),If(isNothing(g), “NA”, g(2)) }
Select dtFinal.Rows.Add(row)).CopyToDataTable

Screenshot Reference :

Hope this helps. Happy Automation!!!

[Mansi_Mhatre]

I have one excel sheet with given data, and want to perform some activities without loop as it contains more than 40000 data and multiple columns also.
Sheet data:
EmpId EmpName EmpGroup EmpDesignation Remark
1 ABC A HR
2 bcd A TL
3 thg B Dev
4 uth D Dev
5 ikhf C HR
6 tppl B HR
7 artu B TL
8 vbnd N TL
9 okjd G PM
10 mnbd C HR
8 jkh N TL
5 kopt C HR

Activities need to perform without loop:

1. Remove duplicate rows on the basis of EmpId column i.e. if more than 1 EmpId found the only keep 1 row and remaining duplicate rows should be move to other sheet (" Duplicate values")

For given example:
Duplicate sheet will be-
8 jkh N TL

5 kopt D HR

2. Filter EmpGroup with value ‘D’ and update Remark as “group D”
   Note: data is more than 40000 and i want all data after filtering and updating not just filtered datatable.

Output:
EmpId EmpName EmpGroup EmpDesignation Remark

1 ABC A HR

2 bcd A TL
3 thg B Dev
4 uth D Dev group D
5 ikhf C HR
6 tppl B HR
7 artu B TL
8 vbnd N TL
9 okjd G PM
10 mnbd C HR

3. Then again for same datatable filter if EmpDesignation is ‘TL’ and
   EmpGroup ks not ‘N’ then update Remarks column as ‘group B’

Output:
EmpId EmpName EmpGroup EmpDesignation Remark
1 ABC A HR
2 bcd A TL group B
3 thg B Dev
4 uth D Dev group D
5 ikhf C HR
6 tppl B HR
7 artu B TL group B
8 vbnd N TL
9 okjd G PM
10 mnbd C HR

Then again check if EmpDesignation equals ‘Dev’ and Remark is blank then update Remark column as “Other employees”

EmpId EmpName EmpGroup EmpDesignation Remark
1 ABC A HR
2 bcd A TL group B
3 thg B Dev Other Employees
4 uth D Dev group D
5 ikhf C HR
6 tppl B HR
7 artu B TL group B
8 vbnd N TL
9 okjd G PM
10 mnbd C HR

I want to perform those actions without loop and with minimum datatables. This is just sample data for logic, data can be more than 40000 and with more than 20 columns.
Basically it should filter and update data but return whole datatable after output not just 1 datatable for 1 filter then perform other filter on that filtered which is wrong.

[Mansi_Mhatre]

Please find final output

[Sree_Krishnan_vellinezhi]

can you provide the linq query for –

i need to compare two datatables with same column for example-

DT1

name id
rajkumar
shahrukh
vijay
sreeram
guru

DT2

PatientName ID
Raj kumar
Sree ram V
Guru V
Vijay T

i need to match the name columns of both DT and if the name contains any one of the word in patient name then it should be matched and give the output in a new column as matched

[ppr (Peter Preuss)]

may we ask you top an new own topic for your case? Thanks

[Sree_Krishnan_vellinezhi]

@ppr

LINQ: left join, Any check, Where filter Approach

can you give that datarow comparer code

[ArchanaN (Archana)]

Hi Ramya, if we need to concatenate two column values and write how do we do? Example instead of a(0), I want to have a(0)= a(1)+a(2). And I want to add this condition for a(2) = =IF(AND(C2>50000,C2<59999,I2>6000),1,0). Can you please help with this? Thank you