Icon

01_​Flow Variable and Component - Solution

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- From 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 (Transpose node)- Aggregate values from all columns into a collection data cell with a Create Collection Column node. Append the resulting collection 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 flow variable AggregatedValues, containing all customer groups.2. Add a Multiple Selection Configuration node, with a flow variable connection from the Table Row to Variable node. Use Twinlist as the Selection Type. Set the output flow variable name ascustomer_group. Under the Flow Variables tab, set possible_choices as the array flow variable AggregatedValues.3. Wrap the Multiple Selection Configuration node into a component, name it Select Customer Groups. Set up the component by following these steps- Add a table output port to the component- Open 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 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 Reference column. 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. These examples also outline how flow variables are createdand 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 the group column, and aggregate the sum of the columnPrice.- 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 the store 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 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.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 current workflow, in the folder data, with the filenameCustomer_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 as the 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 relative path to the data folder as the location. Under theVariable 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 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 variable output-location. Overwrite the file if it alreadyexists.*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 Storeas 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 a flow variable connection from the Value SelectionConfiguration 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 (set horizontal dimensions to Sum(Price), verticaldimensions to UniqueCount(ProductNr), and color dimension to First(AgeGroup)), Bar Chart (choose First(AgeGroup) and occurrence count), Parallel Coordinates Plot (exclude CustomerIDand StoreType)- Wrap all the nodes after the GroupBy node into a component. Adjust the composite view layout, if desired. The resulting component can be configured to 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 thedefault. Pass on the selection as the flow variable 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 Column to test is the First(CustomerGroup) column. For the matching pattern,make a flow variable connection from the Value Selection Widget 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 the First(AgeGroup) column (make sure to lock the column), with Adult as thedefault. 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 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. The Column to test is the First(AgeGroup) column. For the matching pattern,make a flow variable connection from the Value Selection 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. Joined data Aggregate price per storeDescending order by total priceFirst row to variableWrite file with execution dateExercise 1Set execution date and timeJoin file name with execution dateStats per customerSelect data based on store typeStats per customerCustomer group columnRemove duplicatesAggregate valuesinto arraysPath flow variableProcessedCustomer Data GroupBy Sorter Table Rowto Variable Row Filter Excel Writer Excel Reader Excel Writer Store with HighestTotal Sale Date&TimeConfiguration String Manipulation(Variable) Excel Reader GroupBy Store Selection Visualization Excel Reader GroupBy Excel Reader Column Filter DuplicateRow Filter Transpose Create CollectionColumn Table Rowto Variable Select CustomerGroups ReferenceRow Filter Create File/FolderVariables 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- From 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 (Transpose node)- Aggregate values from all columns into a collection data cell with a Create Collection Column node. Append the resulting collection 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 flow variable AggregatedValues, containing all customer groups.2. Add a Multiple Selection Configuration node, with a flow variable connection from the Table Row to Variable node. Use Twinlist as the Selection Type. Set the output flow variable name ascustomer_group. Under the Flow Variables tab, set possible_choices as the array flow variable AggregatedValues.3. Wrap the Multiple Selection Configuration node into a component, name it Select Customer Groups. Set up the component by following these steps- Add a table output port to the component- Open 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 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 Reference column. 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. These examples also outline how flow variables are createdand 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 the group column, and aggregate the sum of the columnPrice.- 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 the store 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 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.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 current workflow, in the folder data, with the filenameCustomer_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 as the 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 relative path to the data folder as the location. Under theVariable 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 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 variable output-location. Overwrite the file if it alreadyexists.*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 Storeas 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 a flow variable connection from the Value SelectionConfiguration 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 (set horizontal dimensions to Sum(Price), verticaldimensions to UniqueCount(ProductNr), and color dimension to First(AgeGroup)), Bar Chart (choose First(AgeGroup) and occurrence count), Parallel Coordinates Plot (exclude CustomerIDand StoreType)- Wrap all the nodes after the GroupBy node into a component. Adjust the composite view layout, if desired. The resulting component can be configured to 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 thedefault. Pass on the selection as the flow variable 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 Column to test is the First(CustomerGroup) column. For the matching pattern,make a flow variable connection from the Value Selection Widget 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 the First(AgeGroup) column (make sure to lock the column), with Adult as thedefault. 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 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. The Column to test is the First(AgeGroup) column. For the matching pattern,make a flow variable connection from the Value Selection 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. Joined data Aggregate price per storeDescending order by total priceFirst row to variableWrite file with execution dateExercise 1Set execution date and timeJoin file name with execution dateStats per customerSelect data based on store typeStats per customerCustomer group columnRemove duplicatesAggregate valuesinto arraysPath flow variableProcessedCustomer Data GroupBy Sorter Table Rowto Variable Row Filter Excel Writer Excel Reader Excel Writer Store with HighestTotal Sale Date&TimeConfiguration String Manipulation(Variable) Excel Reader GroupBy Store Selection Visualization Excel Reader GroupBy Excel Reader Column Filter DuplicateRow Filter Transpose Create CollectionColumn Table Rowto Variable Select CustomerGroups ReferenceRow Filter Create File/FolderVariables

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-11-19
Last update: 2024-11-21
Created with KNIME version: v4.7.6

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

Try NodePit Runner!