Want to create duplicate records for mutiple years

[Pramod91]

Hello everyone,

I have a data sample like this:- 

Item	Item desc	Country	Company	PSD	PED	Currency	Price
Fast spray	Fast spray 20 ml	TBD	New	13-11-2013	08-01-2014	Local	1,693
Fast spray	Fast spray 20 ml	TBD	New	09-01-2014	22-05-2014	Local	1,811

It has two prices for an item between 13-11-2013 to 22-05-2014. I want is to show the price change on a graph with years 2011 to 2021 on X axis. For that I created duplicate records with price points for all the years from 2011 to 2021 like this :-

Item	Item desc	Country	Company	PSD	PED	Currency	Price
Fast spray	Fast spray 20 ml	TBD	New	01-01-2011	31-12-2011	Local	-
Fast spray	Fast spray 20 ml	TBD	New	01-01-2012	12-11-2013	Local	-
Fast spray	Fast spray 20 ml	TBD	New	13-11-2013	08-01-2014	Local	1,693
Fast spray	Fast spray 20 ml	TBD	New	09-01-2014	22-05-2014	Local	1,811
Fast spray	Fast spray 20 ml	TBD	New	23-05-2014	31-12-2014	Local	1,811
Fast spray	Fast spray 20 ml	TBD	New	16-10-2014	31-12-2014	Local	1,811
Fast spray	Fast spray 20 ml	TBD	New	01-01-2015	31-05-2015	Local	1,811
Fast spray	Fast spray 20 ml	TBD	New	01-06-2015	31-12-2015	Local	1,811
Fast spray	Fast spray 20 ml	TBD	New	01-01-2016	30-12-2016	Local	1,811
Fast spray	Fast spray 20 ml	TBD	New	31-12-2016	28-12-2017	Local	1,811
Fast spray	Fast spray 20 ml	TBD	New	29-12-2017	31-12-2017	Local	1,811
Fast spray	Fast spray 20 ml	TBD	New	01-01-2018	31-12-2018	Local	1,811
Fast spray	Fast spray 20 ml	TBD	New	01-01-2019	31-12-2019	Local	1,811
Fast spray	Fast spray 20 ml	TBD	New	01-01-2020	31-12-2020	Local	1,811
Fast spray	Fast spray 20 ml	TBD	New	01-01-2021	-	Local	1,811

Can someone help me find a way to create this type of duplicate records using alteryx?? I will have more Items in the dataset with different prices and dates for which I need to create such duplicate records. Can I do this in alteryx?

[Qiu]

@Pramod91 
Can you explain your logic for expanding the dates?

I failed to see a pattern in your output data.

[Pramod91]

@Qiu 

Thanks for your reply. I noticed that I did entered inconsistent dates. Here is the actual data set after expanding and below is the logic behind it:-

Item	Item desc	Country	Company	PSD	PED	Currency	Price
Fast spray	Fast spray 20 ml	TBD	New	01-01-2011	31-12-2011	Local	-
Fast spray	Fast spray 20 ml	TBD	New	01-01-2012	12-11-2013	Local	-
Fast spray	Fast spray 20 ml	TBD	New	13-11-2013	08-01-2014	Local	1,693
Fast spray	Fast spray 20 ml	TBD	New	09-01-2014	22-05-2014	Local	1,811
Fast spray	Fast spray 20 ml	TBD	New	23-05-2014	31-12-2014	Local	1,811
Fast spray	Fast spray 20 ml	TBD	New	01-01-2015	31-12-2015	Local	1,811
Fast spray	Fast spray 20 ml	TBD	New	01-01-2016	31-12-2016	Local	1,811
Fast spray	Fast spray 20 ml	TBD	New	01-01-2017	31-12-2017	Local	1,811
Fast spray	Fast spray 20 ml	TBD	New	01-01-2018	31-12-2018	Local	1,811
Fast spray	Fast spray 20 ml	TBD	New	01-01-2019	31-12-2019	Local	1,811
Fast spray	Fast spray 20 ml	TBD	New	01-01-2020	31-12-2020	Local	1,811
Fast spray	Fast spray 20 ml	TBD	New	01-01-2021	-	Local	1,811

I simple expanded the dates from 01-01-2011 till 12-11-2013 (a day before the actual price start date), and kept the price as "-" because the prices are not known before the actual price date. Then kept the two actual Price start dates with actual known prices from 13-11-2013 till 22-05-2014 (a price change had happened on 09-01-2014). And after that, duplicated the record with PSD (23-05-2014) of a day after actual PED till end of the year, i.e. 31/12/2014. And then, for each year from 2015 till 2021 assuming that price didn't changed. 

[AngelosPachis · Accepted solution]

Hi @Pramod91 ,

Following the logic you explained in your previous post, I've created a workflow that I believe answers your question.

Please take the time to review it and let me know if it works for you or if you have any questions on the workflow itself.

Regards,

Angelos