Help using generate rows tool to only add a row if a number appears in another list.

[Charlieswims]

Basically, I need to manipulate the below data set to add rows for each number in the defined range (indicated by the period).

column 1       column 2               column 3

1000              67401.67499        500000.699999

So as an example the output should be something like:

1001-67401-500000

1001-67401-500001

1001-67401-500002

*repeating until 1001-67401-699999*

Then we start with the second number in column 2:

1001-67402-500000

1001-67402-500001

*and so on*

So I have a way to accomplish this already using a Regex expression that looks at a specific field and creates 2 new fields at the end for the start and end of the range and then added in another column that keeps a running track of what number should replace that  column on each line item. The problem is when I apply this logic to my large dataset it blows up the data far too much. I have a list of all true combinations of the three fields so I am wondering how I can manipulate the add rows tool or otherwise to only add a row if it shows up in this other file (ex. lets say if you were to look in my other file you would see 1001-67401-500000 and 1001-67401-500001 are real combinations but not anything between 1001-67401-500001 and 1001-67401-699995).

[KilianL]

Hi @Charlieswims ,

If I understand your challenge correctly, it might be easier to take the existing list from the other file and start from there - no need to generate it from scratch.

If you have more items in there than you want to generate, you can filter out the ones which don't fit in the defined range provided.

If you have multiple ranges to check, you can generate the filter expression in Alteryx dynamically and pass it to the filter via a Batch Macro. You can find an example of this attached.

...this was a nice exercise and shows how flexible Alteryx can be. You need the batch macro to update the filter expression dynamically, as this does not work directly by using the field itself.

Spoiler

Workflow


-----------------------------------------

Macro

Hope this helps!

Let me know if this doesn't work, and if it does, please mark this as the solution to help others find solutions quicker.

Kind Regards,
Kilian
Solutions Engineer - Alteryx