topic Time and Loops in Script in QlikView

Time and Loops in Script

JT1 — Sat, 16 Nov 2024 03:35:49 GMT

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!

Re: Time and Loops in Script

chrismarlow — Tue, 14 May 2019 07:53:01 GMT

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.

Re: Time and Loops in Script

JT1 — Mon, 20 May 2019 17:33:48 GMT

Thanks this works perfectly !