Create duplicate rows

[Pramod91]

Hello again,

While working on a workflow I am stuck again while creating duplicate records using the "Generate rows" tool. I have a dataset like this - 

Item	Item desc	Country	Company	PSD	PED	Currency	Price
Fast spray	Fast spray 20 ml	TBD	New	01-01-2006	31-05-2012	Local	106
Fast spray	Fast spray 20 ml	TBD	New	01-06-2012	30-09-2013	Local	108
Fast spray	Fast spray 20 ml	TBD	New	01-10-2013	31-12-2013	Local	114

I want to create rows with PSD of all the years starting from the earliest PSD till latest PSD. And, if for any year, there are two prices for the item then there should be two rows for that year (here, the item has two prices in 2012 and 2013). Given the above data, the output should be like this -  

Item	Item desc	Country	Company	PSD	PED	Currency	Price
Fast spray	Fast spray 20 ml	TBD	New	01-01-2006	31-12-2006	Local	106
Fast spray	Fast spray 20 ml	TBD	New	01-01-2007	31-12-2007	Local	106
Fast spray	Fast spray 20 ml	TBD	New	01-01-2008	31-12-2008	Local	106
Fast spray	Fast spray 20 ml	TBD	New	01-01-2009	31-12-2009	Local	106
Fast spray	Fast spray 20 ml	TBD	New	01-01-2010	31-12-2010	Local	106
Fast spray	Fast spray 20 ml	TBD	New	01-01-2011	31-12-2011	Local	106
Fast spray	Fast spray 20 ml	TBD	New	01-01-2012	31-05-2012	Local	106
Fast spray	Fast spray 20 ml	TBD	New	01-06-2012	31-12-2012	Local	108
Fast spray	Fast spray 20 ml	TBD	New	01-01-2013	30-09-2013	Local	108
Fast spray	Fast spray 20 ml	TBD	New	01-10-2013	31-12-2013	Local	114

I am scratching my head to tackle this. Can someone please help?

[echuong1 · Accepted solution]

There are other ways of doing this, but here is what I came up with.

I started off by using DateTime tools to convert your values to alteryx recognized dates. I used a formula to calculate the EOY value per PSD. I then used a generate rows to iterate through and add a row where the EOY was less than the PED. I would later use a formula to decide which value to use for PED - the EOY value or the actual PED value (for the last row). I used a series of record ID, sorting, and multi-row formulas to determine this. Anything that would have been the last row, and would need the PED value to be left as is would have a 1 for the group ID. I used a formula to adjust these, and a select to remove the extra fields created.

Hope this helps!