Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

ink12345
Contributor

Finding date falling between two dates

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

1 Solution

Accepted Solutions
sasikanth
Valued Contributor III

Re: Finding date falling between two dates

HI Imran,

PFA ,

Might be issue with Date formats

17 Replies
MVP
MVP

Re: Finding date falling between two dates

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?

ink12345
Contributor

Re: Finding date falling between two dates

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.

ink12345
Contributor

Re: Finding date falling between two dates

Hope you understood this now.

ink12345
Contributor

Re: Finding date falling between two dates

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

MVP
MVP

Re: Finding date falling between two dates

Imran Khan wrote:

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

Isn't that valid for both records?

ink12345
Contributor

Re: Finding date falling between two dates

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

‌search for intervalmatch()

regards

Marco

ink12345
Contributor

Re: Finding date falling between two dates

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

ink12345
Contributor

Re: Finding date falling between two dates

Dataset A

   

CREATED TIMEContact Interaction ID
01/11/2015 20:09:51961913713112797733
01/11/2015 20:24:04961913713112797764
01/11/2015 20:30:44961913713112797775

Dataset B

   

Start TimeEndTimeNEW Talk Time
01/11/2015 20:22:3501/11/2015 20:10:0300:00:40
01/11/2015 20:05:5001/11/2015 20:24:0300:04:12
01/11/2015 20:25:5401/11/2015 20:30:5400: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

Community Browser