How to move values from row to row

Hi,
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!
image

Hi @Meghan
welcome to forum

i am not cleared with your question

can u show how ur output should look like?

Hi Nived,
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.

@Meghan
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:
Assign Activity
left side: Groups - Datatype: List(Of List(Of Datarow))
Right side:

(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

Hi Peter,
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?
image

1 Like

@Meghan
give a try on it and in case of open questions get in touch with @balupad14 as he is the author of his package

1 Like

@ppr
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

Thank you.

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.

@Meghan
have a look here:

grafik
grafik


grafik

click Ok, Ok, Ok and it will look like this:
grafik

Also feel free to have a look here: