Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Multiple Field Filter

janety0127
7 - Meteor

Hi,

I have a dataset with many rows. I ultimately add the columns to total the amounts across. I want to filter out the rows that contain zeroes across each column of each respective row, how can I accomplish this?

5 REPLIES 5
apathetichell
18 - Pollux

transpose with a record idand add a summation on the fields grouped by record id. filter where the sum is zero. Join on record to the original dataset. voila.

Qiu
20 - Arcturus
20 - Arcturus

@janety0127 

I would do the same as @apathetichell has suggested.

Just a quick sample on how to do it.

Capture3A.PNG

danilang
19 - Altair
19 - Altair

Hi @janety0127 

 

If your data set includes negative values you have to add in the extra step to handle the case where the values add up to zero, but the individual values are not zero.  In the following data set you want to remove row 4 but keep row 5 since it's values are not all zero

 

danilang_2-1627817529099.png

 

 

danilang_0-1627816911268.png

Add a RecordID, and transpose as @apathetichell suggested.  In the summarize tool add in the following operations

danilang_1-1627816992292.png

The extra Count Distinct operation counts the number of different values in the row.  Since the the only combination of values where the sum=0 and the values are all the same is when all the values are 0, add a filter with the following condition

 

[Sum_Value] != 0 or ([Sum_Value]=0 and [CountDistinct_Value] != 1)

 

 

This will pass through the values where the sum is not equal to 0 or the sum equals 0 and the values are not all 0.  Join on Record ID.  The L output gives the rows where the values are all 0.  The J output gives the rows that you want to keep.

 

Dan

 

 

 

ncrlelia
11 - Bolide

Hi @janety0127 ,

 

@danilang raised a very good point about the negative values. I'm a fan of the 'remove null rows' option in data cleansing tool hence I will suggest the below.

 

ncrlelia_0-1627908870258.png

 

First, use Multi-field formula to set all values = 0 to NULL() for all columns.

ncrlelia_3-1627909485602.png

 

Then, use Data Cleansing Tool to remove null rows and Replace Nulls with 0 (Numeric Fields).

ncrlelia_4-1627909508855.png

 

Lastly, the steps to obtain the sums - create Record ID, Transpose with Record ID as key and Sum the values by Record ID.

 

Hope this helps.

 

Cheers,

Lelia

janety0127
7 - Meteor

Thank you! These were all really helpful!

Labels