I have a PDF that I loaded into Alteryx and I am trying to transformed it into the desired results table. I am having a lot of difficulties when trying to transform it into my desired results because the data is unstructured. The tricky part is distinguishing between the withdrawal and deposits amounts because there are no delimiters between them and only spaces. Seems to be that withdrawal values are more left and deposit values are more towards the right.
Any help would be appreciated, thanks in advance!
Original PDF:
| Description | Withdrawals | Deposits | Balance |
CREDIT MEMO
123
GBL VI 123 | | 1000 | 100000 |
DEBIT MEMO
SETTLEMENT: 567
DATA CENTRE: 11 | 100.30 | | 200000 |
DEBIT MEMO SETTLEMENT: 0010
DATA CENTRE: 12 | 678.00 | | 200000 |
SERVICE CHARGE
AD-HOC PAYMENT | 24.00 | | 514701 |
Table when converting my pdf in Alteryx:
| Data |
CREDIT MEMO 1000 100000
123
GBL VI 123
DEBIT MEMO 100.30 200000
SETTLEMENT: 567
DATA CENTRE: 11
DEBIT MEMO 678.00 200000 SETTLEMENT: 0010
DATA CENTRE: 12 SERVICE CHARGE 24.00 514701
AD-HOC PAYMENT |
My desired results:
| 1 | 2 | 3 | withdrawals | deposits | Total |
| CREDIT MEMO | 123 | GBL VI 123 | | 1000 | 100000 |
| DEBIT MEMO | SETTLEMENT: 567 | DATA CENTRE: 11 | 100.30 | | 200000 |
| DEBIT MEMO | SETTLEMENT: 0010 | DATA CENTRE: 12 | 678.00 | | 200000 |
| SERVICE CHARGE | AD-HOC PAYMENT | | 24.00 | | 514701 |
