Help with creating transactions from values

[#1]

Dear everyone,

First post here.

I need help with a transform.

I have this:

ID	VALTIME	VALUE
1	2013-12-01	0,00
1	2014-01-01	1,00
1	2015-01-01	1,00
1	2016-01-01	0,80
1	2017-01-01	0,60

I want this:

ID	VALTIME	STARTDATE	ENDDATE	VALUE
1	2013-12-01	2013-12-01	2013-12-31	0,00
1	2014-01-01	2014-01-01	2014-12-31	1,00
1	2015-01-01	2015-01-01	2015-12-31	1,00
1	2016-01-01	2016-01-01	2016-12-31	0,80
1	2017-01-01	2017-01-01		0,60

In my case (electricity retailer) the ID is a delivery site and the values are some order information. I want to create a start- and enddate from the first table so I can run an interval-match and see what order is valid on a specific date. The enddate is the next rows startdate -1.

Please help!

Jakob

[anbu1984 · Accepted solution]

Temp:

Load ID,Date#(VALTIME,'YYYY-MM-DD') As VALTIME,VALUE Inline [

ID,VALTIME,VALUE

1,2013-12-01,0.00

1,2014-01-01,1.00

1,2015-01-01,1.00

1,2016-01-01,0.80

1,2017-01-01,0.60 ];

Final:

Load *,VALTIME As STARTDATE,If(ID = Previous(ID), Date(Previous(VALTIME)-1,'YYYY-MM-DD')) As ENDDATE Resident Temp Order by ID,VALTIME desc;

Drop Table Temp;

[#3]

Beautiful. Thank you!