Intervalmatch

[Anonymous]

Hi Gents,

I want to create a Intervalmatch with what I think is a text-string.

I have a field with the name "Start" from table "StartTable" and it contains minutes that counts from 00:00 (For example 10:00 is 600 minutes, 0200 is 120 minutes etc)

In the loading script I have already made a interval to convert it to hours (with a INTERVAL) but I guess I can't use that field in the loading script (?).

I have started to type a IntervalMatch..

TimeGroup:

LOAD * INLINE[

Start, Stop, TimeGroup

00:00(0), 06:00(360) >=00:00 < 06:00 (360 minutes)

06:01(361), 08:00(480) >=06:00 < 08:01 (481)

08:01 (481), 10:00 (600) >=08:01 <10:01 (601)

10:01 (601), 12:00 (720) >=10:01 <12:00 (720)

And further on...

Then the next step is to make a Interval Match

IntervalMatch (StartTable)

LOAD

     Start

Resident TimeGroup;

Load the data - > I will get a Synthetic key and then I type this to get rid of it:

IntervalMatch (StartTable)

Left Join (TimeGroup)

LOAD

     Start

Resident TimeGroup;

Load Data and end it with

Drop fields Start;

And then load data one last time.

So my question after this novel:

1) I have to convert the text-string to a number?

2) How do I write the TimeGroup table correctly so i get it in the format HH:MM? Can I use this and how?

(Interval("START"/24/60, 'hh:mm') as StartHours

3) Any more trouble I might get into?

Thanks in advance

[eduardo_dimperio]

Hi Bjorn (sory my kb doesn't have accent),

I don't understand what's you input and output.

You have a start time, end time and whant the time between?

I don't get it.

[Anonymous]

Hi Eduardo,

(No worries about the accent, Sweden have some downsides: High taxes, bad weather and it's accent..)

Look at it as a Marathon-race where the participants starts on different slots.
I want to be able to list how many that started between 0800-0900, 09:01-10:00 and so on.

Thanks again.

[juraj_misina]

Hello Bjorn,

you do not necessarily need to convert string to number, it is sufficient to use number interpretation function, Interval#(Start, 'hh:mm') in your case. As for IntervalMatch, I believe you need to provide to fields when loading. In fact, I'd do it in oposite direction compared to what you do. Plus, IntervalMatch takes a field name as an argument, therefore I would avoid using the same field name in StartTable and TimeGroup table. Having loaded the StartTable with Start field and TimeGroup table with StartTime and EndTime (instead of Start/End, just to avoid common field names), I'd continue as follows:

//attach interval fields to the Start field

IntervalMatch(Start)

Left Join (StartTable)

LOAD

StartTime,

EndTime

Resident TimeGroup;

//attach TimeGroup label to respective intervals

Left Join (StartTable)

LOAD * Resident TimeGroup;

//drop TimeGroup table to clean up

Drop Table TimeGroup;

Hope this helps.

Best

Juraj

[Anonymous]

Hi Juraj,

Thanks for taking your time on this.

What I have done now:

In StartTable I have renamed "START" to TimeStart.

Then I made a class-function to make intervals and to create the field TimeGroup (Class("START", 480, 'TimeStart') as TimeGroup,). I also used the replace-function to simplify the text output ('>= ' & REPLACE(CLASS("START", 480, 'TimeStart'), '<= TimeStart <', ' < ') AS TimeGroup)

Then i commented out this and created my intervals with several IFs

IF("START">=0 AND "START" <360,'>= 0 < 360',

    IF("START">=360 AND "START" <480,'>= 360 < 480',

        IF("START">=480 AND "START" <600,'>= 480 < 600',

            IF("START">=600 AND "START" <840,'>= 600 < 840',

                IF("START">=840 AND "START" <1080,'>= 840 < 1080',

                    IF("START">=1080 AND "START" <1440,'>= 1080 < 1440')))))) as TimeGroup,

When I was done here, I created my TimeGroup table

TimeGroup:

LOAD * INLINE [

Start1, Stop1, TimeGroup

0, 360, >= 0 < 360

360, 480, >= 360 < 480

480, 600, >= 480 < 600

600, 840, >= 600 < 840

840, 1000 >= 840 < 1000

1000, 1440 >= 1000 < 1440

];

No problems yet but now I meet the cavalry:

I get a circular reference and a synthetic key when I load the script below.

IntervalMatch (TimeStart)

Load

Start1,

Stop1

Resident TimeGroup

When I add the Left Join (TimeGroup) under IntervalMatch the circular reference disappeard, but the synthetic key is still there. Even after i drop the fields Start1 and Stop1.

So my first challenge is to remove the synthetic key and then my challenges are

How to get the minutes into HH:MM when i vizualize and som set-analysis-expression.

Thanks in advance!

[juraj_misina]

Bjorn,

I've got lost here. You create your TimeGroup via nested IFs - which is OK -  and at the same time you create yet another TimeGroup inline which you want then join via IntervalMatch?

Here's some very simple (but working) example for IntevalMatch use which may help you:

Tmp_Intervals:
LOAD * Inline [
ID,    Start,    End
A,    6,    8
B,    2,    15
C,    9,    20
D,    1,    8
E,    8,    15
F,    10,    15
G,    6,    9
H,    8,    9
];


Elements:
LOAD
    IterNo()    as Element
AutoGenerate 1 While IterNo()<20;

Intervals:
IntervalMatch(Element)
LOAD
    "Start",
    "End"
Resident Tmp_Intervals;

Left Join(Intervals)
LOAD
    ID,
    "Start",
    "End"
Resident Tmp_Intervals;

Drop Table Elements;

Getting minutes to hh:mm format is fairly simple. You only need to get the that number to a fraction of 1. So 360 minutes would be 360/60/24=0,25 (integer part=date/number of days, decimal part = time), so you can do Inteval(START/60/24, 'hh:mm').

Hope this helps.

Juraj

[Anonymous]

Hi Juraj.

Sorry for confusing you, I have only worked with Qlik since two months ago.

My goal is to create vizualizations where i can show how many started in the slots.

For example between 06:00-08:00 (360 - 480 min) started x people.

If you can see my picture I got my timeslots and how many ppl started in each timeslot.

If I understand you correctly - I do not need to create the TimeGroup Inline at all?

Thanks for your patience here!

Br

Björn

[juraj_misina · Accepted solution]

Hi Bjorn,

in this case you do not need inline time groups and IntervalMatch at all. See attached app.

BUT, you could use inline definition of time groups and IntervalMatch, because i think it's easier for maintainance than multiple nested IFs. Of course, depends on use case, data volume, etc.

Juraj !

[Anonymous]

Many thanks Juraj!