Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

SELECT CASE WHEN EXISTS, Intervals

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.

1 Solution

Accepted Solutions
Not applicable
Author

### 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;

View solution in original post

1 Reply
Not applicable
Author

### 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;