Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Join and Interval match

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.00Action0Name1ID_1

2012-12-26 15.00

Action2

Name2ID_2

2012-12-27 08.00

Action1

Name3ID_3

2012-12-27 19.00

Action5Name1ID_4

TASK_TABLE:

Time Start
Time Stop
Task ID
Task User
2012-12-26 07:502012-12-26 08:35TASK01Name1
2012-12-26 14:302012-12-26 16:40TASK02Name2
2012-12-27 07.002012-12-26 07:50TASK03Name3

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:502012-12-26 08:35TASK01Name12012-12-26 08.00Name1Action0ID_1
2012-12-26 14:302012-12-26 16:40TASK02Name22012-12-26 15.00Name2Action2ID_2
2012-12-27 07.002012-12-26 07:50TASK03Name3----
----2012-12-27 08.00Name3Action1ID_3
----2012-12-27 19.00Name1Action5ID_4

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

1 Solution

Accepted Solutions
filosofo
Contributor

Re: Join and Interval match

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

2 Replies
filosofo
Contributor

Re: Join and Interval match

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

Not applicable

Re: Join and Interval match

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

Community Browser