Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
ALTER.NEXT:

Join us on Dec 2 for a half-day virtual analytics + data science event!
US & CA customers only

SAVE YOUR SPOT
It's the most wonderful time of the year - Santalytics 2020 is here! This year, Santa's workshop needs the help of the Alteryx Community to help get back on track, so head over to the Group Hub for all the info to get started!
SOLVED

Generate Rows newbie question

Highlighted
Alteryx Partner

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

Highlighted
Alteryx Certified Partner

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

Highlighted
Alteryx Partner

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 

Highlighted
Alteryx Certified Partner

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?

 

Highlighted
Alteryx Partner

Thank you Laura! now it makes much more sense.

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

Highlighted
Alteryx Partner

Thank you, Claje. This also do the trick!

Labels