Creating Date buckets on single date field

[Mohan23912391]

Hi,

I have a date column and value.. I need to create a start date, end date based on that date column and get the below data

	INPUT Data
	Date(MM.YYYY)	Value
	1.2022	100
	2.2022	70
	3.2022	80
	4.2022	80
	5.2022	90
	OUTPUT Data
Start date	End date	Value
1.2022	1.2022	100
2.2022	2.2022	70
3.2022	4.2022	80
5.2022	-	90

Please help me how can I get this in script ?

[vinieme12 · Accepted solution]

raw:
Load monthstart(Date#(Date,'M.YYYY')) as Date,Value Inline [
Date,Value
1.2022,100
2.2022,70
3.2022,80
4.2022,80
5.2022,90
];

temp:
Load *
,if(Value=Peek('Value'),peek('StartDate'),Date) as StartDate
Resident raw
ORder by Date,Value ASC;
drop table raw;

Main:
Load Value,Date(Max(Date)) as EndDate,Date(min(StartDate)) as StartDate Resident temp Group by Value;
Drop table temp;

exit Script;

Group by Value  << Ideally you need to be aggregating on an ID field