Data Preparation
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
Solved! Go to Solution.
- Labels:
- Datasets
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
My question was, if Type =B and if sum of any combination of Type A +type B = 0 then in comments column for Type B , I should have comment as offset entry
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you for the help.
Input>>
Account | Type | Value |
1000 | A | 20 |
2000 | B | -30 |
3000 | A | 10 |
4000 | A | 10 |
6000 | B | 30 |
5000 | B | -10 |
Output>>
Account | Type | Value | Comment |
1000 | A | 20 | |
2000 | B | -30 | Offset |
3000 | A | 10 | |
4000 | A | 10 | |
6000 | B | 30 | |
5000 | B | -10 | Offset |
The logic doesn't work if we have the above input. Can you please provide your feedback?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I think you'll need to provide some more requirements. How are we grouping the totals of A & B? In this case doing total of A minus total of B does not = 0, so nothing is flagged. Its not clear to me how we would systematically get to the answer you're looking for. If you're doing it in excel now can you share the formulas you're using?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
