topic SELECT CASE WHEN EXISTS, Intervals in QlikView

SELECT CASE WHEN EXISTS, Intervals

Fri, 04 Apr 2014 12:03:43 GMT

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.

Re: SELECT CASE WHEN EXISTS, Intervals

Mon, 19 May 2014 11:41:23 GMT

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