Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
If you are a Qlik Insight Bot user, join this collaborative group: JOIN GROUP
Highlighted
New Contributor III

How to have INTERVALMATCH join also other columns

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

Labels (1)
3 Replies
Highlighted
Digital Support
Digital Support

Re: How to have INTERVALMATCH join also other columns

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
Highlighted
New Contributor III

Re: How to have INTERVALMATCH join also other columns

Hello Brett,

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

 

Kind Regards

Renzo

Highlighted
Partner
Partner

Re: How to have INTERVALMATCH join also other columns

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;

 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes