Adjusting Overlapping Dates

[cbushey1]

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

[sunny_talwar]

What is the output you expect to see based on the data you have provided in your inline load?

[cbushey1]

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

[sunny_talwar · Accepted solution]

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;

[cbushey1]

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?

[sunny_talwar]

---

@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 🙂