Help with LINQ for Conditional Value Assignment in DataTable

Help Activities
, ,
1

Hello everyone,

I’m working with a DataTable and I need assistance with a LINQ query to conditionally assign values in one of the columns.

I have the following two columns in my DataTable:

  • Purchase Document*
    Container

What I need is for each group of purchase documents, only the first document should have the value 1 in the Container column, while the subsequent purchase documents in that same group should have the value 0.

The image shows a table with columns labeled "Purchase Document" and "Container," listing three purchase documents (123, 456, 457) and their corresponding container statuses (1 or 0). (Captioned by AI)
The image shows a table with columns labeled "Purchase Document" and "Container," listing three purchase documents (123, 456, 457) and their corresponding container statuses (1 or 0). (Captioned by AI)1075×262 7.05 KB

2

Hi,

How about the following?

image
image572×465 15.3 KB

dt.AsEnumerable.ToList().ForEach(Sub(r) r("Container")=0)
dt.AsEnumerable.GroupBy(Function(r) r("Purchase Document").ToString).ToList().ForEach(Sub(g)
    g.First.Item("Container")=g.Count	
End Sub
)

Sample20241024-1.zip (9.3 KB)

Regards,

1 Like
3

Hi @Juan_Esteban_Valencia

Fallow the steps below. If I helped you, please mark it as solved.

Use “Assign” Activity to create a new filtered DataTable:

dt.AsEnumerable().GroupBy(Function(row) row(“Purchase Document”)).SelectMany(Function(g) g.Select(Function(row, index)
If index = 0 Then
row(“Container”) = 1
Else
row(“Container”) = 0
End If
Return row
End Function)).CopyToDataTable()

The code groups the rows by the “Purchase Document” column.

For each group, it sets the value 1 for the first row (index 0) and 0 for subsequent rows.

CopyToDataTable returns a new DataTable with the updated values.