Data Preparation

Ragini1

Hi All,

I am trying to build logic for the below scenario :

Input>>

Account	Type	Value
1000	A	20
2000	B	-30
3000	A	10
4000	A	10
5000	B	-10

Output>>

Account	Type	Value	Comments
1000	A	20
2000	B	-30	Offset
3000	A	10
4000	A	10
5000	B	-10	Offset

Can someone please help with this?

Regards

mattnason1

Here's how I would handle if I understand what you're trying to do.

Just a quick rundown of my process.

1) Sort records by dollar low to high.

2) Create a running total of the values.

3) Join on the dollar if they zero out.

4) Take the highest dollar value from Group A that gets offset.

5) Add the comment offset to every record from Group B that adds up to the Group A record.

I might be misunderstanding what you're trying.  But it might work for your purposes.  It did work for both examples.

Offset.yxmd