Forum Discussion
Trial balance trasformation
Hi Anonymous ,
Here's my solution.
1.Duplicate a table out, then keep half of the two tables, and unify the column names.
2.Append.
3.Group by.
4.Add custom columns.
5.Expand columns and remove the unneeded columns, then filter rows. Keep the row whose index is 1.
6.Add a custom column to get file name.
Here's the results.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community ChampionAnonymous provide sample data and expected output in a table format here.
Hi smpa01
i hope copying as table is fine.
thanks
Luca
INPUT
| Acc. ID | Acc. Descr | Debit | Acc. ID | Acc. Descr | Credit |
| 0301000 | CREDITI V/CLIENTI | 0601000 | DEBITI V/FORNITORI | ||
| CREDITI V/CLIENTI | 2.180.895,98 | DEBITI V/FORNITORI | 2.466.663,73 | ||
| 0400000 | ATTIVITA' A BREVE | 0702000 | DEBITI DIVERSI | ||
| 00405004 | Erario c/ R.a.i.a. | 45,12 | 00702003 | Debiti v/dipendenti | 91.790,36 |
| 00405010 | Clienti c/Fatture da emettere | 144.101,31 | 00702005 | Debiti v/Ist. Previdenziali (INPS) | 53.026,03 |
| 00405020 | Depositi cauzionali | 2.730,04 | 00702010 | Ritenute su rivalutazione TFR | 294,96 |
| ATTIVITA' A BREVE | 146.876,47 | 00702024 | Finanziamento da controllante | 3.881.609,00 | |
| 0401000 | CASSA | 00702025 | Finanziamento da controllata | 805.000,00 | |
| 00401001 | Cassa contante | 11.918,68 | 00702027 | Debiti per BOLLO VIRTUALE | -5.928,00 |
| 00401002 | Cassa valori bollati | 521,93 | 00702028 | Altri Debiti | 90.000,00 |
| CASSA | 12.440,61 | 00702029 | Cessione del quinto | 465,00 | |
| 00702030 | DEBITO PER SALDO QUOTE EMO LAB | 300.000,00 | |||
| 0402000 | ERARIO C/IVA | DEBITI DIVERSI | 5.216.257,35 |
OUTPUT
| FILE NAME | ACCOUNT DESC. | ACCOUNT ID | AMOUNT |
| PIPPO | CREDITI V/CLIENTI | 0301000 | 2180896 |
| PIPPO | Erario c/ R.a.i.a. | 00405004 | 45,12 |
| PIPPO | Clienti c/Fatture da emettere | 00405010 | 144101,31 |
| PIPPO | Depositi cauzionali | 00405020 | 2730,04 |
| PIPPO | Cassa contante | 00401001 | 11918,68 |
| PIPPO | Cassa valori bollati | 00401002 | 521,93 |
| PIPPO | DEBITI V/FORNITORI | 0601000 | 2466663,7 |
| PIPPO | Debiti v/dipendenti | 00702003 | 91790,36 |
| PIPPO | Debiti v/Ist. Previdenziali (INPS) | 00702005 | 53026,03 |
| PIPPO | Ritenute su rivalutazione TFR | 00702010 | 294,96 |
| PIPPO | Finanziamento da controllante | 00702024 | 3881609 |
| PIPPO | Finanziamento da controllata | 00702025 | 805000 |
| PIPPO | Debiti per BOLLO VIRTUALE | 00702027 | -5928 |
| PIPPO | Altri Debiti | 00702028 | 90000 |
| PIPPO | Cessione del quinto | 00702029 | 465 |
| PIPPO | DEBITO PER SALDO QUOTE EMO LAB | 00702030 | 300000 |
Hi Anonymous ,
Here's my solution.
1.Duplicate a table out, then keep half of the two tables, and unify the column names.
2.Append.
3.Group by.
4.Add custom columns.
5.Expand columns and remove the unneeded columns, then filter rows. Keep the row whose index is 1.
6.Add a custom column to get file name.
Here's the results.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous,
thanks for your answer.
However I dont know who accepted it as solution but it is not solving the problem.
You are still bringing subtotals for some accounts (which is wrong). Moreover you are achieving the result using 2 queries (for the first step, e.g. split credit/debit) and it would be much better having just 1 query
Can you please achieve the result with 1 query and cleaning subtotals as well?