topic Re: IntervalMatch using TimeStamp() in QlikView

IntervalMatch using TimeStamp()

raynac — Sat, 16 Nov 2024 03:03:18 GMT

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.

Re: IntervalMatch using TimeStamp()

chrismarlow — Tue, 09 Jul 2019 19:10:29 GMT

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.

Re: IntervalMatch using TimeStamp()

Frank_Hartmann — Tue, 09 Jul 2019 19:19:45 GMT

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;

Re: IntervalMatch using TimeStamp()

Brett_Bleess — Wed, 31 Jul 2019 19:11:29 GMT

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/ScriptPrefixes/IntervalMatch.htm

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

Hopefully this helps if you are still working on things.

Regards,
Brett

Re: IntervalMatch using TimeStamp()

raynac — Wed, 31 Jul 2019 20:23:12 GMT

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.