Icon

01_​Flow Variable and Component - Solution

Session 1 - Building Components Using Flow Variables Learning objective: In the following exercises you will learn how to build a component using flow variables, configuration nodes and widgetsWorkflow description: This workflow consists of multiple exercises that demonstrate how flow variables can be used to control workflows. Theseexamples also outline how flow variables are created and applied as part of a component with configuration and widget nodes.You'll find the instructions to the exercises in the yellow annotations. Activity I: Filter the records only from the store with the highest total sale1. Find the store with the highest total sale- From the output table of the Processed Customer Data metanode, use the GroupBy node to find the total sale for each store. Use StoreID as thegroup column, and aggregate the sum of the column Price.- Sort the resulting table with the Sorter node. Sort the total price in the Price column in descending order.- Convert the first row of the sorted table to flow variables with the Table Row to Variable node. The resulting flow variable StoreID carries the ID of thestore with the highest total sale.2. Filter the data from the Processed Customer Data metanode with the Row Filter node, only to include the records from the store with the highesttotal sale.- Make a flow variable connection from the Table Row to Variable node to the Row Filter node.- Filter based on the column StoreID. Use the pattern matching with the pattern specified by the flow variable StoreID.3. Save the output of the Processed Customer Data metanode to an Excel file using the Excel Writer node. Specify the path relative to the currentworkflow, in the folder data, with the filename Customer_data.xlsx. Overwrite the existing file, if the file already exists. (hint: to specify the file location,use "../../data/Customer_data.xlsx") Activity II: Create a path variable with a file name with an execution time and use it to save an Excel file.1. Create a path variable specifying the name and the location of the output Excel file- In the Date&Time Configuration node, select Date&time as the type and check the box "Use execution time". Pass on the resulting execution time asthe flow variable Execution_date.- Using the String Manipulation (Variable) node, append the flow variable Execution_time to a string "Store-filtered-." You also need to replace colons(":") with hyphens ("-") in Execution_time.* Append the resulting flow variable as File-name.- Create a path flow variable with the Create File/Folder Variables node to specify the location of the Excel file to be saved (see below). Use a relativepath to the data folder as the location. Under the Variable name, set the output variable name as output-location. Set the file name (path_variable underfile_folder_variables under the Flow Variables tab) with the flow variable File_name. Use "xlsx" as the File extension.2. Write the data table from the store with the highest total sale to an Excel file- Connect the output table from the Store with Highest Total Sale metanode to an Excel Writer node.- Make a flow variable connection from the Create File/Folder Variables node to the Excel Writer node. Set the output file location with the flow variableoutput-location. Overwrite the file if it already exists.*Hint: Use the expression: join("Store-filtered-",replaceChars($${SExecution_date}$$,":" ,"-" )) Activity III: Create a visualization component that allows a user to filter the data based on the store type1. Using the data table from the Excel Reader node, summarize the total sale for individual customers and the store type with a GroupBy node.- Select CustomerID and StoreType as groups.- Choose the following aggregation methods: First for AgeGroup& CustomerGroup, Sum for Price, Unique Count for ProductNr2. Create a component to visualize the aggregation results for a store type specified by a user- Ask a user to select a store type (online or onsite) with the Value Selection Configuration node. Use the Dropdown menu on the StoreType column(make sure to lock the column), with Online Store as the default. Pass on the selection as the flow variable store_type.- Filter the aggregation table by a store type using a Row Filter node. The Column to test is the StoreType column. For the matching pattern, make aflow variable connection from the Value Selection Configuration node and use the flow variable store_type.- In preparation for visualization, use a Color Manager node to assign colors to different classes of the First(AgeGroup) column.- Use the following four view nodes to visualize the data in four separate branches, with one view node per branch: Table View, Scatter Plot (sethorizontal dimensions to Sum(Price), vertical dimensions to UniqueCount(ProductNr), and color dimension to First(AgeGroup)), Bar Chart (chooseFirst(AgeGroup) and occurrence count), Parallel Coordinates Plot (exclude CustomerID and StoreType)- Wrap all the nodes after the GroupBy node into a component. Adjust the composite view layout, if desired. The resulting component can be configuredto select a desired store type. Activity IV: Create a component with widget nodes to perform re-execution as input values change1. In the workflow you constructed in Activity III, modify the component with the following changes- Add a Value Selection Widget node, with the input table from the component input. Use the Dropdown menu on the First(CustomerGroup) column(make sure to lock the column), with CC1 as the default. Pass on the selection as the flow variable customer_group. Under the Re-execution tab, checkRe-execution on widget value change.- After the first Row Filter node for selecting a store type, add another Row Filer node to select a customer group. The Column to test is theFirst(CustomerGroup) column. For the matching pattern, make a flow variable connection from the Value Selection Widget node for the customergroup selection and use the flow variable customer_group.- Add another Value Selection Widget node, with the input table from the component input. Use the Dropdown menu on the First(AgeGroup) column(make sure to lock the column), with Adult as the default. Pass on the selection as the flow variable age_group.- Add a Refresh Button Widget node with the Button text as Refresh. Make a flow variable connection from this node to the Value Selection Widgetnode for the age group selection.- After the second Row Filter node for selecting a customer group, add another Row Filer node to select an age group. The Column to test is theFirst(AgeGroup) column. For the matching pattern, make a flow variable connection from the Value Selection Widget node for the age group selectionand use the flow variable age_group.- Adjust the composite view layout to accommodate the additional widgets, if desired. Activity V (Optional): Generate an array flow variable for a selection of multiple groups in a configuration node1. Create an array flow variable of customer groups by following these steps- Connect the output port of the Excel Reader node to a GroupBy node- In the GroupBy node, no column needs to be selected under the Groups tab. Under the Manual Aggregation tab, select CustomerGroup and set theaggregation method Set- Convert the output table of the GroupBy node to flow variables with a Table Row to Variable node. Now the flow variable Set(CustomerGroup) is anarray variable containing all customer groups2. Add a Multiple Selection Configuration node, with a flow variable connection from the Table Row to Variable node. Use Twinlist as the SelectionType. Set the output flow variable name as customer_group. Under the Flow Variables tab, set possible_choices as the array flow variableSet(CustomerGroup).3. Wrap the Multiple Selection Configuration node into a component, name it Select Customer Groups. Set up the component by following thesesteps- Add a table output port to the component- Open the component and configure the Component Input to propagate the flow variable Set(CustomerGroup).- Connect the table output port of the Multiple Selection Configuration node to the component output port4. Filter the customer data based for the selected customer group(s)- Pass the data table from the Excel Reader node to the top input port of a Reference Row Filter node.- Pass the output table from the Select Customer Groups component to the bottom input port of the Reference Row Filter node.- In the configuration window of the Reference Row Filter node, set CustomerGroup as the Data column, and set customer_group as the Referencecolumn. Joined dataAggregate price per storeDescending order by total priceFirst row to variableWrite file with execution dateExercise 1Set execution date and timeJoin file name with execution datePath flow variableStats per customerSelect data based on store typeStats per customerAggregate valuesinto array ProcessedCustomer Data GroupBy Sorter Table Rowto Variable Row Filter Excel Writer Excel Reader Excel Writer Store with HighestTotal Sale Date&TimeConfiguration String Manipulation(Variable) Create File/FolderVariables Excel Reader GroupBy Store Selection Visualization Excel Reader GroupBy Excel Reader ReferenceRow Filter GroupBy Table Rowto Variable Select CustomerGroups Session 1 - Building Components Using Flow Variables Learning objective: In the following exercises you will learn how to build a component using flow variables, configuration nodes and widgetsWorkflow description: This workflow consists of multiple exercises that demonstrate how flow variables can be used to control workflows. Theseexamples also outline how flow variables are created and applied as part of a component with configuration and widget nodes.You'll find the instructions to the exercises in the yellow annotations. Activity I: Filter the records only from the store with the highest total sale1. Find the store with the highest total sale- From the output table of the Processed Customer Data metanode, use the GroupBy node to find the total sale for each store. Use StoreID as thegroup column, and aggregate the sum of the column Price.- Sort the resulting table with the Sorter node. Sort the total price in the Price column in descending order.- Convert the first row of the sorted table to flow variables with the Table Row to Variable node. The resulting flow variable StoreID carries the ID of thestore with the highest total sale.2. Filter the data from the Processed Customer Data metanode with the Row Filter node, only to include the records from the store with the highesttotal sale.- Make a flow variable connection from the Table Row to Variable node to the Row Filter node.- Filter based on the column StoreID. Use the pattern matching with the pattern specified by the flow variable StoreID.3. Save the output of the Processed Customer Data metanode to an Excel file using the Excel Writer node. Specify the path relative to the currentworkflow, in the folder data, with the filename Customer_data.xlsx. Overwrite the existing file, if the file already exists. (hint: to specify the file location,use "../../data/Customer_data.xlsx") Activity II: Create a path variable with a file name with an execution time and use it to save an Excel file.1. Create a path variable specifying the name and the location of the output Excel file- In the Date&Time Configuration node, select Date&time as the type and check the box "Use execution time". Pass on the resulting execution time asthe flow variable Execution_date.- Using the String Manipulation (Variable) node, append the flow variable Execution_time to a string "Store-filtered-." You also need to replace colons(":") with hyphens ("-") in Execution_time.* Append the resulting flow variable as File-name.- Create a path flow variable with the Create File/Folder Variables node to specify the location of the Excel file to be saved (see below). Use a relativepath to the data folder as the location. Under the Variable name, set the output variable name as output-location. Set the file name (path_variable underfile_folder_variables under the Flow Variables tab) with the flow variable File_name. Use "xlsx" as the File extension.2. Write the data table from the store with the highest total sale to an Excel file- Connect the output table from the Store with Highest Total Sale metanode to an Excel Writer node.- Make a flow variable connection from the Create File/Folder Variables node to the Excel Writer node. Set the output file location with the flow variableoutput-location. Overwrite the file if it already exists.*Hint: Use the expression: join("Store-filtered-",replaceChars($${SExecution_date}$$,":" ,"-" )) Activity III: Create a visualization component that allows a user to filter the data based on the store type1. Using the data table from the Excel Reader node, summarize the total sale for individual customers and the store type with a GroupBy node.- Select CustomerID and StoreType as groups.- Choose the following aggregation methods: First for AgeGroup& CustomerGroup, Sum for Price, Unique Count for ProductNr2. Create a component to visualize the aggregation results for a store type specified by a user- Ask a user to select a store type (online or onsite) with the Value Selection Configuration node. Use the Dropdown menu on the StoreType column(make sure to lock the column), with Online Store as the default. Pass on the selection as the flow variable store_type.- Filter the aggregation table by a store type using a Row Filter node. The Column to test is the StoreType column. For the matching pattern, make aflow variable connection from the Value Selection Configuration node and use the flow variable store_type.- In preparation for visualization, use a Color Manager node to assign colors to different classes of the First(AgeGroup) column.- Use the following four view nodes to visualize the data in four separate branches, with one view node per branch: Table View, Scatter Plot (sethorizontal dimensions to Sum(Price), vertical dimensions to UniqueCount(ProductNr), and color dimension to First(AgeGroup)), Bar Chart (chooseFirst(AgeGroup) and occurrence count), Parallel Coordinates Plot (exclude CustomerID and StoreType)- Wrap all the nodes after the GroupBy node into a component. Adjust the composite view layout, if desired. The resulting component can be configuredto select a desired store type. Activity IV: Create a component with widget nodes to perform re-execution as input values change1. In the workflow you constructed in Activity III, modify the component with the following changes- Add a Value Selection Widget node, with the input table from the component input. Use the Dropdown menu on the First(CustomerGroup) column(make sure to lock the column), with CC1 as the default. Pass on the selection as the flow variable customer_group. Under the Re-execution tab, checkRe-execution on widget value change.- After the first Row Filter node for selecting a store type, add another Row Filer node to select a customer group. The Column to test is theFirst(CustomerGroup) column. For the matching pattern, make a flow variable connection from the Value Selection Widget node for the customergroup selection and use the flow variable customer_group.- Add another Value Selection Widget node, with the input table from the component input. Use the Dropdown menu on the First(AgeGroup) column(make sure to lock the column), with Adult as the default. Pass on the selection as the flow variable age_group.- Add a Refresh Button Widget node with the Button text as Refresh. Make a flow variable connection from this node to the Value Selection Widgetnode for the age group selection.- After the second Row Filter node for selecting a customer group, add another Row Filer node to select an age group. The Column to test is theFirst(AgeGroup) column. For the matching pattern, make a flow variable connection from the Value Selection Widget node for the age group selectionand use the flow variable age_group.- Adjust the composite view layout to accommodate the additional widgets, if desired. Activity V (Optional): Generate an array flow variable for a selection of multiple groups in a configuration node1. Create an array flow variable of customer groups by following these steps- Connect the output port of the Excel Reader node to a GroupBy node- In the GroupBy node, no column needs to be selected under the Groups tab. Under the Manual Aggregation tab, select CustomerGroup and set theaggregation method Set- Convert the output table of the GroupBy node to flow variables with a Table Row to Variable node. Now the flow variable Set(CustomerGroup) is anarray variable containing all customer groups2. Add a Multiple Selection Configuration node, with a flow variable connection from the Table Row to Variable node. Use Twinlist as the SelectionType. Set the output flow variable name as customer_group. Under the Flow Variables tab, set possible_choices as the array flow variableSet(CustomerGroup).3. Wrap the Multiple Selection Configuration node into a component, name it Select Customer Groups. Set up the component by following thesesteps- Add a table output port to the component- Open the component and configure the Component Input to propagate the flow variable Set(CustomerGroup).- Connect the table output port of the Multiple Selection Configuration node to the component output port4. Filter the customer data based for the selected customer group(s)- Pass the data table from the Excel Reader node to the top input port of a Reference Row Filter node.- Pass the output table from the Select Customer Groups component to the bottom input port of the Reference Row Filter node.- In the configuration window of the Reference Row Filter node, set CustomerGroup as the Data column, and set customer_group as the Referencecolumn. Joined dataAggregate price per storeDescending order by total priceFirst row to variableWrite file with execution dateExercise 1Set execution date and timeJoin file name with execution datePath flow variableStats per customerSelect data based on store typeStats per customerAggregate valuesinto array ProcessedCustomer Data GroupBy Sorter Table Rowto Variable Row Filter Excel Writer Excel Reader Excel Writer Store with HighestTotal Sale Date&TimeConfiguration String Manipulation(Variable) Create File/FolderVariables Excel Reader GroupBy Store Selection Visualization Excel Reader GroupBy Excel Reader ReferenceRow Filter GroupBy Table Rowto Variable Select CustomerGroups

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: elisabeth.richter
Created at: 2023-11-07
On NodePit since: 2024-03-08
Last update: 2024-04-27
Created with KNIME version: v4.7.8

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

Try NodePit Runner!