Grouping data and add comma

Hi Team,

I’ve datatable as below

ID Code Usage Plant Code Header text doc_number
36967 204596990 1.518562 1076 04/2021/ ADDITIVE USAGE 4962398035
36952 204584990 631.169375 1076 04/2021/ ADDITIVE USAGE 4962398035
36967 204596990 1.518562 1076 04/2021/ ADDITIVE USAGE 4962398038
36952 204584990 631.169375 1076 04/2021/ ADDITIVE USAGE 4962398038

I want datatable like this

ID Code Usage Plant Code Header text doc_number
36967 204596990 1.518562 1076 04/2021/ ADDITIVE USAGE 4962398035, 4962398038
36952 204584990 631.169375 1076 04/2021/ ADDITIVE USAGE 4962398035, 4962398038

can anyone please help? Thanks in advance

Hi @chiranjit.saha1 ,

You can take reference from below video.

2 Likes

Hi @ermanoj3101 ,
can you please share the xaml file

1 Like

Sorry but i don’t have xaml file, you can get code from video description.

1 Like

Hi,

Hope the following helps you.

img20210715-5

dtResult = dt.AsEnumerable.GroupBy(Function(r) Tuple.Create(r("ID").ToString,r("Code").ToString,r("Usage").ToString,r("Plant Code").ToString,r("Header text").ToString)).Select(Function(g) dt.Clone.LoadDataRow({g.key.item1,g.Key.item2,g.Key.item3,g.Key.item4,g.Key.item5,String.Join(",",g.Select(Function(r) r("doc_number").ToString)) },False)).CopyToDataTable

Regards,

3 Likes

Hi @Yoichi ,

what is the datatype of dt and dtResult?

Hi,

DataTable

FYI, I’ll attach sample files as the following.

Sample20210715-4.zip (9.0 KB)

Regards,

2 Likes

Hi @Yoichi ,

It came as this format… It doesn’t grouped two id’s into one, and second document number came like this (496305514,6) It should come (496305514,496305516).

can you please take below excel format as input and help?

ID SAP FP Code Add. Actual Usage Plant Code Header text document_number Status Posted to SAP ExceptionMessage
37882 204596990 4.8426 1067 06/2021/ ADDITIVE USAGE 4963055142
37879 204584990 694.355566 1067 06/2021/ ADDITIVE USAGE 4963055142
37880 204590990 509.801792 1067 06/2021/ ADDITIVE USAGE 4963055142
37849 204596990 1.415075 1076 06/2021/ ADDITIVE USAGE 4963055143
37831 204584990 651.387132 1076 06/2021/ ADDITIVE USAGE 4963055143
37839 204590990 221.197391 1076 06/2021/ ADDITIVE USAGE 4963055143
38004 204596990 7.317082 1083 06/2021/ ADDITIVE USAGE 4963055144
38002 204584990 1371.761261 1083 06/2021/ ADDITIVE USAGE 4963055144
38003 204590990 633.840303 1083 06/2021/ ADDITIVE USAGE 4963055144
37916 204045990 7.271874 2776 06/2021/ ADDITIVE USAGE
37919 204056990 915.44951 2776 06/2021/ ADDITIVE USAGE
37928 204053990 355.277587 2776 06/2021/ ADDITIVE USAGE
38522 204087990 336.152245 3377 06/2021/ ADDITIVE USAGE 4963055145
38517 204085990 2.627206 3377 06/2021/ ADDITIVE USAGE 4963055145
38529 204095990 98.900433 3377 06/2021/ ADDITIVE USAGE 4963055145
37882 204596990 4.8426 1067 06/2021/ ADDITIVE USAGE 496305514,6
37879 204584990 694.355566 1067 06/2021/ ADDITIVE USAGE 496305514,6
37880 204590990 509.801792 1067 06/2021/ ADDITIVE USAGE 496305514,6
37849 204596990 1.415075 1076 06/2021/ ADDITIVE USAGE 496305514,7
37831 204584990 651.387132 1076 06/2021/ ADDITIVE USAGE 496305514,7
37839 204590990 221.197391 1076 06/2021/ ADDITIVE USAGE 496305514,7
38004 204596990 7.317082 1083 06/2021/ ADDITIVE USAGE 496305514,8
38002 204584990 1371.761261 1083 06/2021/ ADDITIVE USAGE 496305514,8
38003 204590990 633.840303 1083 06/2021/ ADDITIVE USAGE 496305514,8
37916 204045990 7.271874 2776 06/2021/ ADDITIVE USAGE 496305514,9
37919 204056990 915.44951 2776 06/2021/ ADDITIVE USAGE 496305514,9
37928 204053990 355.277587 2776 06/2021/ ADDITIVE USAGE 496305514,9

Hi

How about the following?

dt.AsEnumerable.GroupBy(Function(r) Tuple.Create(r("ID").ToString,r("SAP FP Code").ToString,r("Add. Actual Usage").ToString,r("Plant Code").ToString,r("Header text").ToString)).Select(Function(g) dt.Clone.LoadDataRow({g.key.item1,g.Key.item2,g.Key.item3,g.Key.item4,g.Key.item5,String.Join(",",g.Select(Function(r) if(r("document_number").ToString.Contains(","),System.Text.RegularExpressions.Regex.Replace(r("document_number").ToString,"(\d+)(\d),(\d)",Function(m) m.Groups(1).Value+m.Groups(2).Value+","+m.Groups(1).Value+m.Groups(3).Value),r("document_number").ToString))) },False)).CopyToDataTable

Sample20210715-4v2.zip (17.8 KB)

Regards,

Hi @Yoichi ,
Thanks for looking into it.
so, It isn’t grouped by one ID… and I noticed document number is 10 digits, when it’s concatenated by comma only 9 digits coming.

and I need to add Status, Posted to SAP,Exception message coloum. can you please help.
Thanks again!

ID SAP FP Code Add. Actual Usage Plant Code Header text document_number Status Posted to SAP ExceptionMessage
37882 204596990 4.8426 1067 06/2021/ ADDITIVE USAGE 4963055152
37879 204584990 694.355566 1067 06/2021/ ADDITIVE USAGE 4963055152
37882 204596990 4.8426 1067 06/2021/ ADDITIVE USAGE 496305515,496305516
37879 204584990 694.355566 1067 06/2021/ ADDITIVE USAGE 496305515,496305516

Hi,

Sorry but I cannot get your requirement correctly.
Perhaps you should break down the problems and create a topic for each.

Regards,

Hi Yoichi,

sorry for the confusion. This is the main data table, where for one id, two 10 digit document numbers is generating. This table also contains Status, Posted to SAP and exception message field. ( you haven’t add this last three fields in tuple id create)

ID SAP FP Code Add. Actual Usage Plant Code Header text document_number Status Posted to SAP ExceptionMessage
39017 204596990 6.043488 1067 06/2021/ ADDITIVE USAGE 4963343833 Success TRUE
39003 204584990 653.514393 1067 06/2021/ ADDITIVE USAGE 4963343833 Success TRUE
39017 204596990 6.043488 1067 06/2021/ ADDITIVE USAGE 4963343836 Success TRUE
39003 204584990 653.514393 1067 06/2021/ ADDITIVE USAGE 4963343837 Success TRUE

this output datatable should looks like this - 10 digit document number1, (space) 10 digit document number2 ( and should be grouped by one id, not twice)

ID SAP FP Code Add. Actual Usage Plant Code Header text document_number Status Posted to SAP ExceptionMessage
39017 204596990 6.043488 1067 06/2021/ ADDITIVE USAGE 4963343833, 4963343836 Success TRUE
39003 204584990 653.514393 1067 06/2021/ ADDITIVE USAGE 4963343833, 4963343837 Success TRUE

Hope this helps!. Please let me know

Hi

Do you mean as the following?

Sample20210715-4v3.zip (26.0 KB)

Regards

Yes Yoichi. you’ve added status, posted to sap fileds.
problem is in that workflow-

  1. ID 37882 and 37879 coming twice it should come once
  2. Document number is not in right format. It should came as 10 digit document number1, (space) 10 digit document number2
ID SAP FP Code Add. Actual Usage Plant Code Header text document_number Status Posted to SAP
37882 204596990 4.8426 1067 06/2021/ ADDITIVE USAGE 4963055214 Success TRUE
37879 204584990 694.355566 1067 06/2021/ ADDITIVE USAGE 4963055214 Success TRUE
37882 204596990 4.8426 1067 06/2021/ ADDITIVE USAGE 496305521,8 Success TRUE
37879 204584990 694.355566 1067 06/2021/ ADDITIVE USAGE 496305521,8 Success TRUE

Hi,

Is the above input data?
Can you share expected output for it?

Regards,

Hi That’s the output data came by that workflow you’ve shared.
I’m sharing again i/p and o/p data

Input:

ID SAP FP Code Add. Actual Usage Plant Code Header text document_number Status Posted to SAP ExceptionMessage
39017 204596990 6.043488 1067 06/2021/ ADDITIVE USAGE 4963343833 Success TRUE
39003 204584990 653.514393 1067 06/2021/ ADDITIVE USAGE 4963343833 Success TRUE
39017 204596990 6.043488 1067 06/2021/ ADDITIVE USAGE 4963343836 Success TRUE
39003 204584990 653.514393 1067 06/2021/ ADDITIVE USAGE 4963343837 Success TRUE

Expected Output:

ID SAP FP Code Add. Actual Usage Plant Code Header text document_number Status Posted to SAP ExceptionMessage
39017 204596990 6.043488 1067 06/2021/ ADDITIVE USAGE 4963343833, 4963343836 Success TRUE
39003 204584990 653.514393 1067 06/2021/ ADDITIVE USAGE 4963343833, 4963343837 Success TRUE

Thanks

Hi,

There is input file (sample3.xlsx) in the above zip. Doesn’t this work in your environment?

or did you use other input data?

Regards,

Hi,

Actually that input files are generating in runtime. you can refer any of input files , which I’ve provided(It should contains that coloums). but o/p should come in above following manner

HI,

Can you share your data as xlsx or csv file? I cannot reproduce your issue from the above text data.

Regards,

Hi,
Please find xlsx file. It’s the input data
Data.xlsx (9.2 KB)