Create duplicate rows

Question

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 -

ItemItem descCountryCompanyPSDPEDCurrencyPriceFast sprayFast spray 20 mlTBDNew01-01-200631-05-2012Local106Fast sprayFast spray 20 mlTBDNew01-06-201230-09-2013Local108Fast sprayFast spray 20 mlTBDNew01-10-201331-12-2013Local114

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 -

ItemItem descCountryCompanyPSDPEDCurrencyPriceFast sprayFast spray 20 mlTBDNew01-01-200631-12-2006Local106Fast sprayFast spray 20 mlTBDNew01-01-200731-12-2007Local106Fast sprayFast spray 20 mlTBDNew01-01-200831-12-2008Local106Fast sprayFast spray 20 mlTBDNew01-01-200931-12-2009Local106Fast sprayFast spray 20 mlTBDNew01-01-201031-12-2010Local106Fast sprayFast spray 20 mlTBDNew01-01-201131-12-2011Local106Fast sprayFast spray 20 mlTBDNew01-01-201231-05-2012Local106Fast sprayFast spray 20 mlTBDNew01-06-201231-12-2012Local108Fast sprayFast spray 20 mlTBDNew01-01-201330-09-2013Local108Fast sprayFast spray 20 mlTBDNew01-10-201331-12-2013Local114

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

echuong1 · Accepted Answer

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!