topic Re: Populate values between start date and end date in QlikView

Populate values between start date and end date

Dayna — Tue, 18 Apr 2017 11:08:41 GMT

Hello All!

I have a small problem with one of my reports, rather than using IntervalMatch I need to populate the values between the start and end for each record. I have attached an example report to show the set of data I'm currently using.

Ideally, I'd like the complete hours populated between the start_timestamp and end_timestamp, i.e.

This is my record at the moment:

id	start_timestamp	end_timestamp
5	29/03/2017 15:53	30/03/2017 07:19

And I'd like to show:

id	start_timestamp	timestamp	end_timestamp
5	29/03/2017 15:53	29/03/2017 15:00	30/03/2017 07:19
5	29/03/2017 15:53	29/03/2017 16:00	30/03/2017 07:19
5	29/03/2017 15:53	29/03/2017 17:00	30/03/2017 07:19
5	29/03/2017 15:53	29/03/2017 18:00	30/03/2017 07:19
5	29/03/2017 15:53	29/03/2017 19:00	30/03/2017 07:19
5	29/03/2017 15:53	29/03/2017 20:00	30/03/2017 07:19
5	29/03/2017 15:53	29/03/2017 21:00	30/03/2017 07:19
5	29/03/2017 15:53	29/03/2017 22:00	30/03/2017 07:19
5	29/03/2017 15:53	29/03/2017 23:00	30/03/2017 07:19
5	29/03/2017 15:53	30/03/2017 00:00	30/03/2017 07:19
5	29/03/2017 15:53	30/03/2017 01:00	30/03/2017 07:19
5	29/03/2017 15:53	30/03/2017 02:00	30/03/2017 07:19
5	29/03/2017 15:53	30/03/2017 03:00	30/03/2017 07:19
5	29/03/2017 15:53	30/03/2017 04:00	30/03/2017 07:19
5	29/03/2017 15:53	30/03/2017 05:00	30/03/2017 07:19
5	29/03/2017 15:53	30/03/2017 06:00	30/03/2017 07:19
5	29/03/2017 15:53	30/03/2017 07:00	30/03/2017 07:19

Can someone help me, please?

Re: Populate values between start date and end date

tresB — Tue, 18 Apr 2017 11:23:02 GMT

May be like:

=Interval(Timestamp#( end_timestamp,'DD/MM/YYY hh:mm')-Timestamp#( start_timestamp,'DD/MM/YYY hh:mm'),'hh:mm:ss')

Re: Populate values between start date and end date

Dayna — Tue, 18 Apr 2017 11:35:00 GMT

That only seems to give a value between those dates, rather than populating missing values? Isn't it more like a cartesian timeframe?

Re: Populate values between start date and end date

tresB — Tue, 18 Apr 2017 11:55:20 GMT

Well, may be this then ?

Load

*,

Timestamp(Floor(start_timestamp, 1/24)+IterNo()*1/24) as timestamp

While Floor(start_timestamp, 1/24)+(IterNo()-1)*1/24 <=end_timestamp;

load

Timestamp#(start_timestamp,'DD/MM/YYY hh:mm') as start_timestamp,

Timestamp#(end_timestamp,'DD/MM/YYY hh:mm') as end_timestamp

Inline [

id, start_timestamp, end_timestamp

5, 29/03/2017 15:53, 30/03/2017 07:19]

Re: Populate values between start date and end date

Dayna — Tue, 18 Apr 2017 12:32:53 GMT

You're an absolute star.. thank you!