IntervalMatch Excluding Duplicate Values

[PetitFoulard]

Hi all,

Here is a simplified version of what I am trying to accomplish. I've borrowed the template from the user manual and modified it slightly as what I'm doing is very similar.

[Time Table A]:
LOAD * INLINE [
Alert_Time, ID
01:00, 1
01:00, 2
01:18, 3
02:12, 4
02:23, 5
02:23, 6
02:23, 7
04:15, 8
08:00, 9
09:00, 10
11:43, 11
11:43, 12
];

[Time Table B]:
LOAD * INLINE [
Start_Time, End_Time
01:00, 03:00
03:00, 07:00
07:00, 10:00
10:00, 11:45
];

Inner Join IntervalMatch (Alert_Time)
LOAD Start_Time, End_Time
Resident [Time Table B];

According to the user manual, when I do this, I should have an additional column in my Time Table B with all the matching Alert_Time fields. However, when I perform this, it only partially works - as the new column that gets added has all the corresponding values, but no duplicates.  As a result,  instead of having 12 rows of data in Table B, I have only 8.

When I try adding the ID as a key to the IntervalMatch function, such as ...

Inner Join IntervalMatch (Alert_Time, ID)
LOAD Start_Time, End_Time, ID
Resident [Time Table B];

The resulting table is then empty, and contains no rows of data.
This is baffling me as, I have copied the user manual's example of using a keyfield along with the IntervalMatch function, and it works properly there.
( https://help.qlik.com/en-US/sense/September2019/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPre... )
(Example 2 in the link above)

Any clues on how to get the duplicate values of Alert_Time into [Time Table B]? I have seen other questions online asking how to get rid of duplicate rows upon using IntervalMatch, but no posts with the opposite issue.

Thanks for any help!

[martinpohl]

I can see many problems in your data:

Example:

timestamp 00:00

but there is no interval that includes 00:00 ?

next:

interval 1 from 01:00 to 03.35 and

interval 2 from 02:30 to 07:58

so there is a multi-link time from 02:30 to 3:35

Clean your datas so that is only ONE possible interval for all timestmaps and include all timestamps into interval.

Regards

[PetitFoulard]

Hi Martinpohl,

Thanks very much for taking a look. You are absolutely right, there are some problems in the data posted above. I had slightly modified an example from the user's manual to showcase my current issue. I did not see that these problems existed in the user's manual! I assure you that the tables I am using for the IntervalMatch are a bit cleaner. 🙂

I will edit the post accordingly as to not confuse anyone else. Cheers and thanks again.

[PetitFoulard · Accepted solution]

In case this helps someone in the future:

As far as I could find, there were no examples online of someone using IntervalMatch and getting it to keep the duplicates of the MATCHFIELD value being passed in.

As I understand it, IntervalMatch will treat the MATCHFIELD as a primary key for the new table that the function creates. However this isn't necessary when using IntervalMatch with an Inner Join preceeding it.

For example, if we had the data below:

[Retirement]
Load * INLINE [
ID, Employee, RetirementDate
1, John, 2020-05-01
2, Veronica, 2020-05-01
3, Sam, 2020-04-03
4, Olivia, 2020-04-03
5, Jeff, 2020-07-15
6, Ashley, 2020-07-15
7, Porter, 2020-10-05
8, Leon, 2020-10-05
9, Gabbie, 2020-11-06

[RetirementYearQuarter]
Load * INLINE [
Quarter, QuarterStart, QuarterEnd
Q1, 2020-01-01, 2020-04-01
Q2, 2020-04-02, 2020-07-01
Q3, 2020-07-02, 2020-10-01
Q4, 2020-10-02, 2020-12-31

When I was using IntervalMatch on the two tables above to get a corresponding table of every person's retirement date and which quarter they were going to retire in, the resulting table would not hold any duplicates of retirement dates. Something like ...

Inner Join IntervalMatch (RetirementDate)
Load QuarterStart, QuarterEnd
Resident RetirementYearQuarter;

... Would yield the following result:

[RetirementYearQuarter]
RetirementDate, Quarter, QuarterStart, QuarterEnd
2020-05-01, Q2, 2020-04-02, 2020-07-01
2020-04-03, Q2, 2020-04-02, 2020-07-01
2020-07-15, Q3, 2020-07-02, 2020-10-01
2020-10-05, Q4, 2020-10-02, 2020-12-31
2020-11-06, Q4, 2020-10-02, 2020-12-31

(Funny how everyone seems to be retiring at the same time!) Ultimately what I am trying to accomplish is placing the quarter (Q1, Q2, Q3, Q4) next to the corresponding data in the Retirement table. Since I do not have an ID in the RetirementYearQuarter table to match with the Retirement table, this process becomes slightly more difficult. I tried to use the Quarter column as a keyfield in my interval match (Example:)

Inner Join IntervalMatch (RetirementDate, Quarter)
Load QuarterStart, QuarterEnd, Quarter
Resident RetirementYearQuarter

But that created a table with 0 rows of data (no surprise).

Solution: (Hopefully this will help anyone else who wanders here in the future)

When life gives you lemons ... I took the newly modified RetirementYearQuarter table and used it as a reference for my Retirement table. By doing a Left Join:

Left Join (Retirement) Load
RetirementDate,
Quarter
Resident RetirementYearQuarter;

I was then able to use the RetirementDate value to re-assign the Quarter column to its appropriate values. Looking at it here, detailed and laid out, it seems very simple, but sometimes the simplest problems can be complex when we stick our noses to close to the screen to see properly. Woops!

Cheers and best of luck all.