Find the date between start date and end date.

[amipanda]

when i am trying to do this below i am unable to do this, Please some one help me would be highly appreciated. I am attching the input data and output how its looks like,(2nd table).

Please help me,

[NMangera]

@amipanda 

STEPS:

1. convert 'Price' to date type format

2.  pivot the data so that the start and end date are on the same row

3. generate additional rows for each day between the start and end date

[martinding]

Hi @amipanda,

There are multiple ways to achieve this:

1. I would definitely recommend using the Generate Rows tool method, as it requires the least amount of tools. But it can be difficult to understand at first.

2. So a second approach is to use the TS (time series) Filler tool to automatically fill out the dates, but there may be additional steps involved...

Please find attached:

[amipanda]

Thanks for your help but i need the below type of data . Please help if anyone knows. Provided the data sheet below.

Input				Output
Key	Date	Price		Key	Date	Price
75	3/10/2023	1
75	3/15/2023	2		75	3/10/2023	1
75	3/18/2023	3		75	3/11/2023	1
76	4/8/2023	4		75	3/12/2023	1
76	4/10/2023	5		75	3/13/2023	1
76	4/15/2023	6		75	3/14/2023	1
				75	3/15/2023	2
				75	3/16/2023	2
				75	3/17/2023	2
				75	3/18/2023	3
				76	4/8/2023	4
				76	4/9/2023	4
				76	4/10/2023	5
				76	4/11/2023	5
				76	4/12/2023	5
				76	4/13/2023	5
				76	4/14/2023	5
				76	4/15/2023	6

[martinding]

Hi @amipanda,

Please see attached:

[Yoshiro_Fujimori]

Hi @amipanda ,

You can use Generate Rows tool.

Workflow

Formula

1. Convert the Date to Date type to use DateTime functions.

DateFrom = DateTimeParse([Date],"%m/%d/%Y")

2. Calculate the "To" dates with Multi-Row formula.

IF [Row+1:Key] = 0 THEN [DateFrom]
ELSE DateTimeAdd([Row+1:DateFrom],-1,"days")
ENDIF

3. Generate Row

Initialization Expression

[DateFrom]

Condition Expression

[Date2] <= [DateTo]

Loop Expression

DateTimeAdd([Date2],1,"days")

4. Format the date

Use DateTime functions as you like.

Output

I hope this matched with your requirement.