Alteryx Designer Desktop Discussions

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

Sum certain field cells based off certain criteria of the row

jrgreen
7 - Meteor

Sorry if this question is either impossible or extremely easy to accomplish, I am still fairly new to Alteryx and can't seem to figure this one out. 

 

So I have a data set that consists of the following columns: Year, Account, Entity, and Amount. In my data set I have one entity that the amount for a certain account needs to be combined with another account and that row needs to be removed.

 

In short, I need some sort of formula like "If the account number is 56, take the amount from entity 5 and add it to the amount for entity 1 then delete the row for account 5"

 

Here is a screenshot to make life simpler. Thanks!

13 REPLIES 13
hydrogurl01
8 - Asteroid

This might be something a simple formula tool can solve. 

 

You can try adding a formula tool to create a New Entity Column and using an IF/AND statement to write something like:

 

New Column Name: New Entity

 

EDITED FORMULA:

IF [Account]="56" && [Entity]="5" THEN "1" ELSE [ENTITY] ENDIF

 

This formula will essentially rename all your fields with an Account number of 56 and Entities with a '5' into being an Entity '1'. You can then use a Summarize tool to view a Summarized Sum view of the Amounts for each entity.

jrgreen
7 - Meteor

I am getting a "Malformed If Statement" error on the bolded section of the equation: IIF([Account]="56" && [Entity]="5","1",[ENTITY])

hydrogurl01
8 - Asteroid

Apologies, just edited the formula I wrote in the reply. Also, is your Entity Field Type a Double or a V_String? If it is a Double, no need to put the quotes around the "5", you can leave it as just 5. You can also leave the "1" as just a 1 without the quotes if [Entity] is a double for you.

jrgreen
7 - Meteor

for the amount, it's double

hydrogurl01
8 - Asteroid

Perfect. Is the [Entity] a string then? And does the edited formula I posted above work?

jrgreen
7 - Meteor

Yes, the entity is a string. And I'm still getting an error. It says "Invalid type in operator ==."

hydrogurl01
8 - Asteroid

Hmm..is your [Account] field a String or is that a Double?

jrgreen
7 - Meteor

the account is "int32" 

hydrogurl01
8 - Asteroid

That's probably the issue here. In our formula we are asking it to look for an Account with a String equivalent since we have quotes around it. Try changing the [Account] type to a String prior to this formula and see if that works.

Labels