Hello. Thanks in advance for any ideas here. I am trying to convert a python based process to a alteryx based process.
The following is a table with in col1 ledger numbers and summation lines for those numbers (col2 is an amount, col3 indicates a summation level. I want to be able to "tag" the ledger numbers with the summation lines. I dont know in advance how many ledger numbers appear before a summation level is reported. As you can see, the summation levels are also "nested".
Each of these files (1 by entity) can have different accounts, and therefore some summation levels might not appear in some files.
| col1 | col2 | col3 |
| 112690 | 0 | |
| 201223 | 2 | |
| 202440 | 1 | |
| TOTAL CASH | 3 | 4 |
| TOTAL CASH AND CASH EQUIVALENTS | 3 | 3 |
| | | |
| 12101000 | 5 | |
| 12101010 | 4 | |
| TOTAL ACCOUNT AND NOTE REC. TRADE | 9 | 4 |
| TOTAL AR TRADE/DISC.ACCTS/BAD DEBTS | 9 | 3 |
| | | |
| NEXT SUMMATION LEVEL | 12 | |
In python I am reading the rows until col1 is text, then, to the rows I have read so far, I tag my input with the text. Looking at the summation level, I know if I need to add a tag or not.
In order to facilitate data processing down stream, this is the format that I am looking for.
| col1 | col2 | col3 |
| 112690 | 0 | TOTAL CASH |
| 201223 | 2 | TOTAL CASH |
| 202440 | 1 | TOTAL CASH |
| TOTAL CASH | 3 | TOTAL CASH AND CASH EQUIVALENTS |
| TOTAL CASH AND CASH EQUIVALENTS | 3 | NEXT SUMMATION LEVEL |
| | | |
| 12101000 | 5 | TOTAL ACCOUNT AND NOTE REC. TRADE |
| 12101010 | 4 | TOTAL ACCOUNT AND NOTE REC. TRADE |
| TOTAL ACCOUNT AND NOTE REC. TRADE | 9 | TOTAL AR TRADE/DISC.ACCTS/BAD DEBTS |
| TOTAL AR TRADE/DISC.ACCTS/BAD DEBTS | 9 | NEXT SUMMATION LEVEL |
| | | |
| NEXT SUMMATION LEVEL | 12 | NEXT SUMMATION LEVEL +1 |
In the end I should end up with a linked chain of summation levels.
Any suggestions are greatly appreciated.
