iterate through Database rows

[sidatms]

Hi All,

I am new to Alteryx and trying to implement simple use case where i have to read database then make post call for each database row.

I am struggling to iterate through each row. Any idea how can i achieve that? 

[rzdodson]

@sidatms this should help you out. You are wanting to create an iterative macro where each row number becomes the iteration we are looping through.

Once you bring in your data set, you are going to want to create a row number field - you can do this with either a RecordID tool, or in some cases a Generate Rows tool. From there, you are going to want to establish an iterative macro where we test that RecordID against the Engine.Iteration number (see below).

From there, apply a Filter tool to your incoming data set where you are testing the Iteration. Engine.IterationNumber as a variable always begins at 0. Since we don't have iterations where records have a 0 for its iteration, we add 1 to the Engine.IterationNumber. As a result, we ask for the Filter "Can you return records where 1 = 1, 2 = 2, 3=3, etc. as we progress through the number of iterations in the macro?" All instances where the Iteration field does not match the current iteration number we are on get dropped out of the False anchor of the Filter tool; all records that do match our Filter criteria are passed through the True anchor of the Filter tool.

After completing that necessary step for the iterative macro, you are then needing to finish setting up your iterative macro. 

[sidatms]

Thanks for your response. Even though database query is selecting 10 rows, generate rows is generating 100 rows instead of 10. 

[rzdodson]

The above example workflow is intended for demonstration purposes *only* to show the set up of an iterative macro. 

The 10 value inside the Generate Rows was intended to establish a generic iteration number. That iteration number will presumably be the number of rows in your unique data set.

[sidatms]

Thanks for the quick response. Yes understood. However, i am confused because i am selecting 10 rows from the database and then using Generate Rows to Generate 10 rows but instead it's repeating the original dataset 10 times and Generating 100 rows.

[sidatms]

Please see the screenshot

[rzdodson · Accepted solution]

I see what is going on. The Generate Rows tool that I provided in the initial response was for a very specific use case where I started out with one record and turned it in to many so I show you the iterative macro build process.

Swap out your Generate Rows for a RecordID tool - it is in the Preparation palette. You do not need Generate Rows in your specific use case because you are not needing to create multiple instances of each record in your data set. The column that is created from your RecordID will become what you test against the Engine.IterationNumber+1 value in the iterative macro.

[sidatms]

Thanks a ton for all your assistance.