Compare and replace value in a data table

Help Studio
, ,
1

Hi,
I have two data tables DT1 and DT2,

DT1

image

DT2

image

I need a solution which compares, DT1(“country”).tostring.split(“_”)(2) ( i.e Australia) with DT2(“country”). if both are equal then I need to replace DT1(country) with DT2(actualcountry)

Expected output

DT1

image

Thanks in advance.

2

@Jnanesh_Ballal

Follow the steps

  1. Use for each row in datatable on dt1
  2. Use filter datatable activity inside loop and filter dt2 with currentrow("Country").ToString.Split({"_"},StringSplitOptions.None)(2)
  3. next use if condition and check filtereddt.rowcount>0
  4. On the then side use assign currentRow("Country") = filtereddt.Rows(0)("ActualCountry").ToString

Hope this helps

Cheers

1 Like
3

@Anil_G thank you,
can you provide the linq solution for this?

4

@Jnanesh_Ballal

To be honest there would not be any performance difference …but here it is

First clone dt1 dtoutput = dt1.Clone()

dtOutput = (From d In Dt1.AsEnumerable() Let val = if(dt2.AsEnumerable.Any(function(x) x("Country").ToString.Equals(d("Country").ToString.Split({"_"},StringSplitOptions.None)(2)),dt2.AsEnumerable.Where(function(x) x("Country").ToString.Equals(d("Country").ToString.Split({"_"},StringSplitOptions.None)(2)))(0)("ActualCountry"),d("Country").ToString) Let ra = New Object(){d("AcNo"),d("Date"),val,d("amount")} Select r = DTOutput.Rows.Add(ra)).CopyToDataTable()

Cheers

1 Like
5

@Anil_G
I am getting this error after using the linq
image

6

@Jnanesh_Ballal

Missed aa bracket recompiled it please check

(From d In Dt1.AsEnumerable() 
Let val = If(dt2.AsEnumerable.
	Any(Function(x) x("Country").ToString.Equals(d("Country").ToString.Split({"_"},StringSplitOptions.None)(2))),
	dt2.AsEnumerable.Where(Function(x) x("Country").ToString.Equals(d("Country").ToString.Split({"_"},StringSplitOptions.None)(2)))(0)("ActualCountry"),
	d("Country").ToString) 
	Let ra = New Object(){d("AcNo"),d("Date"),val,d("amount")} 
	Select r = dtoutput.Rows.Add(ra)).CopyToDataTable()

cheers

1 Like
7

Is there any other simpler linq solution for this?

8

@Jnanesh_Ballal

What do you mean by simpler?

First I gave you a simple solution only…but you wanted it like this…so please tell the actual requirement and explain what you need so that we can help you better

cheers

9

@Anil_G ,
The solution which you provided using for each worked, thanks for the solution.

1 Like
10

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