This step-by-step guide is a continuation from the previous post about Alteryx Weekly Challenge 50 which can be found in the following link:

The above link contains the challenge description, alongside with the existing and improvised solutions. In this post, the detailed steps in creating the improvised solution are provided as below.

Step 1. Understand the requirements and start selecting row

As the challenge only wants to elaborate details from Question number 38, filtering column 38 from the Questions input file was done to reduce the workflow making time. Figure 1-1 shows the configuration of filter tool in selecting Column 38.

Figure 1-1. Configuration of Filter tool (Filter Column=38)

Step 2. Evaluate data and adjust field values

From the Data input file, it was observed that column F38 contains a combination of values that represent age and rating. Hence, column F38 was split into 2 columns using a ‘Text to Columns’ tool to organize the data. Figure 2-1 shows the configuration of the tool Split Column F38. Meanwhile, figure 2-2 shows column F38 before and after splitting.

Figure 2-1. Configuration of ‘Text to Columns’ tool to split column F38



Figure 2-2. Column F38 (a) before splitting; and (b) after splitting

Step 3. Rename first 3 columns according to the first row

The column header names ‘RespondentId’, ‘Date’ and ‘Time’ which is contained in the desired output can be found in the first rows in Data input file. Hence, a configuration of Dynamic Rename tool to ‘Take Field Names from First Row of Data’ was applied to set the first 3 column header names. Figure 3-1 shows the configuration of the Dynamic Rename tool.

Figure 3-1. Configuration of Dynamic Rename tool for the first 3 columns

Step 4. Select only relevant fields

As only 5 columns are required in the desired output, select tool was applied to select the relevant fields. The selected columns include ‘RespondentId’, ‘Date’, ‘Time’, F381 (renamed to 38) and F382 (renamed to AgeGroup). Figure 4-1 shows the configuration to select relevant fields.

Figure 4-1. Configuration of Select tool to select 5 relevant fields

Step 5. Apply Dynamic Rename to rating response column

Response column titled ‘38’ was renamed to ‘Please tell us about your experience with the site’s search feature today. (Please select all that apply.)’. This method was executed using ‘Take Field Names from Right Input Rows’ as the Rename Mode, setting Old Field Name from Column ‘Column’ and New Field Name from Column ‘Text’. Figure 5-1 shows the configuration to rename response column.

Figure 5-1. Configuration of Dynamic Rename tool to change name of Column ‘38’

Step 6. Prepare time data for sorting

To ensure that the data can be consistently sorted based on date and time, time data was prepared. Preparation of time data was conducted by converting from String to Date/Time format using DateTime parsing tool. Figure 6-1 shows the configuration of the DateTime parsing tool.

Figure 6-1. Configuration of DateTime parsing tool to convert ‘Time’ field

Step 7. Sort data based on Date and Time fields

As shown in figure 7-1, the fields ‘Date’ and ‘DateTime_Out’ were selected to sort data based on Date and Time in ascending order.

Figure 7-1. Configuration of Sort tool to sort data based on Date and Time

Step 8. Finalising output and adjusting arrangements

In this workflow, output finalisation was conducted through using another Select tool to ensure that the final result consistently matches the desired output. Finally, the final workflow was evaluated to ensure that the proper workflow structure has been applied. Figure 8-1 shows the configuration of the Select tool. Meanwhile, figure 8-2 and figure 8-3 illustrate the first 8 rows of the final result and final workflow arrangement, respectively.

Figure 8-1. Configuration of Select tool to finalise result

Figure 8-2. Illustration of the first 8 rows of the final result

Figure 8-3. Illustration of the final workflow arrangement

Kristiadi Uisan
Author: Kristiadi Uisan