This week we focused on Alteryx in preparation for our core exam. This was one of the challenges that I attempted. Here is my walkthrough - Warning their is spoilers if your planning to attempt this challenge.

This challenge is broken up into 4 sections. So we will take this step by step. So first lets merge the records. So first lets input our data. For this you need list of sheet names rather then a particular name. Then we would do a dynamic input. This provides a command interface.

The next step is to reclassify the lighting conditions. What that means is using the second data set we want to find and replace. We basically want the lighting condition to take the reclassify information.

The following is what the find replace looks like.

Find within field looks at the first dataset.

Find value is joining the datasets with the common field- Lighting conditions.

Finally we wish to replace this with the reclassify column.

Task 3: create a date field using accident Date.

To do this we need to add the DateTime tool.

For this tool to work it is important you remember to type it as the format that is presented and not the format you want the outcome to be. Below is some examples of how you would write them in this tool.

Once doing this in the tool it produced the following:

Task 4:Create time buckets from the field "Time (24hr)" using the parameters below. Call the field "Time Buckets"

0000 - 0559 - Early Morning
0600 - 1159 - Morning
1200 - 1559 - Afternoon
1600 - 2059 - Evening
2100 - 2359 - Night

Above shows the time buckets in which we wish to categorize these into.

To create this we would do a formula tool with an if statement. This states that if this is a certain value then categorize as this and if not then we would use elseif and then for the following three categories- morning, afternoon and evening. For the final parameter we can just say else without any values needed, as all the other values would be categorized above. to end the statement we use endif. Shown below is the if statement used for this task.

After doing this task you can export your outcome. Finally below shows what my final workflow looks like.