Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Announcement: Certain actions are currently causing a Page Not Found error. This is a known issue and we are working with the platform vendor to investigate and resolve it.
Highlighted
siobhancrossen
New Contributor II

IntervalMatch to assign a Time Period label to Event Times

Hi

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

Capture1.PNGthis is the format of the original table

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

Capture2.PNG

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

Capture3.PNG

 

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

Capture4.PNG

 

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

1 Reply
Support
Support

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

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

To help users find verified answers, please don't forget to mark a correct resolution or answer to your problem or question as correct.