Hi Everyone,
I am new to Alteryx and i am trying to build a new workflow, current face an data cleaning issue and hopefully i can get some advice from here.
My input data source is a txt format file. And i would like to clean it up before i perform any manipulation. Ideally the below is the example of expected outcome.
| 1 | | USA COMPANY | | |
| REF-000-01 | | DAILY ACTIVITY SUMMARY | | |
| SEP. 20, 2020 | | INVESTMENT Account SYUMIA | | PAGE 1 |
| | Opening Balance | | | Closing Balance |
| | 12,345,678.90 | | | 12,345,678.90 |
| | | Credits | DEBITS | |
| Customer Activity | | 11,222.13 | 11,222.33 | |
| WITHHOLDING TAX | | | .10 | |
I tried few options, the 1st is fixed the width text file, but it would cause a problem because im not sure the data file width is fixed.
In 2nd option, i tried is using RegEX + Text to Columns. But the most difficult part is that at "WITHOLDING TAX" Row, ".10" will move to credits side because there is no amount on credit column.
Grateful for help...
