ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now

Alteryx Designer Discussions

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

Generate Rows newbie question

DiegoParker
10 - Fireball

Hi, I have been using Alteryx for a week now and this is my first post here. This may be a relatively easy question but I couldn't solve it myself.

 

I'm trying to create two columns, with 10,000 rows:

- The first column goes from 1 to 100, with each number repeating itself 100 times.

- The second column has a sequence of numbers from 1-100 a hundred times.

 

 

Example:

 

1 1

1 2

1 3

2 1

2 2

2 3

3 1

3 2

3 3

 

So, instead of 1 to 3, I would like to do it from 1 to 100. I managed to do the second column using generate rows, but I don't know how to do the first one.

 

Thanks in advance!!

Laurap1228
9 - Comet

Hi Diego,

 

You were on the right track. Check out the example workflow attached. I added a multi-row formula tool to get 1-100 repeated over and over again Column2. Then sort Cloumn2, rename it Column1 add a RecordID and join back to the original.

Diego workflow.PNG

Hope this helps!

 

Cheers,

Laura

DiegoParker
10 - Fireball

Thank you Laura! This is great, exactly what I was looking for. Because it has been just two weeks at the school, I'm struggling to understand fully what the first formula is doing. Would you mind explaining me? thank you a lot 

Laurap1228
9 - Comet

No problem! The Multi-Row Formula tool is great because it allows you to look up and down at other values within a column to calculate the result.

IF [Row-1:Column2]=100 THEN 1 ELSE [Row-1:Column2]+1 ENDIF

In plain English means if the value of the row above is 100 then start the count over again at 1 otherwise take the value in the row above and add 1. Since there is no 'row above' the first row, (row above + 1) equals 1. 

 

Does that make sense?

 

DiegoParker
10 - Fireball

Thank you Laura! now it makes much more sense.

Claje
14 - Magnetar

Another option for this request is to use two Generate Row tools, each to generate a column with 100 values, in sequence.


I've attached an example of this, using the same template as the initial example.

 

The generate Rows tool will process for each initial row of data fed into it, so you can easily create recordsets like the one you were looking for.

DiegoParker
10 - Fireball

Thank you, Claje. This also do the trick!

Labels