Finding missing seq number for the rows with certain values

[fieldew1]

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.

[aatalai]

Could you sort the missing order ID in ascending order and the other data based on time stamp, and then join based on field position ? would that help?

[peterr_h]

Could you use a join on multiple fields (the fields that are present) to then match to the transactions that weren't declined?

If these are in the same dataset, you could do a filter first with the condition IsNull([Order ID]), then take the T (filter) anchor to the L (join) and the F anchor to the R. If I'm understanding the data and question correctly. Anything that joins (J output anchor) will give you a successful Order ID with matching data for the transaction(s) without an Order ID

[fieldew1]

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.

[peterr_h]

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.

[fieldew1]

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.