Hi,
I wonder if anyone can help?
I’m trying to transform payee fields for card payment operations imported from a qif file.
13/10/2023 CARD PAYMENT TO BURGER KING ON 12-05-2023, 15.11GBP 15.11
16/10/2023 CARD PAYMENT TO BURGER KING ON 13-10-2023, 10.08GBP 10.08
The date at the beginning on the line is the date the bank processed the transaction on next working day. It’s got actual date and and amount embedded in the payee string which will throw off the number payee entries. So I’m hope to extract the actual payee only in this case “BURGER KING”. So this will group all burger king to one entry on the payee page.
Once imported a payee entry will look like this:
CARD PAYMENT TO BURGER KING ON 12-05-2023, 15.11GBP
So I done a search with regexp ‘…’ on payee.
e.g.
regexp ‘CARD PAYMENT TO.*\bON \d±\d±\d+.*’
I got all the card payments and then I was hoping to extract the actual payee with:
CARD PAYMENT TO (.*)ON \d±\d±\d+,.*
This should give me “BURGER KING”.
I’m wonder if I can achieve this with “=regexpcapture(attribute,‘…’,…)” but a bit puzzled on how to use it. It’s got input for two values. I’ve put “CARD PAYMENT TO (.*)ON \d±\d±\d+,.*” in the first but not sure what to put in the second. If I execute it as is I get this error.
[ERR-5]: Process execution failed
[ERR-5]: Rule 2 failed
[ERR-10001]: near “)”: syntax error Unable to execute statement
[ERR-10001]: UPDATE v_operation_prop set t_PAYEE=REGEXPCAPTURE(‘CARD PAYMENT TO (.*)ON \d±\d±\d+,.', t_PAYEE, ) WHERE i_tmp=1 AND NOT(t_PAYEE=REGEXPCAPTURE('CARD PAYMENT TO (.)ON \d±\d±\d+,.*’, t_PAYEE, ))
Please let me know how I might be able to can get around this?
Many thanks.
Snorlax