Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Register for the upcoming Live Community Q&A Session - and don't forget to submit your questions for @DeanS regarding the future role of analytics here.
SOLVED

Sum certain field cells based off certain criteria of the row

Highlighted
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!

Highlighted
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.

Highlighted
7 - Meteor

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

Highlighted
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.

Highlighted
7 - Meteor

for the amount, it's double

Highlighted
8 - Asteroid

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

Highlighted
7 - Meteor

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

Highlighted
8 - Asteroid

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

Highlighted
7 - Meteor

the account is "int32" 

Highlighted
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