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: 
Anonymous
Not applicable

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

17 Replies
Anonymous
Not applicable
Author

Dataset A

  

CREATED TIMEContactInteraction 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

Anonymous
Not applicable
Author

Anonymous
Not applicable
Author

Events:
Load * From DataSet A;

Intervals:
Load * From DataSet B;

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

Anonymous
Not applicable
Author

Just modified HIC suggestion

Anonymous
Not applicable
Author

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..

sasikanth
Master
Master

HI Imran,

PFA ,

Might be issue with Date formats

Anonymous
Not applicable
Author

Attachment is missing

sasikanth
Master
Master

HI,

i am able to see the attachment , check once