Formula Pulling from Different Rows

[hannah1818]

Hi, 

We're trying to create a workflow where the amounts in the "Credit" column should be calculated by first identifying the subaccount and multiplying the debit amount in the first line by the respective rate. For instance if the subaccount is 220 then we want the formula to be =debit * 0.08 (1,750 *0.08) and if the sub account is 320 then we want the formula to be =debit * 0.05 (1,750 *0.05). Is there a way to pull amounts in the formula based on different row values.

The top line is from a different input file that we merged with the bottom 2 lines. Would you know how we go about doing this in Alteryx?

[ScottLewis]

Having a hard time understanding how the pictures relate to what you're trying to do so this might be way off. Multi-row formulas exist, but this doesn't actually seem like it requires one. More like a data organization issue to get the right rate on each line.

When we need to apply a rate, or really any value, based on a criteria existing in the input data, the pattern we use for that is based on a join.

You would create a table (or read one in and format it to get to) like this:

sub account           rate

220                          .05
320                          .08

Then you would join that with the data containing amounts using sub account as the key for the join (and deselecting sub account from the table side to avoid duplication.)

Then your formula is just

Amount * Rate. 

[binuacs]

@hannah1818 would you be able to provide some sample input file and expected output?