Multi Row Formula

[JVM15]

Hi I am not sure if I am using the correct tool or if this is even possible.

I have a clients AP data and the 'Amount' field has offsetting entries for AP, ex: (-18162), 18162, and also has the corresponding Asset account debit, sometimes broken out into multiple accounts, there is also the occasional 'Purchase Variance' account that makes up differences. Below is one example

API_OBJ_ID	ACCOUNT_DESC	ITEM_AMOUNT	INVOICE	PO_NUMBER
100008291	A/P Trade Unmatched-Inv	1360.8	99015A	8509186
100008291	A/P Trade Unmatched-Inv	-1360.8	99015A	8509186
100008291	Domestic Purchase Variance	136.08	99015A	8509186
100008291	A/P Trade Unmatched-Inv	1224.72	99015A	8509186

I want to create a 'Net Amount Field' that will Reference the 'API_OBJ_ID' batch field and group transactions together so that I can roll up the total to one line like so

API_OBJ_ID	ACCOUNT_DESC	NET AMOUNT	INVOICE	PO_NUMBER
100008291	A/P Trade Unmatched-Inv	1360.8	99015A	8509186

I thought to use the Multi-Row Formula tool with the following expression,

IF IsNull([Row-1:Amount]) THEN [Amount]
ELSE [Row-1:Net Amount] + [Amount] ENDIF

This took care of the offsetting entries but still leaves two entries for the same amount on the instances that there are just three accounts

and when the Variance account is present then the 'Net Amount' reflects that balance.

I then tried to reference based on a range using the 'API_OBJ_ID' but this expression showed as parse error.

IF ([Row-1:API_OBJ_ID] = [Row+1:API_OBJ_ID])
THEN ([Row-1:Net Amount] + [Ryan_Item Amount] + [Row+1:Ryan_Item Amount])

Any help/Ideas of what I can try would be greatly appreciated - even if it is not possible with Alteryx

[Qiu]

@JVM15 
If we can be sure that field name of occasional 'Purchase Variance' account, then this might work.

[JVM15]

Thanks for the Reply Qui,

Just so we are on the same page, you converted the 'Purchase Variance' account to output as 'A/P Trade' ?

is there a solution that does not omit/replace the account name? 

[Qiu]

@JVM15 
Also want to clarify, is below you need?

API_OBJ_ID	ACCOUNT_DESC	ITEM_AMOUNT	INVOICE	PO_NUMBER	NET AMOUNT
100008291	A/P Trade Unmatched-Inv	1360.8	99015A	8509186	1360.8
100008291	A/P Trade Unmatched-Inv	-1360.8	99015A	8509186	1360.8
100008291	Domestic Purchase Variance	136.08	99015A	8509186	1360.8
100008291	A/P Trade Unmatched-Inv	1224.72	99015A	8509186	1360.8

[JVM15]

No, the output from your solution is correct. Each invoice # should contain one line with the net amount reflecting the different accounts hit.

[Qiu]

@JVM15 
I am confused.

Can you provide sample of what you need as output?

[grazitti_sapna]

Hi @JVM15,

Is this what you are looking for? Do you want the different accounts to be shown in different rows?

If you want only 1 account to be shown and group on API_OBJ_ID alone then I think what @Qiu has given you as solution makes sense.