Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, the problem is this:
visit:
LOAD * INLINE [
VisitID, User, Site, EntryDateTime, ExitDateTime, Note
1001, VIP1, A, '2014-02-27 09:01:50', '2014-03-01 15:32:21', VIP1
1002, Bob, A, '2014-03-01 09:00:00', '2014-03-01 17:30:00', CEO
1003, 007, A, '2014-03-01 07:53:39', '2014-03-01 09:21:10', ''
1004, 007, A, '2014-03-01 09:58:15', '2014-03-01 14:12:01', ''
1005, 007, B, '2014-03-01 16:10:35', '2014-03-02 11:28:40', ''
1006, 007, B, '2014-03-05 13:01:58', '2014-03-06 18:24:14', ''
1007, VIP1, A, '2014-03-06 09:00:00', '2014-03-06 16:00:01', VIP1
1008, VIP1, C, '2014-03-07 10:30:00', '2014-03-07 17:25:30', VIP1
1009, 007, C, '2014-03-08 06:00:25', '2014-03-08 10:38:21', ''
];
permit:
LOAD * INLINE [
PermitID, User, Site, ValidFromDateTime, ValidToDateTime
1, Bob, A, '2014-01-01 09:00:00', '9999-12-31 23:59:59'
2, VIP1, A, '2014-02-27 08:00:00', '2014-03-02 00:00:00'
3, VIP1, A, '2014-03-06 08:30:00', '2014-03-06 18:00:00'
4, 007, A, '2014-03-01 06:00:00', '2014-03-01 09:00:00'
5, 007, A, '2014-03-01 12:00:00', '2014-03-01 18:00:00'
6, 007, B, '2014-03-01 00:00:00', '2014-04-01 00:00:00'
7, 007, C, '2014-03-08 06:00:00', '2014-03-08 18:00:00'
];
/*
The Desired new visit table is (the column PartialPermitNote is not essential) :
VisitID, User, Site, EntryDateTime, ExitDateTime, Note, HasFullPermit PartialPermitNote
1001, VIP1, A, '2014-02-27 09:01:50', '2014-03-01 15:32:21', VIP1, Yes
1002, Bob, A, '2014-03-01 09:00:00', '2014-03-01 17:30:00', CEO, Yes
1003, 007, A, '2014-03-01 07:53:39', '2014-03-01 09:21:10', '', No, Entry Only
1004, 007, A, '2014-03-01 09:58:15', '2014-03-01 14:12:01', '', No, Exit Only
1005, 007, B, '2014-03-01 16:10:35', '2014-03-02 11:28:40', '', Yes
1006, 007, B, '2014-03-05 13:01:58', '2014-03-06 18:24:14', '', Yes
1007, VIP1, A, '2014-03-06 09:00:00', '2014-03-06 16:00:01', VIP1, Yes
1008, VIP1, C, '2014-03-07 10:30:00', '2014-03-07 17:25:30', VIP1, No
1009, 007, C, '2014-03-08 06:00:25', '2014-03-08 10:38:21', '', Yes
*/
-------------------------------------------------------------------------------------------------------------------------------------------------------
In SQL i can create a hasPermit column as follows, but I am looking for a QlikView equivalent for the purpose of creating a QVD file.
SELECT *,
CASE WHEN EXISTS (
SELECT 1 FROM permit p
WHERE p.Site = v.Site
AND p.User = v.User
AND (v.ExitDateTime BETWEEN p.ValidFromDateTime AND p.ValidToDateTime)
AND (v.EntryDateTime BETWEEN p.ValidFromDateTime AND p.ValidToDateTime)
) THEN 1 ELSE 0
END hasPermit
FROM visit v
Any assistance is appreciated.
### The solution is as follows ###
In summary:
I use a natural join to get all the relevant combinations of the visits and the permits.
Add if statements to create flags (bit values) for filtering later.
'Group by' the data to get the unique visits
-----------------------------------------------------------------
visitpermit:
LOAD * INLINE [
VisitID, User, Site, EntryDateTime, ExitDateTime, Note
1001, VIP1, A, '2014-02-27 09:01:50', '2014-03-01 15:32:21', VIP1
1002, Bob, A, '2014-03-01 09:00:00', '2014-03-01 17:30:00', CEO
1003, 007, A, '2014-03-01 07:53:39', '2014-03-01 09:21:10', ''
1004, 007, A, '2014-03-01 09:58:15', '2014-03-01 14:12:01', ''
1005, 007, B, '2014-03-01 16:10:35', '2014-03-02 11:28:40', ''
1006, 007, B, '2014-03-05 13:01:58', '2014-03-06 18:24:14', ''
1007, VIP1, A, '2014-03-06 09:00:00', '2014-03-06 16:00:01', VIP1
1008, VIP1, C, '2014-03-07 10:30:00', '2014-03-07 17:25:30', VIP1
1009, 007, C, '2014-03-08 06:00:25', '2014-03-08 10:38:21', ''
];
JOIN
LOAD * INLINE [
PermitID, User, Site, ValidFromDateTime, ValidToDateTime, Type
1, Bob, A, '2014-01-01 09:00:00', '9999-12-31 23:59:59', STAFF
2, VIP1, A, '2014-02-27 08:00:00', '2014-03-02 00:00:00', STAFF
3, VIP1, A, '2014-03-06 08:30:00', '2014-03-06 18:00:00', VISITOR
4, 007, A, '2014-03-01 06:00:00', '2014-03-01 09:00:00', VISITOR
5, 007, A, '2014-03-01 12:00:00', '2014-03-01 18:00:00', VISITOR
6, 007, B, '2014-03-01 00:00:00', '2014-04-01 00:00:00', VISITOR
7, 007, C, '2014-03-08 06:00:00', '2014-03-08 18:00:00', SPECIAL
];
visitpermit1:
LOAD
if(EntryDateTime >= ValidFromDateTime and EntryDateTime <= ValidToDateTime, 1,0) as Entry_InPermitPeriod,
if(ExitDateTime >= ValidFromDateTime and ExitDateTime <= ValidToDateTime, 1,0) as Exit_InPermitPeriod,
if((if(EntryDateTime >= ValidFromDateTime and EntryDateTime <= ValidToDateTime, 1,0))
<> (if(ExitDateTime >= ValidFromDateTime and ExitDateTime <= ValidToDateTime, 1,0)),1,0) as IsPartialPermit,
if((EntryDateTime >= ValidFromDateTime and EntryDateTime <= ValidToDateTime)
and (ExitDateTime >= ValidFromDateTime and ExitDateTime <= ValidToDateTime)
, 1,0
) as WholeVisit_InPermitPeriod,
*
RESIDENT visitpermit
ORDER BY Site, User, EntryDateTime, ValidFromDateTime, ValidToDateTime
;
DROP TABLE visitpermit;
visits:
LOAD
VisitID, User, Site, EntryDateTime, ExitDateTime, Note,
MAX(WholeVisit_InPermitPeriod) as HasFullPermit,
MAX(if(IsPartialPermit = 1 and Entry_InPermitPeriod = 1, 1)) as HasEntryOnlyPermit,
MAX(if(IsPartialPermit = 1 and Exit_InPermitPeriod = 1, 1)) as HasExitOnlyPermit
, Replace(Replace(MAX(if(IsPartialPermit = 1, if( Entry_InPermitPeriod = 1, 100, 200))),100,'Entry Only'),200, 'Exit Only') as PartialPermitType
RESIDENT visitpermit1
GROUP BY VisitID, User, Site, EntryDateTime, ExitDateTime, Note
ORDER BY VisitID, EntryDateTime, ExitDateTime;
;
DROP TABLE visitpermit1;
### The solution is as follows ###
In summary:
I use a natural join to get all the relevant combinations of the visits and the permits.
Add if statements to create flags (bit values) for filtering later.
'Group by' the data to get the unique visits
-----------------------------------------------------------------
visitpermit:
LOAD * INLINE [
VisitID, User, Site, EntryDateTime, ExitDateTime, Note
1001, VIP1, A, '2014-02-27 09:01:50', '2014-03-01 15:32:21', VIP1
1002, Bob, A, '2014-03-01 09:00:00', '2014-03-01 17:30:00', CEO
1003, 007, A, '2014-03-01 07:53:39', '2014-03-01 09:21:10', ''
1004, 007, A, '2014-03-01 09:58:15', '2014-03-01 14:12:01', ''
1005, 007, B, '2014-03-01 16:10:35', '2014-03-02 11:28:40', ''
1006, 007, B, '2014-03-05 13:01:58', '2014-03-06 18:24:14', ''
1007, VIP1, A, '2014-03-06 09:00:00', '2014-03-06 16:00:01', VIP1
1008, VIP1, C, '2014-03-07 10:30:00', '2014-03-07 17:25:30', VIP1
1009, 007, C, '2014-03-08 06:00:25', '2014-03-08 10:38:21', ''
];
JOIN
LOAD * INLINE [
PermitID, User, Site, ValidFromDateTime, ValidToDateTime, Type
1, Bob, A, '2014-01-01 09:00:00', '9999-12-31 23:59:59', STAFF
2, VIP1, A, '2014-02-27 08:00:00', '2014-03-02 00:00:00', STAFF
3, VIP1, A, '2014-03-06 08:30:00', '2014-03-06 18:00:00', VISITOR
4, 007, A, '2014-03-01 06:00:00', '2014-03-01 09:00:00', VISITOR
5, 007, A, '2014-03-01 12:00:00', '2014-03-01 18:00:00', VISITOR
6, 007, B, '2014-03-01 00:00:00', '2014-04-01 00:00:00', VISITOR
7, 007, C, '2014-03-08 06:00:00', '2014-03-08 18:00:00', SPECIAL
];
visitpermit1:
LOAD
if(EntryDateTime >= ValidFromDateTime and EntryDateTime <= ValidToDateTime, 1,0) as Entry_InPermitPeriod,
if(ExitDateTime >= ValidFromDateTime and ExitDateTime <= ValidToDateTime, 1,0) as Exit_InPermitPeriod,
if((if(EntryDateTime >= ValidFromDateTime and EntryDateTime <= ValidToDateTime, 1,0))
<> (if(ExitDateTime >= ValidFromDateTime and ExitDateTime <= ValidToDateTime, 1,0)),1,0) as IsPartialPermit,
if((EntryDateTime >= ValidFromDateTime and EntryDateTime <= ValidToDateTime)
and (ExitDateTime >= ValidFromDateTime and ExitDateTime <= ValidToDateTime)
, 1,0
) as WholeVisit_InPermitPeriod,
*
RESIDENT visitpermit
ORDER BY Site, User, EntryDateTime, ValidFromDateTime, ValidToDateTime
;
DROP TABLE visitpermit;
visits:
LOAD
VisitID, User, Site, EntryDateTime, ExitDateTime, Note,
MAX(WholeVisit_InPermitPeriod) as HasFullPermit,
MAX(if(IsPartialPermit = 1 and Entry_InPermitPeriod = 1, 1)) as HasEntryOnlyPermit,
MAX(if(IsPartialPermit = 1 and Exit_InPermitPeriod = 1, 1)) as HasExitOnlyPermit
, Replace(Replace(MAX(if(IsPartialPermit = 1, if( Entry_InPermitPeriod = 1, 100, 200))),100,'Entry Only'),200, 'Exit Only') as PartialPermitType
RESIDENT visitpermit1
GROUP BY VisitID, User, Site, EntryDateTime, ExitDateTime, Note
ORDER BY VisitID, EntryDateTime, ExitDateTime;
;
DROP TABLE visitpermit1;