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

Subtract one row from another based on a common ID and different cost categories

hellyars
13 - Pulsar

Here is my basic problem.   An Item can have multiple rows if it has multiple Cost Types.  

 

I want to setup a formula that finds those rose that have multiple Cost Types and subtract A from B and replace the amount for A with the result or create a new row (call it A-B) and get rid of the original A and B rows for an item.  I want to keep all As that don't have a corresponding B and all Cs.  

 

What would the formula (or multi-field formula) look like?

 

Thank you for your assistance.  

 

ItemCost TypeAmount
99999A235253
10001A32423
10001B243523
10001C3235
54321A154325
6 REPLIES 6
john_miller9
11 - Bolide

Here's one way to solve this

 

Subtract Rows Solution.PNGSubtract Rows Output.PNG

 

derekbelyea
12 - Quasar

 

Here is an alternative approach to solving this.

 

 

2018-02-11_00006.png

 

danrh
13 - Pulsar

This should get you there:

image.png

 

The Cross Tab puts the different cost types in different columns.  Then a simple formula produces the result you're looking for, a Transpose to put the data back in it's original form, and a filter to get rid of the excess rows.

 

Hope it helps!

hellyars
13 - Pulsar

Thank you @john_miller9. This almost works.  But, it still keeps the original A and B rows for Item 10001 (and all Items like 10001 when I try to scale it).

 

hellyars
13 - Pulsar

This works @danrh great.   But, I quickly ran into a problem.   My real dataset also has a column for Quantity.   Where Cost Type A will always have a value between 0 and N.   Cost Type B and C will always be 0.  Adding Quantity, trips up the approach.   But, I am working amending the Quantity column back to the final result of your solution.  

hellyars
13 - Pulsar

It worked.  Thank you.

 

I had to add a quantity field.  I filtered out Quantity, solved for A-B, and appended Quantity to the result.

 

Screen Shot 2018-02-12 at 11.13.46 AM.png

Labels