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