Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

cerezantonio
New Contributor

Match events with intervals of time

Hello to everyone.

I'm quite new to Qlik Sense and I need some help to match discrete events with an interval of time.

I have these two tables:


Sales:

Floor, DateTime, Sales, NuLine
1, '23/11/2017 00:01', '105,41', 1
1, '23/11/2017 07:28', '120,45', 2
1, '23/11/2017 09:10', '138,54', 3
2, '23/11/2017 19:28', '122,68', 4
3, '23/11/2017 21:26', '17,52', 5
2, '23/11/2017 23:58', '157,83', 6
2, '24/11/2017 00:06', '149,16', 7
3, '24/11/2017 00:47', '180,82', 8
3, '24/11/2017 01:34', '86,47', 9
1, '24/11/2017 01:54', '101,94', 10

Managers:
FloorM, Manager, StartTime, EndTime, NoLine
1, manolo, '23/11/2017 08:00', '23/11/2017 15:59', 1
1, sara, '23/11/2017 16:00', '23/11/2017 23:59', 2
2, pedro, '23/11/2017 00:00', '23/11/2017 07:59', 3
3, jose, '24/11/2017 08:00', '24/11/2017 15:59', 4
3, sara, '24/11/2017 16:00', '24/11/2017 23:59', 5

I need to know how much money is made during the shifts of every floor manager.

I've tried to use IntervalMatch but I haven't found an example that depends on some other field like Floor.

I've discarded using a 'minutes' table to match every Sale with a Manager because it was too much millions rows of data per month.

Finally, I have also tried to make this loop but it doesn't work as the "peek()" functions on the "let pasa = if(...)' always have 'null' value:

for each nul in fieldValueList('NuLine')
    let para = 0;
    let i = 0;
    do while para = 0
          let i = i + 1;
          let para =
              if(peek('Floor', $(nul), Sales) = peek('FloorM', $(i) - 1, Managers),
                  if(peek('DateTime', $(nul), Sales) >= peek('StartTime', $(i) - 1, Managers)
                  and peek('DateTime', $(nul), Sales) <= peek('StartTime', $(i), Managers), '1', '0')
              , '0');
    loop
    Matched:
    Load
          '$(nul)' as NuLinea,
          '$(i)' as NoLinea;
next nul;

Could anyone help me with this?

1 Solution

Accepted Solutions
MVP
MVP

Re: Match events with intervals of time

Have you checked the Extended Interval Match?

IntervalMatch (Extended Syntax) ‒ QlikView

Somehting like this

Sales:

LOAD * INLINE [

    Floor, DateTime, Sales, NuLine

    1, 23/11/2017 00:01, "105,41", 1

    1, 23/11/2017 07:28, "120,45", 2

    1, 23/11/2017 09:10, "138,54", 3

    2, 23/11/2017 19:28, "122,68", 4

    3, 23/11/2017 21:26, "17,52", 5

    2, 23/11/2017 23:58, "157,83", 6

    2, 24/11/2017 00:06, "149,16", 7

    3, 24/11/2017 00:47, "180,82", 8

    3, 24/11/2017 01:34, "86,47", 9

    1, 24/11/2017 01:54, "101,94", 10

];

Managers:

LOAD * INLINE [

    Floor, Manager, StartTime, EndTime, NoLine

    1, manolo, 23/11/2017 08:00, 23/11/2017 15:59, 1

    1, sara, 23/11/2017 16:00, 23/11/2017 23:59, 2

    2, pedro, 23/11/2017 00:00, 23/11/2017 07:59, 3

    3, jose, 24/11/2017 08:00, 24/11/2017 15:59, 4

    3, sara, 24/11/2017 16:00, 24/11/2017 23:59, 5

];

Left Join (Sales)

IntervalMatch(DateTime, Floor)

LOAD StartTime,

EndTime,

    Floor

Resident Managers;

Left Join (Sales)

LOAD *

Resident Managers;

DROP Table Managers;

3 Replies
MVP
MVP

Re: Match events with intervals of time

Have you checked the Extended Interval Match?

IntervalMatch (Extended Syntax) ‒ QlikView

Somehting like this

Sales:

LOAD * INLINE [

    Floor, DateTime, Sales, NuLine

    1, 23/11/2017 00:01, "105,41", 1

    1, 23/11/2017 07:28, "120,45", 2

    1, 23/11/2017 09:10, "138,54", 3

    2, 23/11/2017 19:28, "122,68", 4

    3, 23/11/2017 21:26, "17,52", 5

    2, 23/11/2017 23:58, "157,83", 6

    2, 24/11/2017 00:06, "149,16", 7

    3, 24/11/2017 00:47, "180,82", 8

    3, 24/11/2017 01:34, "86,47", 9

    1, 24/11/2017 01:54, "101,94", 10

];

Managers:

LOAD * INLINE [

    Floor, Manager, StartTime, EndTime, NoLine

    1, manolo, 23/11/2017 08:00, 23/11/2017 15:59, 1

    1, sara, 23/11/2017 16:00, 23/11/2017 23:59, 2

    2, pedro, 23/11/2017 00:00, 23/11/2017 07:59, 3

    3, jose, 24/11/2017 08:00, 24/11/2017 15:59, 4

    3, sara, 24/11/2017 16:00, 24/11/2017 23:59, 5

];

Left Join (Sales)

IntervalMatch(DateTime, Floor)

LOAD StartTime,

EndTime,

    Floor

Resident Managers;

Left Join (Sales)

LOAD *

Resident Managers;

DROP Table Managers;

cerezantonio
New Contributor

Re: Match events with intervals of time

Thank you!

It seems to work as I need.

The test app is giving me some trouble with the randomly generated dates but I hope it won't happen with real data.

EDIT:

This method works perfectly but IntervalMatch becomes unusable with large amounts of data.

My "Sales" table has around 25.000.000 rows and "Managers" is close to a million and Qlik Sense always finishes with an error after 20 minutes or so. If I try to debug, it works with limited load.

MVP
MVP

Re: Match events with intervals of time

That is a problem which we always run into when we have to do IntervalMatch... don't think I have a better way to do this