Create new rows under condition

Question

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 2017A1Jan 2018 A1Jan 2018A2Dec 2017 A1Feb 2018A2Jan 2018 A2Dec 2017A3Dec 2017 A2Jan 2018A3Jan 2018 A2Feb 2018B1Dec 2017 A3Dec 2017B1Jan 2018 A3Jan 2018    A3Feb 2018    B1Dec 2017    B1Jan 2018    B1Feb 2018

estherb47 · Accepted Answer

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 Answer

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:

* 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.
* The second example shows this same thing using date formulas for more flexibility.
* 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!