Match two table and get result

Help Studio
, ,
#1

Hi All ,

I have two DT as follows;

DT1

|NAME| LIFE COVER|

|ABC | 100,000.00|
|DEF | 50,000.00|

DT2

|Life_Cover| LifePremium|
|15,000| 23|
|25,000| 38|
|50,000| 76|
|75,000| 113|
|110, 000| 151|

I want to match DT1 “Life Cover amount” with DT2 “Life_Cover” column data and get the lifePremium . But when there is no exact match related to DT1 life cover amount then it should capture the greater value from the DT2 Life_Cover column related to DT1

exaple as follow;

DT1 life cover = 100,000.00
should match the greater value from the DT2 Life_cover as 110,000.00

hope you understand my scenario and earliest response on this regard much appreciated.

#2

Hi @Duleepa_Krishan
Use below Linq query to achieve your usecase

// Compare the two data tables
var diffTable = table1.AsEnumerable().Except(table2.AsEnumerable(), DataRowComparer.Default)

// Get the maximum value from the resulting data table
var maxValue = diffTable.Max(row[“ColumnName”])

Regards,
Kaviyarasu N

#3

Hi,

Do you need the following table as a result?

image

If so,the following will work.

image
image738×692 31.2 KB 

dt1.AsEnumerable.Select(Function(r) dt1.Clone.LoadDataRow({r(0),r(1),dt2.AsEnumerable.Where(Function(r2) Double.Parse(r2("Life_Cover").ToString,System.Globalization.NumberStyles.Any)>=Double.Parse(r("Life Cover amount").ToString,System.Globalization.NumberStyles.Any)).OrderBy(Function(r2) Double.Parse(r2("Life_Cover").ToString,System.Globalization.NumberStyles.Any)).First().item("LifePremium")},False)).CopyToDataTable()

Sample20230109-4aL.zip (3.2 KB)

Regards,

#4

Hi @Duleepa_Krishan

Kindly refer below Workflow if you are comfortable.
Hope you get solution

BlankProce.zip (164.9 KB)

Thanks
VP