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
@JVM15
If we can be sure that field name of occasional 'Purchase Variance' account, then this might work.
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?
@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 |
No, the output from your solution is correct. Each invoice # should contain one line with the net amount reflecting the different accounts hit.
@JVM15
I am confused.
Can you provide sample of what you need as output?