How to calculate sum on values based on certain rules
- 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,
In my table below, I am trying to solve this issue for the value of KEY1. So for all IDs belonging to the same KEY3 (for e.g. IDs:1,2,3 all belong to KEY3: A), the value of KEY1, where KEY2 = X, should be the sum of all KEY1 values belonging to the same KEY3 (for e.g. KEY1s:200,300,500 all belong to KEY3: A). I've added another table below to show what the result should look like.
ID | KEY1 | KEY2 | KEY3 |
1 | 200 | A | |
2 | 300 | X | A |
3 | 500 | A | |
4 | 800 | X | B |
5 | 100 | B | |
6 | 700 | C | |
7 | 400 | C | |
8 | 600 | X | C |
9 | 200 | D |
This is what the result should look like (change in KEY1 values, where KEY2 = X):
ID | KEY1 | KEY2 | KEY3 |
1 | 200 | A | |
2 | 1000 | X | A |
3 | 500 | A | |
4 | 900 | X | B |
5 | 100 | B | |
6 | 700 | C | |
7 | 400 | C | |
8 | 1700 | X | C |
9 | 200 | D |
Thank you! Much appreciated!
Solved! Go to Solution.
- Labels:
- Data Investigation
- Datasets
- Join
- Parse
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @Jairacha ,
nice and simple.
Just sum the KEY1 value grouped by KEY3 then join back to the isolated rows where KEY2=X. This replaces the KEY1 value for these records. Then union back together and sort accordingly.
Hope this helps,
M.