Linq to enter value on one column based on another column value of the same datatable

53410c195f64c51d98438bb2e · July 20, 2023, 10:25am

Hi Experts,
I hope you are doing well. I have one scenario where I have multiple column in a datatable, out of that one column name is “Image Name”, and another column name is “Business Unit”. “Business Unit” column is empty. And based on value of the “Image Name” column “Business Unit” column would be updated. For example if “Image Name” column contains “ati-”, then “Business Unit” column would be “ATI”. These two columns from the same datatable. The datatable contains more than 10,000 records. I want to do it in Linq query, I dont want to use any For each loop. Note: Total these type of conditions would be more than 10, so if you can help with more that one condition in the query, that would help me.

Please let me know if you have question regarding the same.

Regards,
Sourav

mkankatala · July 20, 2023, 10:27am

Hii @53410c195f64c51d98438bb2e

Share all the conditions then it will help us to write the linq.

53410c195f64c51d98438bb2e · July 20, 2023, 10:31am

Thanks for your reply @mkankatala

The conditions are from Excel, which I am converting into datatable,

it is on high level
“=If(COUNTIF(B”+int_RowCounter.ToString+“,”“ati-”“),”“ATI”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“nasm”“),”“NASM”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“activity-engines”“),”“ATI”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“assessments”“),”“ATI”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“acs-lem”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“lms-”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“aces”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“digital-fulfill”“),”“Clicksafety”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“clicksafety”“),”“Clicksafety”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“medhub”“),”“Medhub”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“examfx”“),”“ExamFX”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“fisdap”“),”“JBL”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“CDX”“),”“JBL”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“JBL”“),”“JBL”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“certification-services”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“sre”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“ua/”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“asc-”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“f5networks”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“istio”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“vault*”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“moodle3x”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“ascend-crm”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“mitch-test”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“assessment-delivery*”“),”“ATI”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“remote-proctoring”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“ehrt/”“),”“ATI”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“assessment-delivery”“),”“ATI”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“nha-api”“),”“NHA”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“ascend-sales-transaction”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“crm-userupdate-processor”“),”“NASM”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“bv/”“),”“Boardvitals”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“product-package-manager/”“),”“ATI”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“product-information-management/”“),”“ExamFx”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“corporate-applications/”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“kognito/”“),”“Kognito”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“csi-secrets-store/”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“elastic/”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“rancher/”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“newrelic/infra”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“nginx:latest”“),”“BV”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“gitlab-runner”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“kiali/”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“node-problem-detector/”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“prometheusmsteams/”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“aquasec/”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“kube”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“k8s”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“vault”“),”“ASC”“,”“Unknown Owner”“)))))))))))))))))))))))))))))))))))))))))))))))))”

Where B is “Image Name”. Please let me know would it be helpful?

mkankatala · July 20, 2023, 10:38am

Hi @53410c195f64c51d98438bb2e

 - Assign -> dt =  dt.AsEnumerable().Select(Sub(row)
                               If row.Field(Of String)("Image Name").Contains("ati-") Then
                                   row.SetField("Business Unit", "ATI")
                               ElseIf row.Field(Of String)("Image Name").Contains("xyz-") Then
                                   row.SetField("Business Unit", "XYZ")).CopytoDatatable.

I have written for the two conditions by this reference you can write all conditions.

Hope it helps!!

53410c195f64c51d98438bb2e · July 20, 2023, 10:41am

Thanks @mkankatala for the quick help, let me try this quickly, I will share the update shortly.

pravallikapaluri · July 20, 2023, 10:43am

Hi @53410c195f64c51d98438bb2e

dt.AsEnumerable().ToList().ForEach(Sub(row)
row(“Business Unit”) =
If(row.Field(Of String)(“Image Name”).Contains(“ati-”), “ATI”,
If(row.Field(Of String)(“Image Name”).Contains(“nasm”), “NASM”,
If(row.Field(Of String)(“Image Name”).Contains(“activity-engines”), “ATI”,
If(row.Field(Of String)(“Image Name”).Contains(“assessments”), “ATI”,
If(row.Field(Of String)(“Image Name”).Contains(“acs-lem”), “ASC”,
If(row.Field(Of String)(“Image Name”).Contains(“lms-”), “ASC”,
If(row.Field(Of String)(“Image Name”).Contains(“aces”), “ASC”,
If(row.Field(Of String)(“Image Name”).Contains(“digital-fulfill”), “Clicksafety”,
If(row.Field(Of String)(“Image Name”).Contains(“clicksafety”), “Clicksafety”,
If(row.Field(Of String)(“Image Name”).Contains(“medhub”), “Medhub”,
If(row.Field(Of String)(“Image Name”).Contains(“examfx”), “ExamFX”,
If(row.Field(Of String)(“Image Name”).Contains(“fisdap”), “JBL”,
If(row.Field(Of String)(“Image Name”).Contains(“CDX”), “JBL”,
If(row.Field(Of String)(“Image Name”).Contains(“JBL”), “JBL”,
If(row.Field(Of String)(“Image Name”).Contains(“certification-services”), “ASC”,
If(row.Field(Of String)(“Image Name”).Contains(“sre”), “ASC”,
If(row.Field(Of String)(“Image Name”).Contains(“ua/”), “ASC”,
If(row.Field(Of String)(“Image Name”).Contains(“asc-”), “ASC”,
If(row.Field(Of String)(“Image Name”).Contains(“f5networks”), “ASC”,
If(row.Field(Of String)(“Image Name”).Contains(“istio”), “ASC”,
If(row.Field(Of String)(“Image Name”).Contains(“vault”), “ASC”,
If(row.Field(Of String)(“Image Name”).Contains(“moodle3x”), “ASC”,
If(row.Field(Of String)(“Image Name”).Contains(“ascend-crm”), “ASC”,
If(row.Field(Of String)(“Image Name”).Contains(“mitch-test”), “ASC”,
If(row.Field(Of String)(“Image Name”).Contains(“assessment-delivery”), “ATI”,
If(row.Field(Of String)(“Image Name”).Contains(“remote-proctoring”), “ASC”,
If(row.Field(Of String)(“Image Name”).Contains(“ehrt/”), “ATI”,
If(row.Field(Of String)(“Image Name”).Contains(“nha-api”), “NHA”,
If(row.Field(Of String)(“Image Name”).Contains(“ascend-sales-transaction”), “ASC”,
If(row.Field(Of String)(“Image Name”).Contains(“crm-userupdate-processor”), “NASM”,
If(row.Field(Of String)(“Image Name”).Contains(“bv/”), “Boardvitals”,
If(row.Field(Of String)(“Image Name”).Contains(“product-package-manager/”), “ATI”,
If(row.Field(Of String)(“Image Name”).Contains(“product-information-management/”), “ExamFx”,
If(row.Field(Of String)(“Image Name”).Contains(“corporate-applications/”), “ASC”,
If(row.Field(Of String)(“Image Name”).Contains(“kognito/”), “Kognito”,
If(row.Field(Of String)(“Image Name”).Contains(“csi-secrets-store/”), “ASC”,
If(row.Field(Of String)(“Image Name”).Contains(“elastic/”), “ASC”,
If(row.Field(Of String)(“Image Name”).Contains(“rancher/”), “ASC”,
If(row.Field(Of String)(“Image Name”).Contains(“newrelic/infra”), “ASC”,
If(row.Field(Of String)(“Image Name”).Contains(“nginx:latest”), “BV”,
If(row.Field(Of String)(“Image Name”).Contains(“gitlab-runner”), “ASC”,
If(row.Field(Of String)(“Image Name”).Contains(“kiali/”), “ASC”,
If(row.Field(Of String)(“Image Name”).Contains(“node-problem-detector/”), “ASC”,
If(row.Field(Of String)(“Image Name”).Contains(“prometheusmsteams/”), “ASC”,
If(row.Field(Of String)(“Image Name”).Contains(“aquasec/”), “ASC”,
If(row.Field(Of String)(“Image Name”).Contains(“kube”), “ASC”,
If(row.Field(Of String)(“Image Name”).Contains(“k8s”), “ASC”,
If(row.Field(Of String)(“Image Name”).Contains(“vault”), “ASC”,
“Unknown Owner”
))))))))))))))))))))))))))))))))))))))))))))
Next)

Hope it helps!!

supermanPunch · July 20, 2023, 10:56am

Hi @53410c195f64c51d98438bb2e ,

We would like to for you to confirm on the Logic or Formula provided as the Initial Post suggested logic and the logic in the Formula, does not match.

Let us know if the Formula is actually the right one or if there is a change in the logic.

53410c195f64c51d98438bb2e · July 20, 2023, 11:00am

The If condition I have shared that is the logic which is required to get in Linq. Please let me know if you need any further clarification.

53410c195f64c51d98438bb2e · July 20, 2023, 11:03am

I am getting some lambda expression error.

53410c195f64c51d98438bb2e · July 24, 2023, 8:46am

If any possible help, I am getting expression error every time. I cannot post the error somehow. Please let me know if anyone tried the query, as it is not working for me, I am working on VB.Net frame work, and my expected output would be on Datatable.

Actual if condition is
“=If(COUNTIF(B”+int_RowCounter.ToString+“,”“ati-”“),”“ATI”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“nasm”“),”“NASM”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“activity-engines”“),”“ATI”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“assessments”“),”“ATI”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“acs-lem”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“lms-”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“aces”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“digital-fulfill”“),”“Clicksafety”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“clicksafety”“),”“Clicksafety”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“medhub”“),”“Medhub”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“examfx”“),”“ExamFX”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“fisdap”“),”“JBL”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“CDX”“),”“JBL”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“JBL”“),”“JBL”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“certification-services”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“sre”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“ua/”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“asc-”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“f5networks”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“istio”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“vault*”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“moodle3x”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“ascend-crm”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“mitch-test”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“assessment-delivery*”“),”“ATI”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“remote-proctoring”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“ehrt/”“),”“ATI”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“assessment-delivery”“),”“ATI”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“nha-api”“),”“NHA”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“ascend-sales-transaction”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“crm-userupdate-processor”“),”“NASM”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“bv/”“),”“Boardvitals”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“product-package-manager/”“),”“ATI”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“product-information-management/”“),”“ExamFx”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“corporate-applications/”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“kognito/”“),”“Kognito”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“csi-secrets-store/”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“elastic/”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“rancher/”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“newrelic/infra”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“nginx:latest”“),”“BV”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“gitlab-runner”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“kiali/”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“node-problem-detector/”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“prometheusmsteams/”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“aquasec/”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“kube”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“k8s”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“vault”“),”“ASC”“,”“Unknown Owner”“)))))))))))))))))))))))))))))))))))))))))))))))))”

supermanPunch · July 24, 2023, 10:49am

@53410c195f64c51d98438bb2e ,

Is it possible to show us a Sample data and its Expected Output, It will erase a lot of confusions and we can get to the solution faster.

53410c195f64c51d98438bb2e · July 24, 2023, 10:56am

This is the sample data

Image Name	Business Unit
rancher/rancher-agent:v2.6.10	ASC
rancher/rancher-agent:v2.6.10	ASC
rancher/rancher-agent:v2.6.10	ASC
rancher/rancher-agent:v2.6.10	ASC
rancher/rancher-agent:v2.6.10	ASC
rancher/rancher-agent:v2.6.10	ASC
rancher/rancher-agent:v2.6.10	ASC
ati-/rancher-agent:v2.6.10	ATI

Based on image name column Business Unit column value would appear. For that currently doing on Excel using If condition.
expression is
“=If(COUNTIF(B”+int_RowCounter.ToString+“,”“ati-”“),”“ATI”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“nasm”“),”“NASM”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“activity-engines”“),”“ATI”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“assessments”“),”“ATI”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“acs-lem”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“lms-”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“aces”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“digital-fulfill”“),”“Clicksafety”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“clicksafety”“),”“Clicksafety”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“medhub”“),”“Medhub”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“examfx”“),”“ExamFX”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“fisdap”“),”“JBL”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“CDX”“),”“JBL”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“JBL”“),”“JBL”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“certification-services”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“sre”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“ua/”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“asc-”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“f5networks”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“istio”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“vault*”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“moodle3x”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“ascend-crm”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“mitch-test”“),”“ASC”“,l
If(COUNTIF(B”+int_RowCounter.ToString+“,”“assessment-delivery*”“),”“ATI”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“remote-proctoring”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“ehrt/”“),”“ATI”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“assessment-delivery”“),”“ATI”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“nha-api”“),”“NHA”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“ascend-sales-transaction”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“crm-userupdate-processor”“),”“NASM”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“bv/”“),”“Boardvitals”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“product-package-manager/”“),”“ATI”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“product-information-management/”“),”“ExamFx”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“corporate-applications/”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“kognito/”“),”“Kognito”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“csi-secrets-store/”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“elastic/”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“rancher/”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“newrelic/infra”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“nginx:latest”“),”“BV”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“gitlab-runner”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“kiali/”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“node-problem-detector/”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“prometheusmsteams/”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“aquasec/”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“kube”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“k8s”“),”“ASC”“,
If(COUNTIF(B”+int_RowCounter.ToString+“,”“vault”“),”“ASC”“,”“Unknown Owner”“)))))))))))))))))))))))))))))))))))))))))))))))))”

Need to do that in datatable, preferred approach would Linq If condition. I am using VB.Net framework. Expected output would be datatable.

ppr · July 24, 2023, 11:40am

we would setup a mapping table and convert it within a LookUp Dictionary
Here we would also add a key “Unknown Owner” with the value: Unknown Owner
dictLookUp

Assign Activity
dtFilled = dtOrig.Clone

Assign Activity:
dtFilled =

(From d in dtOrig.AsEnumerable
Let si = d("Image Name").toString.Trim
Let k = dictLookUp.Keys.Where(Function (x) si.Contains(x)).DefaultIfEmpty("Unknown Owner").First()
Let bu = dictLookUp(k)
Let ra = new Object(){si,bu}
Select r = dtFilled.Rows.Add(ra)).CopyToDataTable

supermanPunch · July 24, 2023, 12:00pm

@53410c195f64c51d98438bb2e ,

Could we maybe try for a Mapping Storage and then pick the Value based on the mapping present in the Storage.

For Example, we can prepare the Mapping Sheet in an Excel as Key and Value like below :

We can then use a Linq Expression to get the Mapping data value.

Reading the Input Data Excel sheet, and store it as a Datatable, say DT and
Mapping Table Sheet as MappingDT.
We can then use a For Each Row activity on DT as we have to update the column Business Unit in it. Combining with a Linq Expression in it to fetch the matching key and the value from the MappingDT according to the Image Name value in DT, we can then update the Column with the matched value, In the else part, you could provide the Default value.

Implementation :

dr_Array = MappingDT.AsEnumerable.Where(Function(x)CurrentRow("Image Name").ToString.ToLower.Contains(x("Key").ToString.ToLower)).ToArray

Here, dr_Array is a variable of type Array of DataRow.

The Expression present in Assign within If activity is as below :

CurrentRow("Business Unit") = dr_Array.First.Item("Value").ToString

Debug Visuals :

53410c195f64c51d98438bb2e · July 31, 2023, 2:24pm

Thanks for the help @ppr

But I am getting this error
21.10.6+Branch.support-v21.10.Sha.5cb5747f95eaecc8ba6a6256e82fd4d128d2c90e

Source: Assign

Message: Object reference not set to an instance of an object.

Exception Type: System.NullReferenceException

RemoteException wrapping System.NullReferenceException: Object reference not set to an instance of an object.
at lambda_method(Closure , VB$AnonymousType_02 ) at System.Linq.Enumerable.WhereSelectEnumerableIterator2.MoveNext()
at System.Linq.Enumerable.WhereSelectEnumerableIterator2.MoveNext() at System.Linq.Enumerable.WhereSelectEnumerableIterator2.MoveNext()
at System.Linq.Enumerable.WhereSelectEnumerableIterator2.MoveNext() at System.Data.DataTableExtensions.LoadTableFromEnumerable[T](IEnumerable1 source, DataTable table, Nullable1 options, FillErrorEventHandler errorHandler) at System.Data.DataTableExtensions.CopyToDataTable[T](IEnumerable1 source)
at lambda_method(Closure , ActivityContext )
at Microsoft.VisualBasic.Activities.VisualBasicValue1.Execute(CodeActivityContext context) at System.Activities.CodeActivity1.InternalExecuteInResolutionContext(CodeActivityContext context)
at System.Activities.Runtime.ActivityExecutor.ExecuteInResolutionContext[T](ActivityInstance parentInstance, Activity1 expressionActivity) at System.Activities.InArgument1.TryPopulateValue(LocationEnvironment targetEnvironment, ActivityInstance activityInstance, ActivityExecutor executor)
at System.Activities.RuntimeArgument.TryPopulateValue(LocationEnvironment targetEnvironment, ActivityInstance targetActivityInstance, ActivityExecutor executor, Object argumentValueOverride, Location resultLocation, Boolean skipFastPath)
at System.Activities.ActivityInstance.InternalTryPopulateArgumentValueOrScheduleExpression(RuntimeArgument argument, Int32 nextArgumentIndex, ActivityExecutor executor, IDictionary2 argumentValueOverrides, Location resultLocation, Boolean isDynamicUpdate) at System.Activities.ActivityInstance.ResolveArguments(ActivityExecutor executor, IDictionary2 argumentValueOverrides, Location resultLocation, Int32 startIndex)
at System.Activities.Runtime.ActivityExecutor.ExecuteActivityWorkItem.ExecuteBody(ActivityExecutor executor, BookmarkManager bookmarkManager, Location resultLocation)

ppr · July 31, 2023, 2:25pm

what was done in detail?

53410c195f64c51d98438bb2e · July 31, 2023, 2:28pm

After cloning the data table, I used this query in assign activity
(From d In dt_AquaInfo.AsEnumerable
Let si = d(“ImageName”).toString.Trim
Let k = dictLookUp.Keys.Where(Function (x) si.Contains(x)).DefaultIfEmpty(“Unknown Owner”).First()
Let bu = dictLookUp(k)
Let ra = New Object(){si,bu}
Select r = dt_Copy.Rows.Add(ra)).CopyToDataTable

source table is dt_AquaInfo and ImageName is the column to match.

ppr · July 31, 2023, 2:34pm

Are properly prepared
dictLookUp
dt_Copy

Especially the dictLookUp Dictionary?

Topic		Replies	Views
Linq Query to Update a column depending on the value of another column Studio	8	1815	September 1, 2023
Linq query for updating a value in a datatable based on a condition Studio studio , question , activities_panel	10	1378	September 23, 2022
Update data table using linq Help	11	14544	November 13, 2021
Replace column row value in datatable based on condition using linq query Studio studio , question , activities_panel	11	1297	April 11, 2023
I have one excel file in which there are 10 Columns and I need to check with a condition if it was true I need to update the values in the D column using linq query? Can anyone help..? Something Else feedback	8	3919	October 29, 2021

Most Active Users - Yesterday
ashokkarale
Yoichi
Anil_G
sonaliaggarwal47
sven.wullum1
Hiba_B
Vincent_Nuestro
Bot_Psychology
A_Learner
Sairam_RPA
More details...

Linq to enter value on one column based on another column value of the same datatable

Related topics