I am able to do simple builds but this one is more complex, to me.
Using ReFrameWork > Aggregate by Group > Trying to move the credit (in this example row 1 $-51) to the oldest debit UNIT DTE (row 2).
If row 2 only had a debit of $50 then I need the remaining $1 moved to the next oldest debit UNIT DTE (row 3) until the credit is zero. Then it can move on to the next group.
Thanks for your help!
welcome to forum
i am not cleared with your question
can u show how ur output should look like?
Thank you for replying. The blue columns in the example below is what I’m hoping the output can look like. I am trying to move the credit to the oldest unit with a debit. Hope that makes sense.
just giving some starter help:
Unique Column Names are required (working with datatable)
- The col names fro Blue block requires to be unique e.g. with a name like UNIT2, UNIT DTE2
As mentioned starting with grouping the data will help:
left side: Groups - Datatype: List(Of List(Of Datarow))
(From d in dtData.AsEnumerable Group d by k=d("ACCT").toString.Trim into grp=Group Select grp.toList).toList
the outer list represents the groups, the inner lists represents the different group members
within a for each we can iterate over the groups:
For each acitivity: grp (item)- Groups (Values), TypeArgument: List(Of DataRow)
inside the for each we could do following:
order the group by date:
- grpo| List(Of DataRow) = grp.OrderBy(Function (x) CDate(x(“UNIT DTE”).toString.Trim)).toList
transfer the values from the different rows of grpo to each other e.g.
- grpo(0)(“AMOUNT”) = grpo(1)(“UNIT BAL”)
Maybe some adoptions are to do e.g. other date parsing method. However just do a start and let us know your feedback and open questions
Thank you so much for your reply. I appreciate you breaking it down and making it simple to understand. I have a question about the grouping. Since I’m in ReFramework building out this process, can I use the activity Group by Aggregation (see below) instead of grouping the data with an assign activity?
Would you mind helping me with this silly issue, I am having a hard time with the datatype: List(Of List(Of Datarow))
Is it: System.Collections.Generic.List
After looking at the Group by Aggregation, I realize this won’t work for what I’m trying to accomplish. So I am back to square one with grouping and then transferring the values from different rows.
have a look here:
click Ok, Ok, Ok and it will look like this:
Also feel free to have a look here: