Join and Interval match

[#1]

Hi all,

Sorry for the dummy qustion but I'm a very newbie.

I have to join this two table:

SESSION_TABLE

Session Time	SeName	User	ID
2012-12-26 08.00	Action0	Name1	ID_1
2012-12-26 15.00	Action2	Name2	ID_2
2012-12-27 08.00	Action1	Name3	ID_3
2012-12-27 19.00	Action5	Name1	ID_4

TASK_TABLE:

Time Start	Time Stop	Task ID	Task User
2012-12-26 07:50	2012-12-26 08:35	TASK01	Name1
2012-12-26 14:30	2012-12-26 16:40	TASK02	Name2
2012-12-27 07.00	2012-12-26 07:50	TASK03	Name3

And I would like to join the table and I have to create the following two table:

TBJOIN1 (list all the Session with the related Task.)

The join condition is: The Session Time is included in the range Task Time Start and Task Time Stop AND Session User Name is the Same Task User:

TASK.TIMESTART	TASK.TIMESTOP	TASK.TASKID	TASK.USER	SESSION.TIME	SESSION.USER	SESSION.SENAME	SESSION.ID
2012-12-26 07:50	2012-12-26 08:35	TASK01	Name1	2012-12-26 08.00	Name1	Action0	ID_1
2012-12-26 14:30	2012-12-26 16:40	TASK02	Name2	2012-12-26 15.00	Name2	Action2	ID_2
2012-12-27 07.00	2012-12-26 07:50	TASK03	Name3	-	-	-	-
-	-	-	-	2012-12-27 08.00	Name3	Action1	ID_3
-	-	-	-	2012-12-27 19.00	Name1	Action5	ID_4

I tried with the Extended interval match...but it doesn't work. Any Idea?

[Anonymous · Accepted solution]

Hi, you can do the following:

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

TASK_TABLE:
LOAD * INLINE [
Time Start, Time Stop, Task ID, Task User
2012-12-26 07:50, 2012-12-26 08:35, TASK01, Name1
2012-12-26 14:30, 2012-12-26 16:40, TASK02, Name2
2012-12-27 07:00, 2012-12-26 07:50, TASK03, Name3
];

SESSION_TABLE:
LOAD * INLINE [
Session Time, SeName, User, ID,
2012-12-26 08:00, Action0, Name1, ID_1
2012-12-26 15:00, Action2, Name2, ID_2
2012-12-27 08:00, Action1, Name3, ID_3
2012-12-27 19:00, Action5, Name1, ID_4
];

LEFT JOIN
IntervalMatch([Session Time])
LOAD
[Time Start],
[Time Stop]
RESIDENT TASK_TABLE;

JOIN
LOAD *
RESIDENT TASK_TABLE;

DROP Table TASK_TABLE;

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

The second join is an outer join (default QlikView). Also see attached.

Regards, Sander

[#3]

Le mail ti raggiungono ovunque con BlackBerry® from Vodafone!

Alessandro Zatti

Communication Valley

Via Budellungo, 2

43123 - Parma - ITALY

phone: +39 0521 4980

mobile: +39 346 8737605

a.zatti@reply.it

www.reply.it