Icon

01_​Flow Variable and Component - Exercise

Activity IV: Create a component with widget nodes to perform re-execution as input values changeIn the workflow you constructed in Activity III, modify the component with the following changesAdd a Value Selection Widget node, with the input table from the component input. Use the Dropdown menu on theFirst(CustomerGroup) column (make sure to lock the column), with CC1 as the default. Pass on the selection as the flowvariable customer_group. Under the Re-execution tab, check Re-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 Columnto test is the First(CustomerGroup) column. For the matching pattern, make a flow variable connection from the Value SelectionWidget node for the customer group 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 theFirst(AgeGroup) column (make sure to lock the column), with Adult as the default. Pass on the selection as the flow variableage_group.Add a Refresh Button Widget node with the Button text as Refresh. Make a flow variable connection from this node to theValue Selection Widget node 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. TheColumn to test is the First(AgeGroup) column. For the matching pattern, make a flow variable connection from the ValueSelection Widget node for the age group selection and use the flow variable age_group.Adjust the composite view layout to accommodate the additional widgets, if desired. Activity I: Filter the records only from the store with the highest total saleFind the store with the highest total saleFrom the output table of the Processed Customer Data metanode, use the Row Aggregator node to find the total sale for eachstore. Use StoreID as the category 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 variableStoreID carries the ID of the store with the highest total sale.Filter the data from the Processed Customer Data metanode with the Row Filter node, only to include the records from thestore with the highest total 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.Save the output of the Processed Customer Data metanode to an Excel file using the Excel Writer node. Specify the pathrelative to the current workflow, in the folder data, with the filename Customer_data.xlsx. Overwrite the existing file, if the filealready 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.Create a path variable specifying the name and the location of the output Excel fileIn the Date&Time Configuration node, select Date&time as the type and check the box Use execution time. Pass onthe resulting execution time as the flow variable Execution_time.Using the String Manipulation (Variable) node, append the flow variable Execution_time to a string "Store-filtered-." You alsoneed 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 (seebelow). Use a relative path 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 under file_folder_variables under the Flow Variables tab) with the flow variableFile_name. Use "xlsx" as the File extension.Write the data table from the store with the highest total sale to an Excel fileConnect 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 filelocation with the flow variable output-location. Overwrite the file if it already exists.*Hint: Use the expression: join("Store-filtered-",replaceChars($${SExecution_date}$$,":" ,"-" )) Activity V (Optional): Generate an array flow variable for a selection of multiple groups in a configuration nodeCreate an array flow variable of customer groups by following these stepsFrom the output data table of the Excel Reader node, remove all columns except CustomerGroup (Column Filter node)Remove duplicate rows (Duplicate Row Filter node)Transpose the table, converting rows of data into columns (Table Transposer node)Aggregate values from all columns into a collection data cell with a Create Collection Column node. Append the resultingcollection cell as a new column AggregatedValues.Convert the table to flow variables with a Table Row to Variable node. Now the collection cell is converted to an array flowvariable AggregatedValues, containing all customer groups.Add a Multiple Selection Configuration node, with a flow variable connection from the Table Row to Variable node. Use Twinlistas the Selection Type. Set the output flow variable name as customer_group. Under the Flow Variables tab, setpossible_choices as the array flow variable AggregatedValues.Wrap the Multiple Selection Configuration node into a component, name it Select Customer Groups. Set up the component byfollowing these stepsAdd a table output port to the componentOpen the component and configure the Component Input to propagate the flow variable AggregatedValues.Connect the table output port of the Multiple Selection Configuration node to the component output portFilter 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 setcustomer_group as the Reference column. Part 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. These examples also outline how flow variables are created andapplied as part of a component with configuration and widget nodes. You'll find the instructions to the exercises in the yellow annotations. Activity III: Create a visualization component that allows a user to filter the data based on the store typeUsing the data table from the Excel Reader node, summarize the total sale for individual customers and the store type with aGroupBy node.Select CustomerID and StoreType as groups.Choose the following aggregation methodsFirst for AgeGroup, CustomerGroupSum for PriceUnique Count for ProductNrCreate a component to visualize the aggregation results for a store type specified by a userAsk a user to select a store type (online or onsite) with the Value Selection Configuration node. Use the Dropdown menu on theStoreType column (make sure to lock the column), with Online Store as the default. Pass on the selection as the flow variablestore_type.Filter the aggregation table by a store type using a Row Filter node. The Column to test is the StoreType column. For thematching pattern, make a flow variable connection from the Value Selection Configuration node and use the flow variablestore_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 ViewScatter Plot: set horizontal dimensions to Sum(Price), vertical dimensions to UniqueCount(ProductNr), and color dimension toFirst(AgeGroup)Bar Chart: choose First(AgeGroup) and occurrence countParallel Coordinates Plot: exclude CustomerID and StoreTypeWrap all the nodes after the GroupBy node into a component. Adjust the composite view layout, if desired. The resultingcomponent can be configured to select a desired store type. Exercise INode 412Node 413Node 418Joined dataStats per customerNode 432Store with HighestTotal Sale Excel Reader Excel Reader Excel Reader ProcessedCustomer Data GroupBy Excel Reader Visualization Activity IV: Create a component with widget nodes to perform re-execution as input values changeIn the workflow you constructed in Activity III, modify the component with the following changesAdd a Value Selection Widget node, with the input table from the component input. Use the Dropdown menu on theFirst(CustomerGroup) column (make sure to lock the column), with CC1 as the default. Pass on the selection as the flowvariable customer_group. Under the Re-execution tab, check Re-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 Columnto test is the First(CustomerGroup) column. For the matching pattern, make a flow variable connection from the Value SelectionWidget node for the customer group 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 theFirst(AgeGroup) column (make sure to lock the column), with Adult as the default. Pass on the selection as the flow variableage_group.Add a Refresh Button Widget node with the Button text as Refresh. Make a flow variable connection from this node to theValue Selection Widget node 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. TheColumn to test is the First(AgeGroup) column. For the matching pattern, make a flow variable connection from the ValueSelection Widget node for the age group selection and use the flow variable age_group.Adjust the composite view layout to accommodate the additional widgets, if desired. Activity I: Filter the records only from the store with the highest total saleFind the store with the highest total saleFrom the output table of the Processed Customer Data metanode, use the Row Aggregator node to find the total sale for eachstore. Use StoreID as the category 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 variableStoreID carries the ID of the store with the highest total sale.Filter the data from the Processed Customer Data metanode with the Row Filter node, only to include the records from thestore with the highest total 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.Save the output of the Processed Customer Data metanode to an Excel file using the Excel Writer node. Specify the pathrelative to the current workflow, in the folder data, with the filename Customer_data.xlsx. Overwrite the existing file, if the filealready 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.Create a path variable specifying the name and the location of the output Excel fileIn the Date&Time Configuration node, select Date&time as the type and check the box Use execution time. Pass onthe resulting execution time as the flow variable Execution_time.Using the String Manipulation (Variable) node, append the flow variable Execution_time to a string "Store-filtered-." You alsoneed 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 (seebelow). Use a relative path 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 under file_folder_variables under the Flow Variables tab) with the flow variableFile_name. Use "xlsx" as the File extension.Write the data table from the store with the highest total sale to an Excel fileConnect 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 filelocation with the flow variable output-location. Overwrite the file if it already exists.*Hint: Use the expression: join("Store-filtered-",replaceChars($${SExecution_date}$$,":" ,"-" )) Activity V (Optional): Generate an array flow variable for a selection of multiple groups in a configuration nodeCreate an array flow variable of customer groups by following these stepsFrom the output data table of the Excel Reader node, remove all columns except CustomerGroup (Column Filter node)Remove duplicate rows (Duplicate Row Filter node)Transpose the table, converting rows of data into columns (Table Transposer node)Aggregate values from all columns into a collection data cell with a Create Collection Column node. Append the resultingcollection cell as a new column AggregatedValues.Convert the table to flow variables with a Table Row to Variable node. Now the collection cell is converted to an array flowvariable AggregatedValues, containing all customer groups.Add a Multiple Selection Configuration node, with a flow variable connection from the Table Row to Variable node. Use Twinlistas the Selection Type. Set the output flow variable name as customer_group. Under the Flow Variables tab, setpossible_choices as the array flow variable AggregatedValues.Wrap the Multiple Selection Configuration node into a component, name it Select Customer Groups. Set up the component byfollowing these stepsAdd a table output port to the componentOpen the component and configure the Component Input to propagate the flow variable AggregatedValues.Connect the table output port of the Multiple Selection Configuration node to the component output portFilter 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 setcustomer_group as the Reference column. Part 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. These examples also outline how flow variables are created andapplied as part of a component with configuration and widget nodes. You'll find the instructions to the exercises in the yellow annotations. Activity III: Create a visualization component that allows a user to filter the data based on the store typeUsing the data table from the Excel Reader node, summarize the total sale for individual customers and the store type with aGroupBy node.Select CustomerID and StoreType as groups.Choose the following aggregation methodsFirst for AgeGroup, CustomerGroupSum for PriceUnique Count for ProductNrCreate a component to visualize the aggregation results for a store type specified by a userAsk a user to select a store type (online or onsite) with the Value Selection Configuration node. Use the Dropdown menu on theStoreType column (make sure to lock the column), with Online Store as the default. Pass on the selection as the flow variablestore_type.Filter the aggregation table by a store type using a Row Filter node. The Column to test is the StoreType column. For thematching pattern, make a flow variable connection from the Value Selection Configuration node and use the flow variablestore_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 ViewScatter Plot: set horizontal dimensions to Sum(Price), vertical dimensions to UniqueCount(ProductNr), and color dimension toFirst(AgeGroup)Bar Chart: choose First(AgeGroup) and occurrence countParallel Coordinates Plot: exclude CustomerID and StoreTypeWrap all the nodes after the GroupBy node into a component. Adjust the composite view layout, if desired. The resultingcomponent can be configured to select a desired store type. Exercise INode 412Node 413Node 418Joined dataStats per customerNode 432Store with HighestTotal Sale Excel Reader Excel Reader Excel Reader ProcessedCustomer Data GroupBy Excel Reader Visualization

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: Emilio.Silvestri
Created at: 2023-07-26
On NodePit since: 2024-06-21
Last update: 2024-11-21
Created with KNIME version: v5.2.5

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

Try NodePit Runner!