
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Join 2 tables with a condition
Dear Community,
I have 2 tables and that I want to connect them with a condition And create a new field
For example:
Table A:
Table B:
When the USERID is the same and ACTIVITYDATE in Table A is between TimeStart And TimeEnd in Table B Create a new field in
Table A with the matching TimeStart
Like this:
Is it possible to do this?
If so then I would be happy to help
- Tags:
- qlikview_scripting
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Check this
TableA:
LOAD USERID,
Date(Date#(ACTIVITYDATE, 'DD-MM-YYYY hh:mm:ss'), 'DD-MM-YYYY hh:mm:ss') as ACTIVITYDATE;
LOAD * INLINE [
USERID, ACTIVITYDATE
A, 15-05-15 01:58:49
B, 15-05-15 01:59:06
A, 17-05-15 21:27:12
A, 17-05-15 21:33:34
B, 17-05-15 21:33:46
C, 17-05-15 21:37:21
];
TableB:
LOAD USERID,
Date(Date#(TimeStart, 'DD/MM/YYYY hh:mm'), 'DD/MM/YYYY hh:mm') as TimeStart,
Date(Date#(TimeEnd, 'DD/MM/YYYY hh:mm'), 'DD/MM/YYYY hh:mm') as TimeEnd;
LOAD * INLINE [
USERID, TimeStart, TimeEnd
A, 14/05/2015 16:55, 15/05/2015 02:34
B, 17/05/2015 16:34, 18/05/2015 02:47
A, 17/05/2015 20:30, 18/05/2015 01:00
];
Left Join (TableA)
IntervalMatch(ACTIVITYDATE, USERID)
LOAD TimeStart,
TimeEnd,
USERID
Resident TableB;
Left Join (TableA)
LOAD *
Resident TableB;
DROP Table TableB;

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
hi Sunny,
Thanks for the Reply.
I saw the IntervalMatch way but I do not know how to do it with 2 condition's first the USERID and second the date

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
This is called Extended interval match where you join not just on the date range, but also another common field or fields.... Look at the attached document to see how this can be solved in different ways. The document is from this thread

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Check this
TableA:
LOAD USERID,
Date(Date#(ACTIVITYDATE, 'DD-MM-YYYY hh:mm:ss'), 'DD-MM-YYYY hh:mm:ss') as ACTIVITYDATE;
LOAD * INLINE [
USERID, ACTIVITYDATE
A, 15-05-15 01:58:49
B, 15-05-15 01:59:06
A, 17-05-15 21:27:12
A, 17-05-15 21:33:34
B, 17-05-15 21:33:46
C, 17-05-15 21:37:21
];
TableB:
LOAD USERID,
Date(Date#(TimeStart, 'DD/MM/YYYY hh:mm'), 'DD/MM/YYYY hh:mm') as TimeStart,
Date(Date#(TimeEnd, 'DD/MM/YYYY hh:mm'), 'DD/MM/YYYY hh:mm') as TimeEnd;
LOAD * INLINE [
USERID, TimeStart, TimeEnd
A, 14/05/2015 16:55, 15/05/2015 02:34
B, 17/05/2015 16:34, 18/05/2015 02:47
A, 17/05/2015 20:30, 18/05/2015 01:00
];
Left Join (TableA)
IntervalMatch(ACTIVITYDATE, USERID)
LOAD TimeStart,
TimeEnd,
USERID
Resident TableB;
Left Join (TableA)
LOAD *
Resident TableB;
DROP Table TableB;

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
It's work!
thank you!
And thank you for the document
