The Alteryx Weekly Challenge #102 looks at the hospitalisation rates for patients with symptoms of influenza. The task is to calculate the cumulative hospitalisation rate on an annual (seasonal) basis for each category.
The data seems to be prepared in a report format, and includes some descriptive/explanatory text in the first two rows. To get rid of those rows, we can use Select Records tool and type in ‘3+’ into the Ranges field to returns the records contained from row 3 onwards. Once we’ve done that, our column titles will appear in the first row. Let’s add the Dynamic Rename tool and set the mode to ‘Take Field Names from the First Row of Data’, which results in a more Alteryx-friendly table.
Next, we need to add a Select tool and change the Type of the WEEKLY RATE field from String to Double, as we will need to perform some numeric aggregations on it. Once this is done, we can add the Summarize tool and configure in the following way:
The CATCHMENT, MMWR-YEAR and MMRW-WEEK fields do not appear in the desired output, so we can ignore them from here onwards. Here’s what we should get:
To transform this table into the desired ‘wider’ format where each age group is represented in a separate column, we will need to use the Cross Tab tool. We will want to group the rates by season, use the values from the AGE CATEGORY column to create new column headers and use the values from the weekly rates column to populate those new columns.
The outcome will look like this:
We can then use the Select tool once again to get rid of the Null column and rename the newly generated fields to include the dash (e.g. 0-4 yr). Finally, we can use a simple filter tool to only keep the records where SEASON is not [Null].