topic Re: Finding date falling between two dates in QlikView

Finding date falling between two dates

Anonymous — Mon, 26 Jan 2026 18:19:17 GMT

Hello All,

I am having one field called created date with value as 19/12/2015 15:45:34 (this format). Also having 2 more fields as start time and end time in same format.

One record is having unique number for identification. One record will have only one created time but there could be many records with start n end time. It means one to many relation. Here comes my requirement -

I want to have only start and end time capture for one unique number. For example;

Created time -19/12/2015 15:45:34

Start time -19/12/2015 15:43:34

End time -19/12/2015 15:47:34

Start time -19/12/2015 15:44:34

End time -19/12/2015 15:48:34

I want to have first record because start date falls between first start and end time and that is the most closest one.

Kindly suggest me the solution.

Regards,

Imran Khan

Re: Finding date falling between two dates

swuehl — Sat, 19 Dec 2015 17:05:45 GMT

Sorry, I haven't understood why you want the first and not the second record. What do you mean with 'most closest one'? Close to what?

Re: Finding date falling between two dates

Anonymous — Sat, 19 Dec 2015 17:15:19 GMT

Because one unique id will have only one original start time and end time. When you call up the customer care , you will be directed to ivr where not necessary all the directed calls to ivr get connected to representative.

Re: Finding date falling between two dates

Anonymous — Sat, 19 Dec 2015 17:15:54 GMT

Hope you understood this now.

Re: Finding date falling between two dates

Anonymous — Sat, 19 Dec 2015 17:16:44 GMT

Just want to pick my created time falls between start time and end time

Re: Finding date falling between two dates

swuehl — Sat, 19 Dec 2015 17:45:42 GMT

Imran Khan wrote:

Just want to pick my created time falls between start time and end time

Isn't that valid for both records?

Re: Finding date falling between two dates

Anonymous — Sat, 19 Dec 2015 18:26:29 GMT

Yes.. it's not valid for both the records. I want show only one output on basis if created date falls between start and end time.

Re: Finding date falling between two dates

MarcoWedel — Sat, 19 Dec 2015 23:04:39 GMT

‌search for intervalmatch()

regards

Marco

Re: Finding date falling between two dates

Anonymous — Wed, 23 Dec 2015 18:40:13 GMT

I tried intervalmatch but it is not working. I know interval match is the right solution for this case but not working for me. I am not sure what mistake i am doing.

Attached is the same data

Re: Finding date falling between two dates

Anonymous — Wed, 23 Dec 2015 18:43:58 GMT

Dataset A

CREATED TIME	Contact	Interaction ID
01/11/2015 20:09:51	9619137131	12797733
01/11/2015 20:24:04	9619137131	12797764
01/11/2015 20:30:44	9619137131	12797775

Dataset B

Start Time	EndTime	NEW Talk Time
01/11/2015 20:22:35	01/11/2015 20:10:03	00:00:40
01/11/2015 20:05:50	01/11/2015 20:24:03	00:04:12
01/11/2015 20:25:54	01/11/2015 20:30:54	00:04:58

Want to do intervalmatch with Created Time and want to look if created time falls between Start Time and End time and would like to tag row with respective Interaction ID

Re: Finding date falling between two dates

Anonymous — Thu, 24 Dec 2015 06:07:59 GMT

Dataset A

CREATED TIME	Contact	Interaction ID
01/11/2015 20:09:51	9619137131	12797733
01/11/2015 20:24:04	9619137131	12797764
01/11/2015 20:30:44	9619137131	12797775

Dataset B

Start Time	EndTime	NEW Talk Time
01/11/2015 20:22:35	01/11/2015 20:10:03	00:00:40
01/11/2015 20:05:50	01/11/2015 20:24:03	00:04:12
01/11/2015 20:25:54	01/11/2015 20:30:54	00:04:58

Want to do intervalmatch with Created Time and want to look if created time falls between Start Time and End time and would like to tag row with respective Interaction ID

Re: Finding date falling between two dates

Anonymous — Thu, 24 Dec 2015 06:14:19 GMT

did you check this?

IntervalMatch

IntervalMatch function

Re: Finding date falling between two dates

Anonymous — Thu, 24 Dec 2015 06:16:15 GMT

Events:
Load * From DataSet A;

Intervals:
Load * From DataSet B;

IntervalMatch:
IntervalMatch (CreatedTime)
Load distinct StartTime, EndTime resident Intervals;

Re: Finding date falling between two dates

Anonymous — Thu, 24 Dec 2015 06:16:51 GMT

Just modified HIC suggestion

Re: Finding date falling between two dates

Anonymous — Thu, 24 Dec 2015 07:02:30 GMT

I tried at my end but it is not working. Ideally it should work but don't know the reason why it is not working..

Re: Finding date falling between two dates

sasikanth — Thu, 24 Dec 2015 07:44:58 GMT

HI Imran,

PFA ,

Might be issue with Date formats

Re: Finding date falling between two dates

Anonymous — Thu, 24 Dec 2015 08:30:11 GMT

Attachment is missing

Re: Finding date falling between two dates

sasikanth — Thu, 24 Dec 2015 08:47:11 GMT

HI,

i am able to see the attachment , check once