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.
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
Yoichi
(Yoichi)
July 15, 2021, 8:28am
5
Hi,
Hope the following helps you.
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?
Yoichi
(Yoichi)
July 15, 2021, 9:00am
7
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
Yoichi
(Yoichi)
July 15, 2021, 10:21am
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
Yoichi
(Yoichi)
July 15, 2021, 11:07pm
11
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
Yoichi
(Yoichi)
July 16, 2021, 9:02am
13
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-
ID 37882 and 37879 coming twice it should come once
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
Yoichi
(Yoichi)
July 16, 2021, 10:20am
15
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
Yoichi
(Yoichi)
July 16, 2021, 10:30am
17
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
Yoichi
(Yoichi)
July 16, 2021, 2:00pm
19
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)