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?