topic Re: Adjusting Overlapping Dates in QlikView

Adjusting Overlapping Dates

cbushey1 — Sat, 16 Nov 2024 03:46:21 GMT

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

Re: Adjusting Overlapping Dates

sunny_talwar — Wed, 24 Apr 2019 13:55:46 GMT

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

Re: Adjusting Overlapping Dates

cbushey1 — Wed, 24 Apr 2019 14:08:02 GMT

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

Re: Adjusting Overlapping Dates

sunny_talwar — Wed, 24 Apr 2019 14:20:55 GMT

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;

Re: Adjusting Overlapping Dates

cbushey1 — Wed, 24 Apr 2019 14:41:42 GMT

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?

Re: Adjusting Overlapping Dates

sunny_talwar — Wed, 24 Apr 2019 14:55:24 GMT

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