Linq query to filter column & update column

[nithya]

Hi Everyone,

I have to filter 2 column value & update another column.

I have to filter “G/L Account” Column & Text column & Update Category column accordingly.

If G/L Account = 1500011 & Text column start with HST then bot to update “Dealer: Vins” in Category Column

If G/L Account = 1500010 & Text column start with AST then bot to update “Dealer: Parts” in Category Column

Thanks in advance

[ppr (Peter Preuss)]

have a look here on the different options. Escpecially later when it is elaborated on updating a sub set
How to Update Data Column Values of a Data Table | Community Blog

We should keep in mind, that LINQ is serving well for filtering, but updating column values with LINQ can risk falling into Black-Boxing.

[mkankatala (Mahesh Kankatala)]

Hi @nithya

You are updating the Category column with the same value, could you provide the Correct data.

Change that part in the below LINQ Expression,

- Assign -> dt_Output = (From row In dt_Input
                         Let Account = row(0).ToString()
                         Let TextCol = row(1).ToString()
                         Let Aging = row(2)
                         Let Open = row(3)
                         Let Category = If(Account.Equals("1500011") AndAlso TextCol.Contains("HST"), "Dealer: Vins", If(Account.Equals("1500010") AndAlso TextCol.Contains("AST"), "Dealer Vins2", ""))
                         Select dt_Input.Clone().Rows.Add({Account, TextCol, Aging, Open, Category})
                               ).CopyToDataTable()

Hope it helps!!

[Ajay_Mishra (Ajay Mahendra Mishra)]

Hey @nithya

You can use below mentioned query, it is useful even if multiple columns will be added in between, it will add Category Column value atlast and keep values by default if G/L Account Column contains any other value than yours requirement.

- Assign -> dt_Output = (From row In dt_Input
                         Let Category = If(row("G/L Account").ToString.Trim.Equals("1500011") AndAlso row("Text").ToString.ToLower.Contains("hst"), "Dealer: Vins", If(row("G/L Account").ToString.Trim.Equals("1500010") AndAlso row("Text").ToString.ToLower.Contains("ast"), "Dealer Parts", row("Category").ToString))
                         Select dt_Input.Clone().Rows.Add(row.ItemArray.Take(row.ItemArray.Count-1).Append(Category).ToArray)
                               ).CopyToDataTable()

Regards,
Ajay Mishra