Icon

KNIME_​repeatedly process wages with user-selection

KNIME_repeatedly process wages with user-selection

KNIME_repeatedly process wages with user-selection

Demo workflow for how we might record previous user-selection so that we don't repeatedly process already-processed records on subsequent invocations of the workflow.

We have an Excel spreadsheet consisting of Name, Date of Birth, Wage and a column for Wage Increase. The idea is that on each run of the workflow, a user could select one or more rows from the Table View and when selected, a 10% wage would be applied to the selected rows.

We want to present to the user the entire file of wages each time, but only allow them to process somebody if they haven't been processed before. Clearly we could just not allow processing if the "increase" column showed non-zero ;-) but let's ignore that minor detail and work on the idea that we only process a row if the "previously selected" flag that we've stored away on a csv file is not set to "Y".

On the Table View, the user can select any row they like (I couldn't see how to prevent that ) so after returning from the Table View, a Rule Engine 'de-selects' any row that was previous selected.

The Wage Increase action takes place on the required rows, and the "previously selected" flags are stored away in a CSV file for processing again on a future run of the workflow.

In the attached workflow, to I have used a couple of "home grown" components. These have blue-box annotations near them. They are here because it is easier for me to produce the working demo with these to simplify a few things, but they aren't really related to the mechanics of the demonstration. You are free to make use of them or replace with your own nodes and processes as you see fit.

The boxes in red indicate the nodes that are just used to "reset" the workflow to an initialised state. You can activate those nodes by configuring the CASE switch to use port 0. Set it back to 1 again to run (and re-run) the main branch of the workflow .

Increase WAGE by 10% if the person is selected in the Table View,but we don't want to process them again if they have already beenselected and processed on a previous run of the workflow Set active port on CASE Switchto 0 to reset the demo WAGES file INITIAL SETUP ONLYResets files to initial state.This section is performed only ifthe case switch port is set to 0 Build an MD5 key for each row, based on Name and Date of Birth Store away those that have been selected and will therefore have been processedready for next iteration Update the WAGE change in Excel ready for nextiteration My Local File Name Variablescomponents simply allow me to setup ademo workflow easily by creating flowvariables pointing at files/folders withinthe workflow's data folder.You can use these components, ordefine your own flow variables to point atthe required files.This component auto-generates String,Path and URL flow variable versions ofeach file so is easy to use with any Filenode (old and new)If you look at the output flow variablesfrom each you will see there aremultiple variables with the same basename,that can be used for differentpurposes E nsure that the CASE Switch is configured to use port 1, otherwise it will initialise all thefiles to restart the demo.You can now repeatedly reset the nodes (Ctrl A - F8) and run all the nodes (Ctrl A-F7). Youcan open the interactive table view and select rows to have wage increased by 10%(remember to apply changes).Once a wage increase has occurred, it cannot be re-applied so no repeat-increases! Evenif the user selects a person twice, the subsequent Rule Engines ensure that no previously-selected person is processed a second time. My "helper" component "Column Headings as Array Variable" simplystores the column headings from the input table as an array variable ofthe name given in the configuration for the node.This array variable is then used in the final Column Filter prior to writingthe file back out and ensures that no matter what other processing hasoccurred, or columns added, only the original columns are written backout.I prefer this method over "Enforce Exclusion" in the Column Filter which Ihave not always find to be reliable. The Rule Engines ensure that even if the user has re-selected an item that has previously been selected, it isnot processed. The first rule engine de-selects any previously selecteditemsThe second rule engine marks anything previouslyselected or newly selected as "previously selected" forwriting back to the "SelectedList.csv" file FileToProcess(Wages XLSX file)Select Peopleto have wage increaseRead Staff Wages fileInitial setup of demo xls fileWrite initial state of fileGet MD5hashDerive unique "key" columnsRename asUniqueKeyIf not selected this time, rowtake upper pathif row is selected, takeslower pathIf a record waspreviously selected, ensure that wedeselect it if it hasbeen selected againRemove all columns other than those specified in "SelectListColumns []"Write back selected to SelectedList.csvRead the SelectList.csv fileJoin WAGES datawith any previous selectionsso we mark the rows that havepreviously been processedSelectedListCSVWorkflowDataFolderFill in missing selected flags to "N"Colour code the"previouslyselected"columnCalc Salary Increaseof 10%Update WageGet all therecords togetheragainWrite back wage changes to Excel FileNode 49Remove all columnsother than thosespecified inWagesColumns []Anything selectedthis time, or previously selectedwill have previously_selectedset to "Y" so itwill be recordedStore awayanything thathas been selected this timeor previously(previously selected=Y)Node 53Node 55Initial setup of SelectedList csvInitial Setupof SelectedList.csvInclude a temporaryrow numberso we can ensure samesort order as original fileafter processingResort therows back to original orderStore column names from Wages file asWagesColumns [ ]Store column names from SelectListas SelectListColumnsNode 62 Local FileName Variables Table View Excel Reader Table Creator Excel Writer String Manipulation Column Aggregator Column Rename Row Splitter Rule Engine Column Filter CSV Writer CSV Reader Joiner Local FileName Variables Local FileName Variables Missing Value Color Manager Math Formula Math Formula Concatenate Excel Writer Column Filter Column Filter Rule Engine Row Filter CASE SwitchVariable (Start) Merge Variables Table Creator CSV Writer Counter Generation Sorter Column Headingsas Array Variable Column Headingsas Array Variable Sorter Increase WAGE by 10% if the person is selected in the Table View,but we don't want to process them again if they have already beenselected and processed on a previous run of the workflow Set active port on CASE Switchto 0 to reset the demo WAGES file INITIAL SETUP ONLYResets files to initial state.This section is performed only ifthe case switch port is set to 0 Build an MD5 key for each row, based on Name and Date of Birth Store away those that have been selected and will therefore have been processedready for next iteration Update the WAGE change in Excel ready for nextiteration My Local File Name Variablescomponents simply allow me to setup ademo workflow easily by creating flowvariables pointing at files/folders withinthe workflow's data folder.You can use these components, ordefine your own flow variables to point atthe required files.This component auto-generates String,Path and URL flow variable versions ofeach file so is easy to use with any Filenode (old and new)If you look at the output flow variablesfrom each you will see there aremultiple variables with the same basename,that can be used for differentpurposes Ensure that the CASE Switch is configured to use port 1, otherwise it will initialise all thefiles to restart the demo.You can now repeatedly reset the nodes (Ctrl A - F8) and run all the nodes (Ctrl A-F7). Youcan open the interactive table view and select rows to have wage increased by 10%(remember to apply changes).Once a wage increase has occurred, it cannot be re-applied so no repeat-increases! Evenif the user selects a person twice, the subsequent Rule Engines ensure that no previously-selected person is processed a second time. My "helper" component "Column Headings as Array Variable" simplystores the column headings from the input table as an array variable ofthe name given in the configuration for the node.This array variable is then used in the final Column Filter prior to writingthe file back out and ensures that no matter what other processing hasoccurred, or columns added, only the original columns are written backout.I prefer this method over "Enforce Exclusion" in the Column Filter which Ihave not always find to be reliable. The Rule Engines ensure that even if the user has re-selected an item that has previously been selected, it isnot processed. The first rule engine de-selects any previously selecteditemsThe second rule engine marks anything previouslyselected or newly selected as "previously selected" forwriting back to the "SelectedList.csv" file FileToProcess(Wages XLSX file)Select Peopleto have wage increaseRead Staff Wages fileInitial setup of demo xls fileWrite initial state of fileGet MD5hashDerive unique "key" columnsRename asUniqueKeyIf not selected this time, rowtake upper pathif row is selected, takeslower pathIf a record waspreviously selected, ensure that wedeselect it if it hasbeen selected againRemove all columns other than those specified in "SelectListColumns []"Write back selected to SelectedList.csvRead the SelectList.csv fileJoin WAGES datawith any previous selectionsso we mark the rows that havepreviously been processedSelectedListCSVWorkflowDataFolderFill in missing selected flags to "N"Colour code the"previouslyselected"columnCalc Salary Increaseof 10%Update WageGet all therecords togetheragainWrite back wage changes to Excel FileNode 49Remove all columnsother than thosespecified inWagesColumns []Anything selectedthis time, or previously selectedwill have previously_selectedset to "Y" so itwill be recordedStore awayanything thathas been selected this timeor previously(previously selected=Y)Node 53Node 55Initial setup of SelectedList csvInitial Setupof SelectedList.csvInclude a temporaryrow numberso we can ensure samesort order as original fileafter processingResort therows back to original orderStore column names from Wages file asWagesColumns [ ]Store column names from SelectListas SelectListColumnsNode 62Local FileName Variables Table View Excel Reader Table Creator Excel Writer String Manipulation Column Aggregator Column Rename Row Splitter Rule Engine Column Filter CSV Writer CSV Reader Joiner Local FileName Variables Local FileName Variables Missing Value Color Manager Math Formula Math Formula Concatenate Excel Writer Column Filter Column Filter Rule Engine Row Filter CASE SwitchVariable (Start) Merge Variables Table Creator CSV Writer Counter Generation Sorter Column Headingsas Array Variable Column Headingsas Array Variable Sorter

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: @takbb Brian Bates
Created at: 2021-05-10
On NodePit since: 2021-05-11
Last update: 2024-11-21
Created with KNIME version: v4.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!