topic Re: How to generate new rows from a formula? in Alteryx Designer Cloud Discussions

How to generate new rows from a formula?

IraWatt — Sun, 22 May 2022 20:32:19 GMT

If I have a dataset of people which has 1 row per person. how do I then write a formula to generate 3 rows for each person with values 1,2,3 for each person?

Re: How to generate new rows from a formula?

JWang_Tri — Mon, 23 May 2022 17:15:07 GMT

Hi Ira,

You can use the Unpivot transformation to achieve this. In short, Unpivot converts selected columns into rows.

Specifically, here's what you can do:

1- Add three columns in your recipe with the values 1, 2, and 3. Here, a column is created with value "1".

2- Use the Unpivot transformation to move the three newly-created columns into rows. Since you want to convert the three numbers into rows, select the three columns.

3- Remember to delete the "key" column if you don't want it, and rename the "value" column accordingly.

Re: How to generate new rows from a formula?

IraWatt — Mon, 23 May 2022 21:04:29 GMT

Awesome answer thanks!

Re: How to generate new rows from a formula?

IraWatt — Mon, 23 May 2022 21:04:39 GMT

Awesome answer thanks!

Re: How to generate new rows from a formula?

AMiller_Tri — Wed, 25 May 2022 13:36:02 GMT

Hi @Ira Watt? , thank you for your question!

While there's already an answer to your scenario, allow me to add another way to achieve the desired output.

This solution will also be scalable no matter how many records you want to create out of a single row - as it will always require only 2 steps.

The first step would be to use the RANGE() function:

This function returns an array of integers sequenced between two values by a step size

I've created an array that starts at 1 (inclusive) and ends at 4 (exclusive), with a step size of 1.

The second step would be to use the flatten transform on the array column; this can be done by clicking the columns' header and using Trifacta's suggestions:

The Flatten transform converts an array to rows so that each element is extracted to its own record.

So this is another way to add as many rows as needed for each record.

Notice that the original column value has the "to be dropped" notion in the preview; it will be replaced with the flattened column; meaning you don't need to delete the column afterwards.

I hope that makes sense; Please feel free to ask any additional questions!

Thanks,

Amit.