How to have INTERVALMATCH join also other columns

[cicciput]

Let's take this example with

• one table showing when a user connects and disconnects to a streaming soccer matches channel
• another table with datetime of when goals are scored

The resulting Hits table shows e.g. that Goal G01 of Match M01 has been seen by 2 viewers.

How do I add UserName column?

Conn:
LOAD * INLINE [
%MatchID, Conn.ConnStart,    Conn.ConnEnd,      Conn.UserName
M01,      01/01/2010 10:00,  01/01/2010 11:00,  UserA
M01,      01/01/2010 10:30,  28/02/2010 12:00,  UserB
M02,      02/01/2010 09:30,  02/01/2010 10:30,  UserA
M02,      01/01/2010 08:00,  02/01/2010 12:30,  UserC
];

Goal:
LOAD * INLINE [
%GoalID, %MatchID, Goal.Timestamp
G01,     M01,      01/01/2010 10:45
G02,     M01,      01/01/2010 11:45
G11,     M02,      02/01/2010 09:00
G12,     M02,      02/01/2010 10:00
G13,     M02,      02/01/2010 12:00
];

Hits:
LOAD %MatchID AS Hits.MatchID,
%GoalID,
Goal.Timestamp AS Hits.Timestamp
RESIDENT Goal;
INNER JOIN(Hits)
INTERVALMATCH (Hits.Timestamp, Hits.MatchID)
LEFT JOIN LOAD Conn.ConnStart AS Hits.ConnStart,
Conn.ConnEnd AS Hits.ConnEnd,
%MatchID AS Hits.MatchID
RESIDENT Conn;

--------------------------

Hits.MatchID %GoalID Hits.Timestamp    Hits.ConnEnd      Hits.ConnStart
M01          G01     01/01/2010 10:45  01/01/2010 11:00  01/01/2010 10:00
M01          G01     01/01/2010 10:45  28/02/2010 12:00  01/01/2010 10:30
M01          G02     01/01/2010 11:45  28/02/2010 12:00  01/01/2010 10:30
M02          G11     02/01/2010 09:00  02/01/2010 12:30  01/01/2010 08:00
M02          G12     02/01/2010 10:00  02/01/2010 10:30  02/01/2010 09:30
M02          G12     02/01/2010 10:00  02/01/2010 12:30  01/01/2010 08:00
M02          G13     02/01/2010 12:00  02/01/2010 12:30  01/01/2010 08:00

[Brett_Bleess]

Renzo, best I have is the following, not sure if you had reviewed these or not, but worth a look as that may provide the detail you need...

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/LoadData/matchi...

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/IntervalMatch_(...

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/Scripting/Scrip...

Design Blog area:

https://community.qlik.com/t5/Qlik-Design-Blog/IntervalMatch/ba-p/1464547

Sorry I do not have anything better for you, but the first link might have something to help I think.

Regards,
Brett

[cicciput]

Hello Brett,

thanks for your reply, I'll have a look at all the docs you posted.

Kind Regards

Renzo

[Vegar]

Try this in your Hits-generation script.

Hits:
LOAD
  %MatchID AS Hits.MatchID,
  %GoalID,
  Goal.Timestamp AS Hits.Timestamp
RESIDENT 
  Goal; 

INNER JOIN(Hits)
INTERVALMATCH (Hits.Timestamp, Hits.MatchID) 
LEFT JOIN LOAD 
  Conn.ConnStart AS Hits.ConnStart, 
  Conn.ConnEnd AS Hits.ConnEnd,
  %MatchID AS Hits.MatchID
RESIDENT 
  Conn;

LEFT JOIN (Hits)
LOAD 
  %MatchID as Hits.MatchID, //Join
  Conn.ConnStart as Hits.ConnStart,    //Join
  Conn.ConnEnd as Hits.ConnEnd, //Join      
  Conn.UserName as Hits.UserName
Resident Conn;