Intervalmatch not matching

[#1]

Hello,

To start, I've used this method before with good success.  I'm using the some of the same data sources, a spreadsheet with 2 columns:

Interval Start	Interval End
0:00	0:30
0:30	1:00
1:00	1:30
1:30	2:00

both fields of Excel Time type (13:30).  I load the Time Interval data last, after loading Employee Schedule data:

EWFMSchedule:

LOAD [Clndr Date] AS Date,

     [Agent Corp Login] AS Login,

     Code,

     Descr,

     Time([Start Moment]) AS [Start Moment],

     Time([Stop Moment]) AS [Stop Moment],

     Interval(Time([Stop Moment])-Time([Start Moment]), 'mm') AS Duration

FROM

[..\2.UserData\Shrink_EWFM_AgentSchedule.xlsx]

(ooxml, embedded labels, table is [EWFM Schedule]);

and then load the Interval data and finally, invoke the intervalmatch function:

TimeInterval:

LOAD Time([Interval Start]) AS [Interval Start],

     Time([Interval End]) AS [Interval End]

FROM

[..\2.UserData\TimeInterval.xlsx]

(ooxml, embedded labels, table is TimeInterval);

IntervalMatch:

IntervalMatch ([Start Moment])

Load distinct [Interval Start], [Interval End] resident TimeInterval;

When I drop all the components into a report block as List boxes, everything except Interval Start and Interval End work.  I can highlight a CODE, START or STOP MOMENT and all other objects filter correctly, except the Interval objects.

Finally, the data model looks correct as well:

Any suggestions would be appreciated.

Thank you!

Matthew Cummings

[#2]

Additional Information:  If I'm reading the tea leaves correctly, the Intervalmatch() function is returning 0 matches.

I've tried a few tricks to make all datatypes the same and on the report block they look the same but repeated efforts to link the [Start Moment] to either of the TimeInterval fields has failed.  I've tried simple If statements to compare the fields "If([Start Moment]=[Interval Start], 1, 0) knowing that some of the Start Moments line up with some of the Interval Start times but to no avail.  There seems to be some internal differences between the two fields.

All responses are greatly appreciated!

[#3 · Accepted solution]

Solved!

I understand Qlikview stores datetime objects in the same manor as MS; as a double precision floating point number.  I created List boxes for each field of concern and created within each listbox, a formula converting the field to a number.  I observed the [Start Moment] from the Schedule table still had values to the left of the decimal point where the [Interval Start] fields only had a 0 to the left of the decimal (no days, only h:m:ss).

Further research revealed the Frac() function that effectively removes the whole integer from the field, leaving the decimal value:

     Time(Frac([Start Moment]), 'hh:mm:ss') AS [Start Moment]

Now my Intervalmatch function is comparing "Apples with Apples"

Before:

After:

The moral of this story is to take a close look at the underlying datatype before soliciting advice on a forum!

Thanks!

[MarcoWedel]

maybe try with

Time(Frac([Start Moment])) AS [Start Moment]

instead of

Time([Start Moment]) AS [Start Moment]

same for all other time fields.

hope this helps

regards

Marco

[Anonymous]

may be helpful for you?

IntervalMatch on Time field