02 - Workflow Control

Activity II: Read multiple Excel files and combine them into a single table with a loop

1. Each file in the folder data/Stores by years contains sales records for the store, with separate sheets for different years. Create a list of Excel files in this folder with a List Files/Folders node.

2. Pass the resulting table with the Excel paths to a Table to Variable Loop Start node.

3. Add an Excel Reader node and create a flow variable connection from the Table to Variable Loop Start node. Use the flow variable Path to specify the location of the input file. Select the sheet 2022 by name.

4. Feed the output table from the Excel Reader node to a Loop End node. After executing all iterations, the tables from all the Excel input files are concatenated into a single table at the output port of the Loop End node.

Activity IV: Conditional execution of a workflow branch with an IF Switch (optional)

1. From the output table of the Excel Reader node, calculate the average age with a GroupBy node. No group column is needed. As for the aggregation method, calculate the Mean of the Age column.

2. Convert the first row of the resulting aggregation table to flow variables with a Table Row to Variable node. Now the average age is propagated by a flow variable Mean(Age).

3. Based on the value of the flow variable Mean(Age), generate a string flow variable with a Variable Expression node.

   • If Mean(Age) is less than or equal to 30, then output a string "top". Otherwise, output a string "bottom"*.

   • The output string is propagated in a flow variable condition.

4. Form another branch of a workflow by connecting the output of the Excel Reader node to an IF Switch node. Create a flow variable connection from the Variable Expression node to the IF Switch node. In the IF Switch configuration window, under the Flow Variables tab, set PortChoice to the flow variable condition.

5. To the top output port of the IF Switch node, add a Row Filter node filtering records with Age up to 30.0 years old (hint: use the operator Less than or equal).

6. Add an End IF node, with the top input port connected to the output from the Row Filter node, and the bottom input port connected to the bottom output port of the IF Switch node.

*Hint: You can instruct K-AI to generate the necessary expression. Or use the expression:if($$["Mean(Age)"] <= 30, "top", "bottom")

Learning objective: In this exercise, you will learn how to control execution of workflows with loops and switches.

Workflow description: This workflow introduces key concepts in workflow control structures through several learning objectives:

• Repeat a process using a loop.

• Automate a process within a loop using flow variables.

• Combine outputs from loops.

• Conditionally execute workflow branches using IF and Case Switches.

• Control active ports in a switch with a flow variable.

You will find the instructions to the exercises in the yellow annotations.

Activity I: Write data from different cities to separate files using a loop

1. Start from the workflow from Activity II of 01_Flow Variable and Component - Exercise you completed earlier. Insert a Group Loop Start node before the Store with Highest Total Sale metanode. Iterate over different city names in the column City.

2. Create a path flow variable for the location and the name of the output Excel file. The filename is the name of the city, followed by the execution date&time.

   • Create a flow variable connection between the Group Loop Start node to the Date&Time Configuration node.

   • With Variable Expression node, combine the city name (flow variable City) with the execution date (flow variable Execution_time). Replace any colon (":") with a hyphen ("-")*. The resulting string is stored as a flow variable File_name.

   • In the Create File/Folder Variables node, use a workflow relative path to the data/Highest sales stores by cities folder as the location.

3. Write the records from the store with the highest total sale in the city of the current iteration to an Excel file

   • Propagate the output table from the Store with Highest Total Sale metanode to an Excel Writer node. It contains the records only from the city of the current iteration.

   • Create a flow variable connection from the Create File/Folder Variables node to the Excel Writer node. Set the output file path with the path flow variable output-location.

4. Add a Variable Loop End node, with a flow variable connection from Excel Writer node. You can try a step-by-step execution by pressing the Step button when hovering over this node. Or you can run all iterations by simply executing the Variable Loop End node.

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

Activity III: Use a CASE Switch to selectively activate only one of the branches of a workflow

1. Start from the workflow from Activity III of 01_Flow Variable and Component - Exercise you completed earlier. Open the Store Selection component.