topic Re: How to have INTERVALMATCH join also other columns in QlikView

How to have INTERVALMATCH join also other columns

cicciput — Sat, 16 Nov 2024 02:52:56 GMT

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.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

Re: How to have INTERVALMATCH join also other columns

Brett_Bleess — Wed, 21 Aug 2019 13:55:55 GMT

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/matching-intervals-to-discrete-data.htm

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

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

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

Re: How to have INTERVALMATCH join also other columns

cicciput — Mon, 02 Sep 2019 09:53:08 GMT

Hello Brett,

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

Kind Regards

Renzo

Re: How to have INTERVALMATCH join also other columns

Vegar — Mon, 02 Sep 2019 10:13:17 GMT

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;