Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
JT1
Contributor II
Contributor II

Time and Loops in Script

Hi all,

Looking for some scripting help.  I have the following fields in a table (100 records)

OriginalTable:

ID | Date | StartTime | EndTime

1  | 4/1/2019 | 14:30:00 | 16:30:00

2  | 4/1/2019 | 15:25:10 | 15:55:27

3  | 4/1/2019 | 14:12:21 | 17:20:27

I need to loop through this table and calculate what percentage of each hour is occupied between StartTime and EndTime.

So for example the ID 1 in the original table will have 3 rows in the new table

NewTable:

ID | NewID | Hour | %Occupied

1 | 1-1 | 14 |  50%

1 | 1-2 | 15 |  100%

1 | 1-3 | 16 |  50%

Any help would be appreciated!

 

Labels (3)
1 Solution

Accepted Solutions
chrismarlow
Specialist II
Specialist II

Hi,

I do like an interval match problem … but I thought this was going to be easier than this 😀. Try the below;

data:
Load
	Timestamp(Date+StartTime-(1/24)) as StartDateTime,
	Timestamp(Date+EndTime) as EndDateTime,
	*
;
LOAD * INLINE [
ID , Date , StartTime , EndTime
1  , 4/1/2019, 14:30:00 , 16:30:00
2  , 4/1/2019 , 15:25:10 , 15:55:27
3 , 4/1/2019, 14:12:21 , 17:20:27
];


intervals:
Load
	IterNo() AS Interval,
     TimeStamp(DMin + ((IterNo()-2)/24)) as Hour
While DMin+((IterNo()-2)/24) <= DMax 
;
Load
	Min(RoundedDateStartTime) AS DMin,
	Max(RoundedDateEndTime)  AS DMax;
Load
	 Timestamp(Date+floor(StartTime, 1/24)) AS RoundedDateStartTime,
	 Timestamp(Date+floor(EndTime, 1/24)) AS RoundedDateEndTime
Resident data;


join IntervalMatch(Hour)
Load 
	StartDateTime,
	EndDateTime
resident data;

left join (data)
Load
	*
resident intervals;

drop table intervals;

data_final:
NoConcatenate
LOAD
	ID,
	ID2,
	Date,
	StartTime,
	EndTime,
	Hour,
	If(StartDateTime+(1/24)<Hour AND EndDateTime>Hour+(1/24),
		1,
		If(ID2=1,
			If(EndDateTime>Hour+(1/24),
				24*(Hour-StartDateTime),
				24*(EndDateTime-(StartDateTime+(1/24)))
				),				
			24*(EndDateTime-Hour)
			)
		) AS Proportion;
Load
	If(RowNo()=1,
		1,
		If(Peek('ID')=ID,
			Peek('ID2')+1,
			1
			)
		) as ID2,
	*	
Resident data
Order By ID, Hour;

drop table data;

Cheers,

Chris.

View solution in original post

2 Replies
chrismarlow
Specialist II
Specialist II

Hi,

I do like an interval match problem … but I thought this was going to be easier than this 😀. Try the below;

data:
Load
	Timestamp(Date+StartTime-(1/24)) as StartDateTime,
	Timestamp(Date+EndTime) as EndDateTime,
	*
;
LOAD * INLINE [
ID , Date , StartTime , EndTime
1  , 4/1/2019, 14:30:00 , 16:30:00
2  , 4/1/2019 , 15:25:10 , 15:55:27
3 , 4/1/2019, 14:12:21 , 17:20:27
];


intervals:
Load
	IterNo() AS Interval,
     TimeStamp(DMin + ((IterNo()-2)/24)) as Hour
While DMin+((IterNo()-2)/24) <= DMax 
;
Load
	Min(RoundedDateStartTime) AS DMin,
	Max(RoundedDateEndTime)  AS DMax;
Load
	 Timestamp(Date+floor(StartTime, 1/24)) AS RoundedDateStartTime,
	 Timestamp(Date+floor(EndTime, 1/24)) AS RoundedDateEndTime
Resident data;


join IntervalMatch(Hour)
Load 
	StartDateTime,
	EndDateTime
resident data;

left join (data)
Load
	*
resident intervals;

drop table intervals;

data_final:
NoConcatenate
LOAD
	ID,
	ID2,
	Date,
	StartTime,
	EndTime,
	Hour,
	If(StartDateTime+(1/24)<Hour AND EndDateTime>Hour+(1/24),
		1,
		If(ID2=1,
			If(EndDateTime>Hour+(1/24),
				24*(Hour-StartDateTime),
				24*(EndDateTime-(StartDateTime+(1/24)))
				),				
			24*(EndDateTime-Hour)
			)
		) AS Proportion;
Load
	If(RowNo()=1,
		1,
		If(Peek('ID')=ID,
			Peek('ID2')+1,
			1
			)
		) as ID2,
	*	
Resident data
Order By ID, Hour;

drop table data;

Cheers,

Chris.

View solution in original post

JT1
Contributor II
Contributor II
Author

Thanks this works perfectly !