community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Generate one additional row after condition is met

Alteryx Partner

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!

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

[Count] < 100000
Alteryx Partner

@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!

@tcroberts You should submit a product idea! I have many a times wished that the generate rows tool would keep the row that breaks the condition as well.

Alteryx Partner

@patrick_digan

 

That's a good idea. I've posted it here if you'd like to vote on it.

Magnetar
Magnetar

This is relatively brittle (largely still tied to your use case), but it wouldn't be too much work to build out a tool with the Generate Rows functionality which had this capability using an Iterative Macro.

 

I've attached an example yxzp that shows an example for this specific use case, but I think you could pretty easily build one out which had the functionality to Add/Edit an existing field, and could apply for any condition and any loop.

Alteryx Partner

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 work2018-07-23 17_02_01-Window.png

Highlighted
Alteryx Partner

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

Alteryx Partner

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.

Labels