Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
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 INTERVALMATCH resulting Hits table shows e.g. that Goal G01 of Match M01 has been seen by 2 viewers.

How do I know which users, i.e. how do I add UserName column to Hits table?

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)
1 Reply
Highlighted
Digital Support
Digital Support

Duplicate post, see https://community.qlik.com/t5/QlikView-App-Development/How-to-have-INTERVALMATCH-join-also-other-col... for full thread.

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.