Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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