Create new rows under condition

[df]

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
A	1	Dec 2017		A	1	Dec 2017
A	1	Jan 2018		A	1	Jan 2018
A	2	Dec 2017		A	1	Feb 2018
A	2	Jan 2018		A	2	Dec 2017
A	3	Dec 2017		A	2	Jan 2018
A	3	Jan 2018		A	2	Feb 2018
B	1	Dec 2017		A	3	Dec 2017
B	1	Jan 2018		A	3	Jan 2018
				A	3	Feb 2018
				B	1	Dec 2017
				B	1	Jan 2018
				B	1	Feb 2018

[estherb47 · Accepted solution]

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!

Best, Esther

[gsaval · Accepted solution]

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!

[df]

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

[jsadove]

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

I have data that looks like this: 

Name	Date	Summary Line
Joe	Date 1	empty
Joe	Date2	empty
Joe	Date3	This is summary Data

I would like it to look like this:

Name	Date
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