I’d appreciate some help on the following process:
I have an input DataTable where I have to group by columns Name, Day, Type and sum column First Sum for each group. The problem is I still need all the rows, because Document Number column contains distinct values used later as Transaction Items. I also need to subtract each First Sum from Total Sum, except for the last item/sum.
I’ve used String.Join with delimiter “|” to the cell rows where I need the distinct values, but I’m now stuck.
I’m attaching the excel sample. InputDataTable in Sheet1, desired OuputDataTable in Sheet2, and Sheet 3 has what I managed to do. Documents.xlsx (11.8 KB) GetData.xaml (18.0 KB)
That can be a simple approach to have a build datatable with one column dtFinal
use a for each row Loop and then concatenate all the elements in that row using concatenate or simply + then the concatenatd string can be added to the datatable dtFinal, as an array element (putting that value inside{...}using Add Data Row
I’ve added the array elements from the initial DataTable and now it looks like this(in Sheet 3):Documents.xlsx (11.7 KB)
I still need to subtract Total Sum from First Sum as in Sheet 2. These will all be inputs set as transaction queue items which I will use in a local app. This is where I’m stuck.
Ex. 1st Group inputs used
2020 123 - 11001.5 (total group sum)
2020 456 - 6001 (total group sum - 5,000.50, document number 2020 123’s first sum)
2018 125 - 4000.5 ( the subtracted new total sum above, 6001 - 2,000.50, document number 2020 456’s first sum)
I stop and move to the next group.
Perhaps I’ll leave them joined with the delimiters and do all these operations when I get the queue items and enter them into the local app. I was hoping to have these all set up before I add them to Orchestrator.
@Anduranauul
referring to your excel creates impression that the different Worksheets are looking like part results but not completely solving the case.
The best would you will share an Excel with a clear sample and description. The best would be 1 Worksheet named input, other Worksheet named output.
I’ve had a look on your post, it did help up to a point where I was no longer able to follow so I went my direction.
I need to:
1st Group By columns Name, Day, Type → Sum all the values in column First Sum for each group.
2nd Subtract values in column First Sum from Total Sum. This is for all groups but also for each item in a group. I tried explaining above. Column Total Sum in the OutputExcel shows what I need.
3rd In the output excel I still need to have all the initial rows and columns. I will upload every row to a Orchestrator queue, where I will use values in columns Document Number, First Sum, Second Sum and the new output column Total Sum.
I didn’t want to concatenate them, I just didn’t know how to group them without losing all the values in the grouped rows.
Hopefully this clarifies what I want. Thank you.
using the index output of second for each (midx ~ member index) we can subtract from total the sum of previous member, when it is not the first group member
@ppr
Peter, this worked wonderfully, did exactly what I wanted. It also works very well with more members in each group. Thank you, I appreciate it. Also your posts, I learned quite a bit lately from them, even though I rather failed miserably at this one.
Anil_G
ashokkarale
kouhei_yamaguchi
ppr
Nabin_Timalsina
sharazkm32
banksmegacorp
alexis.mendoza.rpa
m.takamiya
Honda