Adding rows and setting missing values = 0

[jaydelim16]

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!

[AbhilashR · Accepted solution]

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.

[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. 

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. 

[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?

[AbhilashR]

In hindsight you don't need the RecordID tool in my approach. I was initially thinking of using it to number and sort the universe but later chose to use the individual components of State, Year, Period. I forgot to remove it before I responded to your post, sorry.