IntervalMatch does not work

[mariam_vd]

Hello,

Could you please help me to figure out, why this script returns nothing?

The EndTime vqlue is correct, I can see it, but the match doesn´t work

LOAD
time(timestamp(DTENDE, 'YYYY-MM-DD hh:mm:ss:fff'),'hh:mm:ss') as EndTime;

[TimeStamps]:

SELECT "DTENDE"
FROM "PJ_VD";

[Shifts]:

LOAD * Inline [
Start, End, Shift
07:00:00, 14:59:59, Shift 1
15:00:00, 22:59:59, Shift 2
23:00:00, 06:59:59, Shift 3
];

//Link the field EndTime to the time intervals defined by the fields Start and End.
Inner Join IntervalMatch (EndTime)
LOAD Start, End
Resident Shifts;

[marksouzacosta]

Hi @mariam_vd,

Date and Time always causes some confusion in Qlik.
You have to understand that Qlik basically has two data types: strings and numbers.

Date and Time, or Timestamp values are composed by two numeric parts. An Integer part (date) and the decimals (time).

Time(), Date(), TimeStamp() functions are just format functions, meaning, these functions DON'T change the value of the field, even if it is displayed so.

But, Qlik offers functions that actually change the value of fields. Those are: Time#(), Date#(), TimeStamp#() and other Match functions to extract date and time portions like Floor() and Frac().

So, probably your IntervalMatch is not working because the values are not compatible, so you have to transform the values correctly.

This is how you can manipulate the Date and Time values using Floor and Frac:

And this is how you can try to adjust your Load Script to convert correctly the values.
Please note that I don't know your database values so I'm assuming it is a Date and Time field.

[TimeStamps]:
LOAD
	"DTENDE" AS SourceEndDateTime,
    Date(Floor("DTENDE")) AS EnDate, // Floor changes the value, Date visually format the value
    Time(Frac("DTENDE")) AS EnTime // Frac changes the value, Time visually format the value
;
SELECT
	"DTENDE"
FROM
	"PJ_VD"
;


[Shifts]:
LOAD
// Use these functions, just in case
Time#(Start,'hh:mm:ss') AS Start, // Time# convert the value
Time#(End,'hh:mm:ss') AS End, // Time# convert the value
Shift
Inline [
Start, End, Shift
07:00:00, 14:59:59, Shift 1
15:00:00, 22:59:59, Shift 2
23:00:00, 06:59:59, Shift 3
];

//Link the field EndTime to the time intervals defined by the fields Start and End.
Inner Join IntervalMatch (EndTime)
LOAD Start, End
Resident Shifts;

Regards,

Mark Costa

[rwunderlich]

Your Start and End fields are just times -- fractions of a day. Your EndTime field is a full timestamp, integer date + time fraction. I believe you will want to modify your EndTime load to exclude the date portion.

time(frac(timestamp(DTENDE, 'YYYY-MM-DD hh:mm:ss:fff')),'hh:mm:ss') as EndTime;

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[mariam_vd]

@rwunderlich @marksouzacosta  

Thanks a lot for your replies
Indeed, using fraction of the timestamp helped to make the IntervalMatch start working.

Although, this task is still giving me hard times:

1) for defining the overnight interval (23h-07h) I had to set it twice: 23-24 & 00-07 
Is there another way to do that?

2) I have an operations at 06:59:59 and 07:00:00 as well as those at 16:59:59 and 17:00:00 which lay on the interval borders
and the logic for defining the shift is different:
I understand that the reason for that is that the num() of those time values is not the same as the num() of the interval borders

But how can I round the time value, so that I can be sure that the logic of the system is aligned with user expectation (they want 06:59 be always in Shift 3 and 07:00 in Shift 1)

3) when I set the time() format to hh:mm:ss:fff the num() of that time value doesn't work

[rwunderlich]

See https://qlikviewcookbook.com/2023/08/creating-time-groups-in-your-data-model/ for some tips.

-Rob