Identify Cancelled transaction

[PraveenX]

Hi All,

I have a Dataset (attached below) which contains various transactions. Some of these transaction subsequently get cancelled and a new transaction will take place instead of earlier one I need to identify those transaction which subsequently get cancelled. If we are doing it manually then everything of these transaction will agree, except for:

1) Proceeds Base,Amortized Cost Base,Security Gain/Loss Base,Currency Gain/Loss 988,Gain Crrcy ID,Loss Crrcy ID,Currency Gain/Loss on Interest,Total Gain Loss - these columns have opposite sign when compared to original transaction.

2) Quantity column - for few type of transaction the quantity would be same for others type of transaction Quantity has opposite sign.

3) Accounting Date - It might/might not be the same.

4) Transaction -  The word"CANCEL" will be there for the cancelled transactions.

We need to identify both the Cancelled transaction and the original transaction related to this only. 

Thanks and regards,

[RishiK]

@PraveenX I have started this off for you in the attached workflow. You will have to replace the Input file with your own.

[PraveenX]

Thanks a lot RishiK for taking this up. However, I could not open it. Unfortunately I am using 2019.3 version of Alteryx(which can't be upgraded due to restriction from my organisation), it seems you have built the solution in a upgraded version.

[Tyro_abc]

Hi @PraveenX 

Will you mean this?

[PraveenX]

@Tyro_abc I want to identify the original(against which cancelled transaction takes place) as well as cancelled transaction.

[RishiK · Accepted solution]

@PraveenX could you simply join the data with itself and then do some reconciliation:

[Tyro_abc · Accepted solution]

For this, you need to join the filtered result