Generate Rows not producing final row

I am trying to use Generate Rows to produce the final data set shown below:

I am able to generate rows for 7/1/2014 - 4/22/2019 but I am unable to see why the final row (1/1/2020) will not be produced.

I have added a formula in my workflow to help breakdown the condition and loop expression results for the Generate Rows tool. The formula tool results seem to indicate Begin Period = 4/22/2019 will have Condition = True and Loop = 1/1/2020 but that is not what is happening.

Generate Rows Problem.yxmd

Preparation

Accepted answers

alisonpitt

Hi @tlewis I think the issue is that on the next row the condition will be FALSE, so it doesn't generate the row. I added an additional check to your MIN statement, to check against [Begin Period], which makes that last row TRUE, and makes it show up. It works in this case, hopefully it will work for the rest of what you're doing!

Spoiler

Generate Rows Problem - AP.yxmd

All comments

Luke_C

Hi @tlewis

That's quite the formula you have in there. I took a different approach that seems to get what you want:

Generate a row for each between the start and end
Add a tile tool (this will come in handy later on and help if you are processing multiple policies)
Apply your logic in the filter tool:
1. First record (based on tile tool)
2. Anniversary of policy
3. Jan 1st
4. Filters out the last record based on Study End
Populate End Period as 1 day less than the next row's begin period, unless it's the last record in which case we use 1 day less than the study end.

Generate Rows Problem.yxmd

alisonpitt

Spoiler

Generate Rows Problem - AP.yxmd

tlewis

Thanks, your explanation helped me realize I was associating the wrong Begin Period input in the condition expression. Once corrected, I was able to simplify the condition to be: [Study End] >[Begin Period].

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15874

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703