IntervalMatch using TimeStamp()

[raynac]

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.

[chrismarlow]

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]

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]

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

[raynac · Accepted solution]

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.