Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Let's take this example with
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
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...
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
Hello Brett,
thanks for your reply, I'll have a look at all the docs you posted.
Kind Regards
Renzo
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;