GroupBy Datatable with multiple column values

Help Studio
, ,
1

I have to group by PatientMedicareNumber, startdateofservice and enddateofservice.

input is like this - Input

I have groupby only using patientmedicarenumber which is working fine. However if I want to include startdate and enddate the logic is not working as expected and showing null error.

in_dt_WPSFileInput.AsEnumerable().GroupBy(Function(r) New With{Key.Supplier = r("PatientMedicareNumber")}).[Select](Function(g) g.OrderBy(Function(r) r("PatientMedicareNumber")).First()).CopyToDataTable()

I want to have 7 groupby which have to generate result as 7 datatable as follows - output

Found some queries online and using this but this result same as the input -

(From d In in_dt_WPSFileInput
Group d By a1=d("PatientMedicareNumber").tostring.trim,a2=d("DateofServiceStart").tostring.trim,a3=d("DateofServiceEnd").tostring.trim Into grp=Group
Where grp.count>=1
Select grp.tolist).SelectMany(Function(x) x).copytodatatable()

how to groupby using multiple columns with same value match?

2

@Arun_Singh

Please try this…

  1. Use a for loop and add this in InArgument dt.AsEnumerable.GroupBy(function(x) x("PatientMedicareNumber").ToString + x("DateofServiceStart").ToString + x("DateofServiceEnd").ToString).ToDictionary(function(x) x.Key,function(x) x.CopyToDatatable).ToArray and change the type argument to Datatable
  2. Now inside the loop for each iteration currentitem will have each group of data as you require

Hope this helps

cheers

3

Hi,

Can you try the following sample?

image
image932×716 38.8 KB 

dict = dt.AsEnumerable.GroupBy(Function(r) Tuple.Create(r("PatientMedicareNumber").ToString,r("DateofServiceStart").ToString,r("DateofServiceEnd").ToString)).ToDictionary(Function(g) g.Key,Function(g) g.CopyToDataTable)

note: dict is Dictionary<Tuple<string,string,string>,DataTable> type

Sample20230324-1aL.zip (2.7 KB)

Regards,

4

Thanks @Yoichi. Hands down!

5

Hi @Arun_Singh ,

We see that in your mentioned Expression, we have a check of grouped rows greater than or equal to One, which would again be all the grouped rows, so that check can be eliminated.

Then, we are having the grouped rows in grp, but it was not converted to Datatable as from the Expected Output, we see that you would require the Grouped rows separtely, we would need to convert the grouped rows into a Datatable and get the list of Datatables.

The Modiifed Linq Expression would be :

(From d In in_dt_WPSFileInput
Group d By a1=d("PatientMedicareNumber").tostring.trim,a2=d("DateofServiceStart").tostring.trim,a3=d("DateofServiceEnd").tostring.trim Into grp=Group
Select grp.CopyToDatatable).ToList

Here, the above would return a List of Datatables as the Output.

We can then loop through this datatable list using a For Each Loop and use a Write Range activity to write the data to an Excel sheet. But Before writing the data we would also need to add an Extra Empty row using a Add Data Row activity. The Array Row Property can be assigned as {}.

The above configurations should return the Expected Output as you require.

6

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