topic Re: Match events with intervals of time in App Development

Match events with intervals of time

cerezantonio — Wed, 29 Nov 2017 12:20:35 GMT

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?

Re: Match events with intervals of time

sunny_talwar — Wed, 29 Nov 2017 16:46:03 GMT

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;

Re: Match events with intervals of time

cerezantonio — Thu, 30 Nov 2017 10:49:06 GMT

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.

Re: Match events with intervals of time

sunny_talwar — Tue, 05 Dec 2017 17:52:54 GMT

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