IntervalMatch()

[#1]

Hi all,

i have the following problem. In the attached sample i have two tables:

users: This lists for a number of people their role with a from and to date field.

logins: This lists for a number of people date and location of a login.

Now i am trying to determine the role at the time of the login. The idea is to have one table in the end with one additional field "role" that gives me for each record the role of this person at the time of the log in.

Unfortunately what i tried did not work - yet.

Thanks a lot for some help.

[sbaldwin]

hi, try joining the table directly when doing the interfal match, something like the below

Thanks

Steve

users:
LOAD * INLINE [
    UserID,  DateFrom,   DateTo,   Role
    50,   2010-10-13,  2012-02-10,  User
    50,   2010-02-03,  2099-12-31,  Admin
    51,   2011-11-03,  2011-12-10,  User
    51,   2011-12-11,  2099-12-31,  Admin
];

logins:
LOAD * INLINE [
    UserID,  LogInDt,   Location
    50,   2010-10-13,  Loc_A
    50,   2012-05-15,  Loc_D
    51,   2012-02-11,  Loc_B
];

inner join(logins)
IntervalMatch (LogInDt, UserID)
Load
DateFrom,
DateTo,
UserID
Resident users;

inner join(users)
load * resident logins;
drop table logins;

exit script;