Generate one additional row after condition is met

I'm using the Generate Rows tool to try and figure out when I've reached capacity of something. The value grows predictably, and so I use the Generate Rows tool to create one row for each growth step.

Is there a way to easily generate the row that breaks your condition. For example, if I set my condition to be:

[Count] < 50000

and my increment step to be:

[Count]*2

If I had started at 1000, then I'll jump from 32000 to 64000. I would like to keep this final row,

is it possible to do so using the Generate Rows tool similar to how I have it set up, or would I have to adjust my condition to be at that new level? Currently, I've just put a conditional statement in the loop expression, but this raises a warning on the last iteration, which I'd like to avoid.

Thanks in advance!

Error Message

Preparation

Iterative Macro

Accepted answers

patrick_digan

@tcroberts Can you change your condition to this and achieve your desired result?

[Count] < 100000

jwalder

How about a solution using math?

[Count] <= POW(2, CEIL(LOG10([Threshold]/[Start_Value])/LOG10(2)))*[Start_Value]

I used incoming column values, but you could use static values for [Threshold] and [Start_Value]. You could also put the increment in the formulas as a variable:
Condition: [Count] <= POW([n], CEIL(LOG10([Threshold]/[Start_Value])/LOG10([n])))*[Start_Value]

Loop Expression: [Count] * [n]

2018-07-23 18_24_49-Window.png

generate_records_plus_one.yxmd

All comments

patrick_digan

@tcroberts Can you change your condition to this and achieve your desired result?

[Count] < 100000

tcroberts

@patrick_digan

Hmm, yes I suppose that would work in the case where I'm always multiplying by 2, as anything above 50000 would be the 'final' row I'm looking for, and if it doubled it would necessarily be above 100000.

I suppose I was hoping for a more general answer that didn't require me to give a hard assignment to the condition, but this will work in the interim if I set the condition to 2*[MaxValue].

Thanks!

jwalder

You could sample for the last row of the generate rows output, apply your increment formula one more time and union that with the rest of the generate rows output. If order is important then just set that in the union. It means your formula is in 2 different places, but it will work 2018-07-23 17_02_01-Window.png

jwalder

How about a solution using math?

[Count] <= POW(2, CEIL(LOG10([Threshold]/[Start_Value])/LOG10(2)))*[Start_Value]

Loop Expression: [Count] * [n]

2018-07-23 18_24_49-Window.png

generate_records_plus_one.yxmd

tcroberts

Ooh I like this one. While I think the suggestion to create an iterative macro to do this in general is a little more practical for most users, this is more elegant in my opinion. Would still be nice if this was an option in the tool itself to avoid having to write some fairly tricky formulas for a relatively simple command.

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335