Linq query modification as match the data and update blank colum

Help Studio
1

Hi All

dt1=(From row In dt1.AsEnumerable()
Let A=row(“Column_A”)
Let B=If(dt2.AsEnumerable().Any(Function(r) r(“Master_A”).ToString = row(“Column_A”).ToString), “NIL”, “NA”)
Select dt1.Clone.Rows.Add(A,B)).CopyToDataTable()

  1. I have 2 data tables. One data table has receipting status column
  2. receipting status is updated with certain data however sometimes bot is not updating it properly

3)so in that condition I need to match 2 data tables with certain column name then where receipting status row is blank it should update it as NIL

Pls modify this above Linq query as I am not good in this.

Thanks
Taruna

2

Hi @taruna.mehra ,

Could you maybe provide us with the Sample Input data and it’s Expected Output data ?

The, Linq Query used, has two columns to be compared but it looks like from the Statement provided you would require to compare another column value, once the initial column comparison is done.

The Input and Expected Output data would allow us to infer some things and deduce the logic, which would help in providing suggestions/solutions faster.

3

The expected out out should be as below

Receipting status column
NIl
Nil
12345
NIL

So where the row is blank it should write Nil post matching the data of 2 columns of diff data tables

4

Hi @taruna.mehra

Try this

Dim resultTable As DataTable = dt1.Clone()

For Each row As DataRow In dt1.Rows
Dim columnAValue As Object = row(“Column_A”)
Dim receiptingStatusValue As Object = row(“Receipting_Status”)

' Check if the receipting status is blank or empty
If String.IsNullOrEmpty(receiptingStatusValue.ToString()) Then
    ' Search for a matching row in dt2
    Dim matchingRow As DataRow = dt2.AsEnumerable().FirstOrDefault(Function(r) r("Master_A").ToString() = columnAValue.ToString())

    ' Set the receipting status based on whether a match was found
    Dim receiptingStatus As Object = If(matchingRow IsNot Nothing, "NIL", "NA")

    ' Add a new row to the result table with the updated receipting status

Thanks,

1 Like
5

Hi @taruna.mehra

U can use this expression inside For each datarow activity instead of using old one.
step 1: drag foreach row in datarow activity

image
Step:2
drag Assign Activity and use this expression
Left side:

dt1.Rows(dt1.Rows.IndexOf(CurrentRow))("Column_BU")=

image

Right side:
image

If(dt2.AsEnumerable.where(Function(x) x("Column_J").ToString.Equals(CurrentRow("Column_A").ToString)).count>0,
	If(Not String.IsNullOrWhiteSpace(CurrentRow("Column_BU").tostring),CurrentRow("Column_BU").ToString,"NIL"),CurrentRow("Column_BU").ToString)

Thank you
VP

6

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