topic Re: IntervalMatch does not work in Connectivity & Data Prep

IntervalMatch does not work

mariam_vd — Wed, 12 Jun 2024 16:36:46 GMT

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;

Re: IntervalMatch does not work

marksouzacosta — Wed, 12 Jun 2024 18:06:50 GMT

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

Re: IntervalMatch does not work

rwunderlich — Wed, 12 Jun 2024 22:07:37 GMT

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

Re: IntervalMatch does not work

mariam_vd — Thu, 13 Jun 2024 16:03:59 GMT

@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

Re: IntervalMatch does not work

rwunderlich — Thu, 13 Jun 2024 16:19:48 GMT

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

-Rob