Hi,
I have a dt which looks like the input shown below. I want to combine the values based on Region column and the column A which is SAP Mapping should then hold all the values with comma seperated.
i/p:
o/p:
Here is the file for your reference. Sheet1 is your input & Sheet2 is the output that needs to be generated.
Test.xlsx (11.1 KB)
We depend on the structure details and could do:
Assign Activity
dtResult | DataType: DataTable = dtOrig.Clone
Assign Activity
dtResult =
(From d in dtOrig.AsEnumerable
Group d by k=d("Region").toString.Trim.ToUpper into grp=Group
Let rgl = grp.Select(Function (g) g("SAP Mapping").toString.Trim).Distinct().OrderBy(Function (c) c)
Let sj = String.Join(",",rgl)
Let ra = grp.First().ItemArray.Skip(1).Prepend(sj).Cast(Of Object).toArray
Select r = dtResult.Rows.Add(ra)).CopyToDataTable
Well there can be other SAP Mapping as well. The logic you shared works but not if other SAP Mapping are there. For that I have added another column called Mapping Number and all the products for first iteration that needs to be clubbed are marked by 1 and then 2,3 and so on…
How can we modify the code in this case.
Like first filter by the Mapping number column and then apply your logic.
Sort of like above would be the input.
In general we would suggest to get familar with the different options of Grouping Data (LINQ, NoN-LINQ)
Adressing:
Yes, thats why we mentioned
A few parts are unclear of the modified description:
But in general we address it, by extending the grouping criteria e.g. with a second column used for group by:
(From d in dtOrig.AsEnumerable
Group d by k1=d("Region").toString.Trim.ToUpper,k2=d("Mapping Number").toString.Trim into grp=Group
Let rgl = grp.Select(Function (g) g("SAP Mapping").toString.Trim).Distinct().OrderBy(Function (c) c)
Let sj = String.Join(",",rgl)
Let ra = grp.First().ItemArray.Skip(1).Prepend(sj).Cast(Of Object).toArray
Select r = dtResult.Rows.Add(ra)).CopyToDataTable
Hi @ppr ,
Can you see my reply on below thread? There is one more column called Mapping Number with values like (1,2,3,4, and so on). I want to first find all the rows with same Mapping number and then apply your logic. Can we do multiple groupings?
What do you think of the below query?
(From d in dtOrig.AsEnumerable
Group d by k2=d(“Mapping Number”).ToString.Trim into grp2=Group
From subgrp In (From g in grp2
Group g by k1=g(“Region”).ToString.Trim.ToUpper into subgrp
Let rgl = subgrp.Select(Function (x) x(“SAP Mapping”).ToString.Trim).Distinct().OrderBy(Function (c) c)
Let sj = String.Join(“,”, rgl)
Let ra = subgrp.First().ItemArray.Skip(1).Prepend(sj).Cast(Of Object).ToArray
Select dtResult.Rows.Add(ra)).ToList()
Select subgrp).CopyToDataTable
as mentioned we doubted
Better to share with us where the output from the modified LINQ (with k1,k2) differs from the expected output.
we can:
But it looks to us, that your code will result to the same rows as above using the group by with 2 cols. Maybe you want to have it sorted on the Mapping Number like:
(From d in dtOrig.AsEnumerable
Group d by k1=d("Region").toString.Trim.ToUpper,k2=d("Mapping Number").toString.Trim into grp=Group
Let rgl = grp.Select(Function (g) g("SAP Mapping").toString.Trim).Distinct().OrderBy(Function (c) c)
Let sj = String.Join(",",rgl)
Let ra = grp.First().ItemArray.Skip(1).Prepend(sj).Cast(Of Object).toArray
Order by CInt(k2)
Select r = dtResult.Rows.Add(ra)).CopyToDataTable
Well your earlier modified logic worked. Just made some modifications. Thanks @ppr & others for sharing your solutions.
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.