Netoff the Amount- basis the Transaction reference & Entry

Question

Hello,

I tried multiple times but unable to get fix this. I am looking for the solution.

Below is the Input file  & i need the output in new column "RESULT"

The criteria is

If the transaction ref  is same, Entry - PI & PO (only PI & PO can net off) , Total should net off to zero - then result should give me as FALSE

If it doesn't get net off the result should be FALSE

Transaction RefEntry Total 912796J42PO       (545,809)912796J42PI        545,809912796J42PO     (1,205,578)912796J42PI      1,205,578912796J42PO     (1,856,350)912796J42PI      1,856,350912796K57PO (149,842,847)912796K57PO (149,842,847)912796K57PO (149,842,847)912796K57PO (149,842,847)912796K57PO (149,842,847)912796K57PO (149,842,847)912796K57PO     (1,054,894)912796K57PI      1,054,894912796K57PO     (1,549,375)912796K57PI      1,549,375912796K57PO   (11,253,198)912796K57PI    11,253,198

The Required output

New Workflow3.yxmd

harmful_virus · Answer

HI Team,

I have a simple solution in excel in Colum "Check" . Can you please provide me the same excel formula in apteryx.

Please Note: Column A,B & C data will be provided by the User & i only need the "check "Column to be created & required the same formula in Alteryx.

Please help me with solutiions.

Excel solution.xlsx

mceleavey · Answer

@harmful_virus ,

given what I think you're trying to do, I've attached the workflow.

You were trying to net the PI against the PO (I'm assuming Purchase Order and Purchase Invoice, and so there are no invoices without the PO).

I first converted the data into a usable numeric format (negative is not required as this is denoted by the "Entry" field. To change it back simply use a formula), then gave it a grouped record ID, so I can sort it accordingly at the end. I've included the tool for you.

I then simply split the steam into two on the PO/PI type, and joined back together on the ref and value. This is assuming you are trying to net off the Invoice against the PO with the same value for the same reference.

Then it was a case of putting them back in the correct format and using the union tool to smoosh them back together:

The records that don't join represent those that do not net off against a PO, and the ones that do match need to be split to their component parts, that being PO and PI. These are then unioned back together and given a "FALSE" tag.

The Dynamic Rename is simply removing the "Right_" for the PI matched records.

This gives the following result:

I hope this helps,

M.

mceleavey_net.yxzp

mceleavey · Answer

Hi @harmful_virus ,

Must it net off in sequence? For example, in the desired output you have PO values prior to a PI value, but the PI value is netted off against the last PO in the sequence not the first.

Or, is it netted against the individual value of the PO?

M.