Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

How to calculate sum on values based on certain rules

Jairacha
7 - Meteor

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.

 

IDKEY1KEY2KEY3
1200 A
2300XA
3500 A
4800XB
5100 B
6700 C
7400 C
8600XC
9200 D

 

This is what the result should look like (change in KEY1 values, where KEY2 = X):

 

IDKEY1KEY2KEY3
1200 A
21000XA
3500 A
4900XB
5100 B
6700 C
7400 C
81700XC
9200 D

 

Thank you! Much appreciated!

1 REPLY 1
mceleavey
17 - Castor
17 - Castor

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.

 

mceleavey_0-1636734020043.png

mceleavey_1-1636734036937.png

 

Hope this helps,

 

M.

 

 



Bulien

Labels
Top Solution Authors