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.

Sum values in a column with condition

Ravi
7 - Meteor

Hello! I am looking for some help on summing value on column "Hours" if my values in column "Date" "Region" "ID" and "Person" matches. The below highlighted set of 3 rows I am looking to fix. My first criteria is for the date to match as the data reflects an erroneous entry made, then corrected with a negative entry and a correct entry for the same date with right comments. Any help is appreciated! 

RegionID PersonDateCommentsHours
APAC23456A12/9/2022In-Progress8
APAC23456C12/3/2022On Hold9
EMEA30981A12/2/2022Not Started3
LAD85109C12/1/2022Roadblock4
LAD85109C12/1/2022Not Started4
LAD85109C12/1/2022Not Started-4
NA34619E12/9/2022In-Progress4
NA34619A12/9/2022Completed10
NA34619A12/2/2022Completed12
APAC23456C12/5/2022Not Started4
NA34619D12/9/2022In-Progress3
EMEA30981E12/7/2022On Hold-10
EMEA30981E12/7/2022On Hold10
EMEA30981E12/7/2022Roadblock10
LAD85109B12/9/2022Completed23
NA34619A12/9/2022On Hold2
NA34619A12/9/2022On Hold-2
NA34619A12/9/2022Completed2
APAC23456C12/9/2022Not Started10
LAD85109B12/1/2022Not Started4
EMEA30981D12/7/2022On Hold10

 

Output I am looking for 

RegionID PersonDateCommentsHours
APAC23456A12/9/2022In-Progress8
APAC23456C12/3/2022On Hold9
EMEA30981A12/2/2022Not Started3
LAD85109C12/1/2022Roadblock4
NA34619E12/9/2022In-Progress4
NA34619A12/9/2022Completed10
NA34619A12/2/2022Completed12
APAC23456C12/5/2022Not Started4
NA34619D12/9/2022In-Progress3
EMEA30981E12/7/2022Roadblock10
LAD85109B12/9/2022Completed23
NA34619A12/9/2022Completed2
APAC23456C12/9/2022Not Started10
LAD85109B12/1/2022Not Started4
EMEA30981D12/7/2022On Hold10
7 REPLIES 7
PanPP
Alteryx Alumni (Retired)

Hi @Ravi 

 

I have uploaded a sample WF to summarize the hours by Date/Region/ID. I am not too sure what your desired output is.

 

Hope this helps.

DenisZ
11 - Bolide

Could you just not use the Sum formula for this, or am I missing something?.

 

DenisZ_0-1670582220514.png

 

 

 

Raj
16 - Nebula

just use the summarize tool with the fields "Date" "Region" "ID" and "Person" grouping by and sum "hours" .

then by removing "0" from hours you will get the output.

Ravi
7 - Meteor

@DenisZ  After sum I want to keep the row which is unique of the three in column "Comments".

Raj
16 - Nebula

Raj_0-1670583745306.png

 

Ravi
7 - Meteor

Thanks @Raj I liked what you did but I also have zeroes in my actual data. I leveraged your suggestion and made a tweak to filter all rows with value zeroes, then summarize all columns including "Comments". Finally removed zeroes and then bring back my filtered data which had zeroes.

DavidSkaife
13 - Pulsar

Hi @Ravi 

 

The summerise and Filter approach works to an extent, but the below fields end up grouping and summing as well:

 

DavidSkaife_0-1670585780249.png

 

The attached should deal with this issue

DavidSkaife_1-1670585817955.png

 

Labels
Top Solution Authors