Icon

Main_​Workflow

Step 1: Read BankStatements- Select file- Set correct row as columnheaders Step 2: Clean up- Remove empty columns- Remove irrelevant or empty rows Step 3: Standardise bank statement formats- Rename columns to be the same- Concatenate text columns Step 4: Use LLM to extract customer name from description text to link with ledger Side note:By concatenating, we can see that thecolumns in these disparate datasources end up with the same columnnames and types Step 4.2: Create prompt.Tell the LLM to extract the name from thedescription field. (Also concatenate the promptinstrutions with the description field). Read Ledger:A ledger example to show how tolink bank statements against Step 5: Join ledger with the bankstatement. We use the properties:- Date- Amount (Debit)- Customer name -> Extracted from thedescription fields using the LLMOne might also use a reference number, howeverit's possible that this might either not be presentor isn't unique enough. Step 4.1: Authenticate access to LLM Step 4.3: Send prompt to themodel and get a response LLM Bonus: Visualise the 'meaning representation' of the Descriptiontest as a scatter plot. Statement 1Statement 2Remove columnswhich we don't need and empty columnsRemove columnswhich we don't needRemove rowswith missing dateRemove rowthat is just formattingRemove rowswith missingamountClean up accountnumber which hasother charactersRename columnsinto the unified namesRename columnsinto the unified namesEnsure correcttype of numericalvaluesConvert special stringdate format toKnime Date formatConvert special stringdate format toKnime Date formatGet debit recordsGet credit recordsRemove rowswith missing dateRemove rowswith missingamountRename 'Amount'to 'Debit'Rename 'Amount #1'to 'Credit' Put Credit andDebit in sametableRename columnsinto the unified namesLog-inConfigure settings for LLMQuery LLMCreate promptOpenAIAPI Key Sample LedgerCreate promptQuery LLMNode 38Node 39Node 40Configure settingsfor LLM embeddingsConvert text toembeddingReduce from 1536dimensions/numersdown to 2dimensionsSplit embeddinginto columnsNode 45Make sureDecription isthe RowID soit is visible inthe scatter plotOnly extract 'Bank' recordsExcel Reader Excel Reader Column Filter Column Filter Row Filter(deprecated) Row Filter(deprecated) Row Filter(deprecated) String Manipulation Column Renamer Column Renamer Concatenate String to Number String to Date&Time String to Date&Time Column Filter Column Filter Row Filter(deprecated) Row Filter(deprecated) Column Renamer Column Renamer Concatenate Column Renamer OpenAIAuthenticator OpenAI LLMConnector LLM Prompter String Manipulation CredentialsConfiguration Excel Reader String Manipulation LLM Prompter Joiner String to Number String to Date&Time OpenAI EmbeddingsConnector Text Embedder PCA Split CollectionColumn Scatter Plot RowID Row Filter Step 1: Read BankStatements- Select file- Set correct row as columnheaders Step 2: Clean up- Remove empty columns- Remove irrelevant or empty rows Step 3: Standardise bank statement formats- Rename columns to be the same- Concatenate text columns Step 4: Use LLM to extract customer name from description text to link with ledger Side note:By concatenating, we can see that thecolumns in these disparate datasources end up with the same columnnames and types Step 4.2: Create prompt.Tell the LLM to extract the name from thedescription field. (Also concatenate the promptinstrutions with the description field). Read Ledger:A ledger example to show how tolink bank statements against Step 5: Join ledger with the bankstatement. We use the properties:- Date- Amount (Debit)- Customer name -> Extracted from thedescription fields using the LLMOne might also use a reference number, howeverit's possible that this might either not be presentor isn't unique enough. Step 4.1: Authenticate access to LLM Step 4.3: Send prompt to themodel and get a response LLM Bonus: Visualise the 'meaning representation' of the Descriptiontest as a scatter plot. Statement 1Statement 2Remove columnswhich we don't need and empty columnsRemove columnswhich we don't needRemove rowswith missing dateRemove rowthat is just formattingRemove rowswith missingamountClean up accountnumber which hasother charactersRename columnsinto the unified namesRename columnsinto the unified namesEnsure correcttype of numericalvaluesConvert special stringdate format toKnime Date formatConvert special stringdate format toKnime Date formatGet debit recordsGet credit recordsRemove rowswith missing dateRemove rowswith missingamountRename 'Amount'to 'Debit'Rename 'Amount #1'to 'Credit' Put Credit andDebit in sametableRename columnsinto the unified namesLog-inConfigure settings for LLMQuery LLMCreate promptOpenAIAPI Key Sample LedgerCreate promptQuery LLMNode 38Node 39Node 40Configure settingsfor LLM embeddingsConvert text toembeddingReduce from 1536dimensions/numersdown to 2dimensionsSplit embeddinginto columnsNode 45Make sureDecription isthe RowID soit is visible inthe scatter plotOnly extract 'Bank' recordsExcel Reader Excel Reader Column Filter Column Filter Row Filter(deprecated) Row Filter(deprecated) Row Filter(deprecated) String Manipulation Column Renamer Column Renamer Concatenate String to Number String to Date&Time String to Date&Time Column Filter Column Filter Row Filter(deprecated) Row Filter(deprecated) Column Renamer Column Renamer Concatenate Column Renamer OpenAIAuthenticator OpenAI LLMConnector LLM Prompter String Manipulation CredentialsConfiguration Excel Reader String Manipulation LLM Prompter Joiner String to Number String to Date&Time OpenAI EmbeddingsConnector Text Embedder PCA Split CollectionColumn Scatter Plot RowID Row Filter

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: jacobcoles
Created at: 2024-10-09
On NodePit since: 2024-10-14
Last update: 2024-11-18
Created with KNIME version: v5.3.2

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

Try NodePit Runner!