Finding missing seq number for the rows with certain values

Question

Hi,

I hope I can be as specific as possible but I'm trying to find a missing Txn ID for declined payments. Each time the payment is declined all the information such as timestamp, payment card details, value etc come through but there is no transaction number present. This transaction number is present on the till so by finding a missing number I will be able to cross reference it. I have managed to find a way of generating missing numbers between rows but I can't work out how I can link it to the specific information. This is Pazien data so I hope someone is familiar and will be able to help. Thanks in advance.

Capture2.PNG

fieldew1 · Answer

Thanks peterr_h for the workflow. So I have managed to find the declined payments followed by the successful payment. All the declined payment which have not had any payment card re-entry I need to now find a missing transaction number for. All declined payments have all the details date/time, payment card but have not transaction ID which I could cross reference the till data with. Any help would be much appreciated.

peterr_h · Answer

Okay so from the sounds of it, you may need to use something that's more probabilistic than exact.

What I mean by this is that you will have a window between which transaction A (decline) and transaction B (successful) will take place. In my workflow I've given this 5 minutes, this is probably too long, and would need honing (if this solution would work for you). A full join also won't work where different banks are used, and the time won't be a join field, so this wouldn't work. Hence I've used the DateTimeDiff() feature to give a window of opportunity.

I've attached an example workflow; let me know if this does/doesn't help!

EDIT: Please note I just updated the attachment as I missed the amount match condition in the filter tool.

Declined_Transaction_Matching.yxmd

fieldew1 · Answer

Thanks for getting back to me.

I have just noticed that if the payment is declined and re-entered with a different card, it still records a declined transaction so I would need to exclude those first if I wanted to join by position. Otherwise I have more records than missing fields. I have attached an example. Someone had a declined payment when using Revolut, then re-entered payment using Santander UK what generated the transaction ID.

Capture2.PNG

Capture3.PNG