How to extract group of data in payer column based on company code of duplicate values

Help Studio
, ,
1

I have a scenario which is having two columns in that required group of data at a time regarding the company code in excel.please find the below screenshot
image

2

Hi @Jyothi1

Can you share the expected output?

Regards

3
4

regarding every company code which showing ex:5002 regarding that company code i need to get as group

5

Hi @Jyothi1

=> Read Range Workbook
image
Output → dt

=> Use below syntax in Assign:
resultDt = (From row In dt.AsEnumerable() Group row By CompanyCode = row.Field(Of Double)("Company") Into Group Select result = New Object() {CompanyCode, String.Join(",", Group.Select(Function(r) r.Field(Of String)("Payer")))}) _ .Select(Function(x) dt.Clone().Rows.Add(x)).CopyToDataTable()

=> Write Range Workbook resultDt.
image

image
image1008×654 92.5 KB

data.xlsx (10.5 KB)

Regards

1 Like
6

exact which the query given i have written but not working throwing error

7

any idea why this expression is getting exception

8

Hi @Jyothi1

What is the exception you are getting? can you please share the screenshot of excel please.

Regards

9

IMG20240326181559
IMG202403261815591920×1440 246 KB

10

Hi @Jyothi1

I think you have made some changes in the code. please do not change the query and use the exact query given above.

Regards

11

i Havent changed anything except the variable

12

Hi @Jyothi1

If possible can you share the flow, I will check and let you know.

Regards

13

Hi @Jyothi1

Try this:
resultDt = (From row In dt.AsEnumerable() Group row By CompanyCode = row.Field(Of Double)("Company") Into Group Select result = New Object() {CompanyCode, String.Join(",", Group.Select(Function(r) r.Field(Of String)("Payer")))}).Select(Function(x) dt.Clone().Rows.Add(x)).CopyToDataTable()

Attached xaml file:
Sequence98.xaml (9.3 KB)

Regards

14

Issue marked
grafik

a correctly formatted LINQ Query Syntax will fix the issue and we can also enhance readability by

grafik
grafik840×179 35 KB

15

it is throwing the error again with
Assign: Unable to cast object of type ‘System.Double’ to type ‘System.String’.

16

It is not about the LINQ it is about the data

Have a check on the Data and check

  • all Company values - are there proper Numbers
  • all Payers values - are there expected payers

Give a try at:

(From row In dt.AsEnumerable() 
Group row By CompanyCode = row.Field(Of Double)("Company") Into grp=Group 
Let ra = New Object() { CompanyCode, String.Join(",", grp.Select(Function(r) r("Payer").toString.Trim)) }
Select res = dt.Clone().Rows.Add(ra)).CopyToDataTable()
17

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