Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Generate one additional row after condition is met

tcroberts
12 - Quasar

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!

8 REPLIES 8
patrick_digan
17 - Castor
17 - Castor

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

[Count] < 100000
tcroberts
12 - Quasar

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

patrick_digan
17 - Castor
17 - Castor

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

tcroberts
12 - Quasar

@patrick_digan

 

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

Claje
14 - 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.

jwalder
10 - Fireball

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

jwalder
10 - Fireball

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

tcroberts
12 - Quasar

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