IntervalMatch to assign a Time Period label to Event Times

siobhancrossen — Sat, 16 Nov 2024 04:10:14 GMT

I have a table with a series of 'Events', each of which has a time.

this is the format of the original table

I'm trying to assign a 'time period' to each row, eg

I'm hoping to end up with a table like the below:

I've been trying to use IntervalMatch to achieve this, but for some reason my results are as follows:

The script I am using is as follows

Time:
LOAD time#(Start_Time,'hh:mm:ss:fff') as Start_Time,
time#(End_Time,'hh:mm:ss:fff') as End_Time,
Time_Period_Group
FROM
[C:\Users\Time_Periods.xlsx]
(ooxml, embedded labels, table is Sheet1);

Main_Table:

LOAD Event,
time([Time],'hh:mm:ss:fff')as Time
FROM
[C:\Users\Events.xlsx]
(ooxml, embedded labels, table is Sheet1);

Inner Join IntervalMatch (Main_Table)
Load time#(Start_Time,'hh:mm:ss:fff') as Start_Time,
time#(End_Time,'hh:mm:ss:fff') as End_Time,
Time_Period_Group
Resident Time;

This is my first attempt at using IntervalMatch and no doubt there is some issue with the syntax - any help or advice would be highly appreciated!

Many thanks

Re: IntervalMatch to assign a Time Period label to Event Times

Brett_Bleess — Mon, 25 Mar 2019 21:11:48 GMT

Here is a post from Michael Tarallo that might be of some help, it has been a long while since I used this as well, so not sure I am going to be much help without really diving in and working things myself, and unfortunately I do not have the time to do that at the moment, so hopefully the following may help if you are still struggling:

https://community.qlik.com/t5/Qlik-Design-Blog/Using-IntervalMatch/ba-p/1475510

And here is post from Henric Cronstrom as well:

https://community.qlik.com/t5/Qlik-Design-Blog/IntervalMatch/ba-p/1464547

Regards,
Brett

topic IntervalMatch to assign a Time Period label to Event Times in QlikView

IntervalMatch to assign a Time Period label to Event Times

Re: IntervalMatch to assign a Time Period label to Event Times