Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
Beautiful. Thank you!