Adding rows and setting missing values = 0

Hi, I would appreciate some help with the attached example.

I'm guessing that the multi-row formula or generate rows tools may be useful here, but I am uncertain about how to use them in this case:

On Sheet 1, Col A-D (Left) is the data that I have, and I want it to look like Col H-L (Right).

For each state, my data should contain the following periods (21 total) [basically we drop the last 4 periods for each year]:

2017 has periods: 2, 5, 8, 11, 14, 17, 20, 23, 26, 29, 32

2018 has periods: 2, 5, 8, 11, 14, 17, 20

2019 has periods: 2, 5, 8

On the left, my CA data is correct, but my FL data is missing 7 records.

I would like to add rows where applicable so that FL shows all 21 periods similar to CA, but the missing amounts are then set = 0.

Therefore, I want 42 records in total (like the right side): 21 for CA and 21 for FL

I then want a running total by state and accident year that looks like Column L

Thank you for the help with building a workflow that can solve this!

Alteryx Blanks Issue.xlsx

Preparation

Accepted answers

AbhilashR

Hi @jaydelim16, give the attached solution a try on your dataset. I use a combination of Summarize and Append tool to create an artificial universe of State/Year/Period combination, which is joined back to the original dataset to fill in the missing rows. The operation after that is pretty much driven by the power of Running Total tool.

Let us know if this isn't what you are looking for.

sample.yxmd

All comments

AbhilashR

Let us know if this isn't what you are looking for.

sample.yxmd

cplewis90

Hey @jaydelim16,

Here is one way to accomplish what you want. You can create a table that has all the required records and join it to the data you have and union what would be missing. The clean up the data, sort, and create your running total. I attached the workflow and a picture below.

running totals.PNG

EDIT: forgot to mention the two top inputs you would adjust for what is needed. The bottom is just the dataset in the range you start with.

running totals.yxmd

jaydelim16

Hi @cplewis90,

Thank you for this example. Because my dataset is actually much larger than that, creating the input tables with the required records before joining may be time consuming. But it is very helpful to see how you used the 3 different Join tools, as well as the Generate Rows tool! I learned a few things from your workflow and I thank you for that! 🙂

jaydelim16

Thank you @AbhilashR!

Your workflow does work on my dataset 🙂 One quick question - what is the purpose of the RecordID column that you have created? Is this part necessary?

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703