Match events with intervals of time

[cerezantonio]

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?

[sunny_talwar · Accepted solution]

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]

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.

[sunny_talwar]

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