Selectively retrieving data from DataTable

[ashwin.ashok ((TheCodingTheory))]

I have an excel table as shown below:

There are two inputs, Location Code and Job Code.

Based on the Location Code, I have to first validate whether the Job Code exists in either of the rows with the same Location Code.

If the Job Code is present, then the Admin Sal Plan code from that particular row has to be retrieved, and if not, then the Admin Sal Plan code from the previous row(one where the job code is empty) has to be retrieved.

For example:
Location Code: 102, Job Code: 1123
This should retrieve Admin Sal Plan: SEQA

Location Code: 102, Job Code: 112342
This should retrieve Admin Sal Plan: SEQ

This is a little tricky, so I was hoping of anyone could help me out here, preferably with LINQ since the dataset is actually quite large(dealing with employee details here)

Thanks in advance!

Kind Regards,
Ashwin A.K

[ppr (Peter)]

Will have later a more detail look on it, but looks like a case helped by GroupBy

[HowTo] Overview on different options for grouping data and processing the groups

This HowTo introduces on the different options for grouping data from a datatable in order to process the grouped data. Introduction Grouping data and processing the grouped data is a common scenario e.g. when the grouped data is to aggregate like summing up, find maximum, get the average or concatening items. Lets have a look on following data: [grafik] A possible scenario could be: Create a report containing following information: the region code the sum of CaseCount per RegionCode t…

[poorna_nayak07 (Poornachandra M)]

Hi @ashwin.ashok,

Use below linq expression.
Also, remember you have to replace LocationVariable and JobVariable with required values

If(TestDT.AsEnumerable.Where(Function(x) LocationVariable.Equals(x("LocationCode").ToString.Trim)).Where(Function(y) (y("JobCode").ToString.Trim.Split(","c).Contains(JobVariable))).Count>0,TestDT.AsEnumerable.Where(Function(x) LocationVariable.Equals(x("LocationCode").ToString.Trim)).Where(Function(y) (y("JobCode").ToString.Trim.Split(","c).Contains(JobVariable))).First().Item("SalAdminPlan").ToString,TestDT.AsEnumerable.Where(Function(x) LocationVariable.Equals(x("LocationCode").ToString.Trim)).Where(Function(y) String.IsNullOrEmpty(y("JobCode").ToString.Trim)).First().Item("SalAdminPlan").tostring)

[ppr (Peter)]

as an alternate
Lets assume that strJOBCode / strLocationCode are string Variables which we use for filterings

(From d In dtData.AsEnumerable
Group d By k=d("Location Code").toString Into grp=Group
Where k.Equals(strLocationCode)
Let embr = grp.Where(Function (x) IsNothing(x("Job Code")) OrElse String.IsNullOrEmpty(x("Job Code").toString.Trim))
Let fmbr = grp.Where(Function (y) y("Job Code").ToString.Trim.Split(","c).Contains(strJobCode))
Let res = If(fmbr.Count > 0, fmbr, embr)
Let ret = If(res.Count > 0, res.First()("Job Code").toString, "UNDEFINED")
Select sap = ret).First()

it will be prepared for handling some covered requirements e.g. what is to do if code is not found but no row with empty Job Code exists (returns UNDEFINED)
and others

The group by approach can also be adopted to an Filter Approach

The reason of splitting the LINQ part result is to have a chance to return part result in case of LINQ Statement/Bug Analysis needs. So we can reduce the LINQ Blackbox for this

[ashwin.ashok ((TheCodingTheory))]

Hi @ppr,

As always, thanks you for this amazing solution

(From d In Dt_Data.AsEnumerable
Group d By k=d("Location Code").toString Into grp=Group
Where k.Equals(LocationCode)
Let embr = grp.Where(Function (x) IsNothing(x("Job Code")) OrElse String.IsNullOrEmpty(x("Job Code").toString.Trim))
Let fmbr = grp.Where(Function (y) y("Job Code").ToString.Trim.Split(","c).Contains(JobCode))
Let res = If(fmbr.Count > 0, fmbr, embr)
Let ret = If(res.Count > 0, res.First()("Sal Admin Plan").toString, "UNDEFINED")
Select sap = ret).First()

The Sal Admin Plan had to be retrieved, so that had to be changed but other than that the solution is amazing!

Kind Regards,
Ashwin A.K

[ashwin.ashok ((TheCodingTheory))]

Thank you @poorna_nayak07 I appreciate you taking the time to answer my query!