Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi there.
I am trying to figure out how to adjust my dates that are overlapping.
Events: LOAD * INLINE[ ID,Start,End,DaysSupply 1500,2018-01-02,2018-02-01,30 2432,2018-01-31,2018-03-02,30 5322,2018-03-01,2018-05-30,90 5777,2018-05-16,2018-08-14,90 ] Temp_adj_Dates: LOAD ID , DaysSupply , If(Start < Previous(End),Date(Previous(End)+1,'yyyy-mm-dd'),Start) as Start , If(Start < Previous(End),Date(Previous(End)+1 + DaysSupply, 'yyyy-MM-dd'),End) as End RESIDENT Event ORDER BY ID,Start;
The above is what I have tried, but it doesnt quite work.
* Let me also add that Start plus DaysSupply = End
Try this
Events: LOAD * INLINE [ ID, Start, End, DaysSupply 1500, 2018-01-02, 2018-02-01, 30 2432, 2018-01-31, 2018-03-02, 30 5322, 2018-03-01, 2018-05-30, 90 5777, 2018-05-16, 2018-08-14, 90 ]; Temp_adj_Dates: NoConcatenate LOAD ID, Date(If(RowNo() = 1, Start, If(Start < Peek('End'), Peek('End') + 1, Start))) as Start, Date(If(RowNo() = 1, End, If(Start < Peek('End'), End + Peek('End') + 1 - Start, End))) as End, DaysSupply Resident Events Order By ID, Start; DROP Table Events;
What is the output you expect to see based on the data you have provided in your inline load?
I would expect the dates to have shifted.
So I would want to see the following:
ID,Start,End
1500,2018-01-02,2018-02-01,30
2432,2018-02-02,2018-03-04,30
5322,2018-03-05,2018-06-03,90
5777,2018-06-04,2018-09-02,90
Try this
Events: LOAD * INLINE [ ID, Start, End, DaysSupply 1500, 2018-01-02, 2018-02-01, 30 2432, 2018-01-31, 2018-03-02, 30 5322, 2018-03-01, 2018-05-30, 90 5777, 2018-05-16, 2018-08-14, 90 ]; Temp_adj_Dates: NoConcatenate LOAD ID, Date(If(RowNo() = 1, Start, If(Start < Peek('End'), Peek('End') + 1, Start))) as Start, Date(If(RowNo() = 1, End, If(Start < Peek('End'), End + Peek('End') + 1 - Start, End))) as End, DaysSupply Resident Events Order By ID, Start; DROP Table Events;
Thank you that got me where I needed to be.
Why did you choose to use Peek instead of Previous? Does Peek look at the new calculated value, where previous looks at the pre-calculated value?
@cbushey1 wrote:Why did you choose to use Peek instead of Previous? Does Peek look at the new calculated value, where previous looks at the pre-calculated value?
Yup 🙂