Activity IV: Create a component with widget nodes to perform re-execution as input values change

1. In the workflow you constructed in Activity III, modify the component with the following changes. Perform these changes inside of the component.

Activity I: Filter the records only from the store with the highest total sale

1. Find the store with the highest total sale

   • From the output tableof the Excel Readernode (Customer Data), use the Row Aggregator nodeto find the total sale for each store. 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 variable StoreID carries the ID of the store with the highest total sale.

2. Filter the Customer Data data with the Row Filter node, only to include the records from the store with the highest total sale.

   • Create 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.

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. Export the resulting execution time as the flow variable named Execution_time.

   • Using the Variable Expression node, append the flow variable Execution_time to a string "Store-filtered-." You also need to replace colons (":") with hyphens ("-") in Execution_time.* Export 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:

     • Use a workflow relative path to the data/Highest sales stores folder as the location.

     • Under the Variable name, set the output variable name as output-location.

     • Control the file name (in the Flow Variables tab: file_folder_variables/path_values field) 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.

   • Create a flow variable connection from the Create File/Folder Variables node to the Excel Writer node. Control the output file location with the flow variable output-location. Overwrite the file if it already exists.

*Hint: You can instruct K-AI to generate the necessary expression. Or use the expression:"Store-filtered-" + replace($$["Execution_date"], ":", "-")

Activity V (Optional): Generate an array flow variable for a selection of multiple groups in a configuration node

1. 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 the aggregation 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 an array variable 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 as customer_group. In the Flow Variables tab, set possible_choices as the array flow variable Set(CustomerGroup).

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 Set(CustomerGroup).

   • Connect the table output port of the Multiple Selection Configuration node to the component output port

4. 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. Include the rows from the reference table.

01 - Building Components Using Flow Variables

Learning objective: In the following exercises you will learn how to use flow variables and build components using flow variables, configuration nodes and widgets.

Workflow 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 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 III: Create a visualization component that allows a user to filter the data based on the store type

1. 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 ProductNr

2. 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 Filter column is the StoreType column. For the matching pattern, create a flow 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: set horizontal dimensions to Sum(Price), vertical dimensions to UniqueCount(ProductNr), and color dimension to First(AgeGroup)

     • Bar Chart: choose First(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 configured to select a desired store type.