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: 
raynac
Partner - Creator
Partner - Creator

IntervalMatch using TimeStamp()

My colleague and I are trying to intervalmatch() a dateTime field.  The code below shows onscreen as producing identically formatted fields that look as though they should compare, but they do not.  Can anyone see where we are going stray?

timeslots:
load * Inline
[
from_time, to_time
'08:00:00', '08:14:59'
'08:15:00', '08:29:59'
'08:30:00', '08:44:59'
'08:45:00', '08:59:59'
];

master_cal:
load * inline
[date1
"07/01/2019"
"07/02/2019"
];

NoConcatenate
temp1:
load date1 resident master_cal;
join(temp1) load from_time, to_time resident timeslots;

NoConcatenate

temp2:
load
timestamp#(date1&' '&from_time) as "FromTime1",
timestamp#(date1&' '&to_time) as "ToTime1"
resident temp1;

drop table temp1;

data:
load * inline
[
name, loginTime
"Rayna", "07/01/2019 08:14:00"
"Anselm", "07/01/2019 08:48:00"
"Rayna", "07/02/2019 08:32:00"
"Anselm", "07/02/2019 08:06:00"
];

data1:
load name, timestamp#((loginTime)) as loginTime1
Resident data;


IntervalMatch (
loginTime1
)
load FromTime1, ToTime1 resident temp2;

drop table data;

This is what is produced.  We have tried a number of different number and date formats but they do not want to match.

qlik.JPG

Labels (1)
1 Solution

Accepted Solutions
raynac
Partner - Creator
Partner - Creator
Author

Hey Brett!  Thanks for chiming in.  My colleague and I knew to use the intervalmatch piece; but it just was not working that day for us and was making us crazy, because we knew it should.

I am fairly certain we tried both of the two responses and met with some success with both.  In the end, they led us to whatever wound up working, because it's working fine now but the code doesn't currently really resemble either answer.

View solution in original post

4 Replies
chrismarlow
Specialist II
Specialist II

Hi,

I think you need to force a join on your interval match & throw away some of your temporary tables, so try this after your load to data1;

left join (data1)
IntervalMatch (loginTime1)
load FromTime1, ToTime1 resident temp2;

drop tables data, timeslots,temp2;

Cheers,

Chris.

Frank_Hartmann
Master II
Master II

seems to work if you use date# function instead of timestamp function.

give this a try:

timeslots:
load * Inline
[
from_time, to_time
'08:00:00', '08:14:59'
'08:15:00', '08:29:59'
'08:30:00', '08:44:59'
'08:45:00', '08:59:59'
];

master_cal:
load * inline
[date1
"07/01/2019"
"07/02/2019"
];

NoConcatenate
temp1:
load date1 resident master_cal;
join(temp1) load from_time, to_time resident timeslots;

NoConcatenate

temp2:
load
date#(date1&' '&from_time,'DD/MM/YYYY hh:mm:ss') as "FromTime1", 
date#(date1&' '&to_time,'DD/MM/YYYY hh:mm:ss') as "ToTime1" 
resident temp1;
drop table temp1;
data:
load * inline
[
name, loginTime
"Rayna", "07/01/2019 08:14:00"
"Anselm", "07/01/2019 08:48:00"
"Rayna", "07/02/2019 08:32:00"
"Anselm", "07/02/2019 08:06:00"
];

data1:
load name, date#(loginTime,'DD/MM/YYYY hh:mm:ss') as loginTime1
Resident data;

IntervalMatch (loginTime1)
load FromTime1, ToTime1 resident temp2;

drop table data;
Brett_Bleess
Former Employee
Former Employee

Hey Rayna, not sure if either of the other posts got you what you needed, if they did, be sure to give the guys credit by clicking on the Accept as Solution button if so.  Here is a Design Blog link that might help a bit further if you are still working on things:

https://community.qlik.com/t5/Qlik-Design-Blog/IntervalMatch/ba-p/1464547

Not sure if you found all the Help either, so here are those links too:

http://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/Scripting/Script...

http://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/IntervalMatch_(E...

Hopefully this helps if you are still working on things.

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
raynac
Partner - Creator
Partner - Creator
Author

Hey Brett!  Thanks for chiming in.  My colleague and I knew to use the intervalmatch piece; but it just was not working that day for us and was making us crazy, because we knew it should.

I am fairly certain we tried both of the two responses and met with some success with both.  In the end, they led us to whatever wound up working, because it's working fine now but the code doesn't currently really resemble either answer.