Icon

Solution 2 - Reconcile to Trial Balance

<p>In this exercise, you will import trial balance, join it to the aggregated journal entries and calculate the difference</p>
Exercise 2 - Reconcile to Trial Balance Activity I: Import and Process Trial BalanceRead the trial_balance.csv file. You can find it in the datafolder.Create a column Negative Balance as the negative of theBalance columnUse a Rule Engine node to assign negative values to theBalance column if the Type is Debit. Use the following formula$Type$ = "Debit" =&gt; $Negative Balance$TRUE =&gt; $Balance$ Activity II: Join Trial Balance to JournalsUse a Joiner node to join the Trial Balance data with theJournal data from Exercise 1.Use the Account Name as joining columnsPerform a right outer join, in order to also include the rowsfrom the trial balance that do not have a match in the journaltable. Activity III: Calculate DifferenceCreate a new column Difference, containing the differencebetween the Net column of the Journal and the Balancecolumn.Note: You can do this operation with a Math Formula or aColumn Expression node Activity IV: Polish TableUse a Column Filter node to remove the unnecessarycolumns. The columns you need areAccount NameNetBalanceDifferenceRename the columns as followsNet -&gt; Jnl ValueBalance -&gt; TB ValueResort the columns in the following order: Account Name, JnlValue, TB Value, Difference. Import and Manipulate Journal ExtractThis part is covered in Exercise 1 Learning objective: In this exercise, you will import trial balance, join it to the aggregated journal entries and calculate the differenceYou will find the instructions to the exercises in the yellow annotations. Raw Journal ExportJoin Trial Balance to JournalsCalculate difference between Trial Balance and Net Journal AmountRemoveUncecessary ColumnsRenameColumnsTrial BalanceMake a negativebalance columnLet Credits have positive balance and Debits have negative balanceRe-orderColumnsNode 125 CSV Reader Joiner Column Expressions Column Filter Column Renamer CSV Reader Math Formula Rule Engine Column Resorter Process Journal Exercise 2 - Reconcile to Trial Balance Activity I: Import and Process Trial BalanceRead the trial_balance.csv file. You can find it in the datafolder.Create a column Negative Balance as the negative of theBalance columnUse a Rule Engine node to assign negative values to theBalance column if the Type is Debit. Use the following formula$Type$ = "Debit" =&gt; $Negative Balance$TRUE =&gt; $Balance$ Activity II: Join Trial Balance to JournalsUse a Joiner node to join the Trial Balance data with theJournal data from Exercise 1.Use the Account Name as joining columnsPerform a right outer join, in order to also include the rowsfrom the trial balance that do not have a match in the journaltable. Activity III: Calculate DifferenceCreate a new column Difference, containing the differencebetween the Net column of the Journal and the Balancecolumn.Note: You can do this operation with a Math Formula or aColumn Expression node Activity IV: Polish TableUse a Column Filter node to remove the unnecessarycolumns. The columns you need areAccount NameNetBalanceDifferenceRename the columns as followsNet -&gt; Jnl ValueBalance -&gt; TB ValueResort the columns in the following order: Account Name, JnlValue, TB Value, Difference. Import and Manipulate Journal ExtractThis part is covered in Exercise 1 Learning objective: In this exercise, you will import trial balance, join it to the aggregated journal entries and calculate the differenceYou will find the instructions to the exercises in the yellow annotations. Raw Journal ExportJoin Trial Balance to JournalsCalculate difference between Trial Balance and Net Journal AmountRemoveUncecessary ColumnsRenameColumnsTrial BalanceMake a negativebalance columnLet Credits have positive balance and Debits have negative balanceRe-orderColumnsNode 125 CSV Reader Joiner Column Expressions Column Filter Column Renamer CSV Reader Math Formula Rule Engine Column Resorter Process Journal

Nodes

Extensions

Links

Download

To use this workflow in KNIME, download it from the below URL and open it in KNIME:

Download Workflow
Created by: J Crossman-Smith
Created at: 2024-03-25
On NodePit since: 2025-11-04
Last update: 2025-11-04
Created with KNIME version: v5.3.0

Deploy, schedule, execute, and monitor your KNIME workflows locally, in the cloud or on-premises – with our brand new NodePit Runner.

Try NodePit Runner!