community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE
SOLVED

Create new rows under condition

Highlighted
Asteroid

Hello guys,

 

i want to creat new rows into my table. The logic is: For each row look in column 2 (or cell 2) and compare it with the next row.

if its the same (for example "1" & "1" do nothing) if its not the same (for example "1" & "2") create new row between active/current row and next row.

 

i hope someone can help.

 

Input   Output  
A1Dec 2017 A1Dec 2017
A1Jan 2018 A1Jan 2018
A2Dec 2017 A1Feb 2018
A2Jan 2018 A2Dec 2017
A3Dec 2017 A2Jan 2018
A3Jan 2018 A2Feb 2018
B1Dec 2017 A3Dec 2017
B1Jan 2018 A3Jan 2018
    A3Feb 2018
    B1Dec 2017
    B1Jan 2018
    B1Feb 2018
Pulsar

Hi!

Started with a Multi-row formula tool to decide if a new row should be added or not (based on your description of the # in column 2 matching or not). That creates a "tag" column of Y or N

Filtered out for the Ys, and updated the date column with the following formula, to increase the month by one month. It's convoluted because you have to convert into a date, then back into a string.

DateTimeFormat(DateTimeAdd(DateTimeParse([Date],"%b %Y"),1,"month"),"%b %Y")

Finally, unioned the original data with the new rows. And there you have it!

image.pngBest, Esther

Alteryx Certified Partner

As with most things in Alteryx, there's a few ways to accomplish what you are trying to do.  Here are some solutions I came up with:

 

  1. The first option is to use the multi-row formula to create a flag for rows where column 2 is the same as the next row column 2, use that flag to generate new rows where needed, then use another multi-row formula to change the new row values to Feb 2018.
  2. The second example shows this same thing using date formulas for more flexibility.
  3. The third example creates a dummy field to allow for a cross tab.  From there you can just add a new column using a formula and transpose the "date" columns back to add the new row.

Hope this helps!

Asteroid

Thank you for your answers, it worked for me :)

Meteoroid

I have tried to use this solution for a somewhat similar problem.

I have data that looks like this: 

NameDateSummary Line
Joe Date 1empty
Joe Date2 empty
Joe Date3 This is summary Data

 

I would like it to look like this:

NameDate
Joe Date 1
Joe Date2 
Joe Date3 
This is summary Data

 

 

I would like the bottom line to stretch across the 2 columns. In other words, have a centered summary row. I tried to adapt your approach with no success. Do you know a way this is possible?

Thanks, J

Labels