IntervalMatch extra lines

[#1]

Hello,

The problem im trying to solve, is that intervalmatch is creating me more records, like the same record with the same data repeated up to 23 times, where its suppose to be only one.

For exmaple, i got my data somehwat like:

ID	DateOn	DateOff
1	01/01/2015	25/01/2015
2	01/01/2015	24/01/2015
3	14/01/2015	31/01/2015
4	7/01/2015	9/01/2015
5	8/01/2015	15/01/2015
6	10/01/2015	14/01/2015
7	1/01/2015	3/01/2015
8	14/01/2015	15/01/2015
9	16/01/2015	28/01/2015
1	27/01/2015	29/01/2015
2	26/01/2015	31/01/2015
6	27/01/2015	30/01/2015

Using a calendar, ive created with the max and min dates, im trying to have a register for each day the ID was ON.

For example, i would like to have (only for the case o ID 7)

Date	ID	DateOn	DateOff
01/01/2015	7	1/01/2015	3/01/2015
02/01/2015	7	1/01/2015	3/01/2015
03/01/2015	7	1/01/2015	3/01/2015

This would be for every ID, the problem is that i get 5 register of each, giving me a total of 15 registers for ID 7, wich when scaling up would be a problem.

Im currently using the following expression for my intervalmatch

inner JOIN(Period)

INTERVALMATCH(Date)

LOAD

DateOn,

DateOff

RESIDENT Period;

Ive read some cases that using a key field solves this, the problem is that my Calendar only keyfield is Date, so i dont know what else should i do.

Any ideas?

thanks,

[ramoncova06]

try with this syntax

Inner Join(Period) IntervalMatch (Date,ID ) LOAD DateOn, DateOff, ID RESIDENT Period;

[#3]

This dosnt work, it return me nothing, since the ID field is only on Data table, not the calendar one, on the calendar one ive only got dates.

[MarcoWedel]

don't join but keep the table created by the intervalmatch load as a separate link table between the period and calendar tables.

regards

Marco

[hic]

If your Date field contains a time fraction also (with different times on different rows), you will get this behaviour. Try to define your date by using

  Date( Floor( Date ) ) as Date

instead

HIC

[MarcoWedel · Accepted solution]

Hi,

another reason for this behaviour could be multiple occurrences of the same interval (1/01/2015 - 3/01/2015) in your Period table:

if so, instead of

Period:
LOAD * Inline [
ID DateOn DateOff
1 01/01/2015 25/01/2015
2 01/01/2015 24/01/2015
3 14/01/2015 31/01/2015
4 7/01/2015 9/01/2015
5 8/01/2015 15/01/2015
6 10/01/2015 14/01/2015
7 1/01/2015 3/01/2015
8 14/01/2015 15/01/2015
9 16/01/2015 28/01/2015
1 27/01/2015 29/01/2015
2 26/01/2015 31/01/2015
6 27/01/2015 30/01/2015
10  1/01/2015 3/01/2015
11  1/01/2015 3/01/2015
12  1/01/2015 3/01/2015
13  1/01/2015 3/01/2015
] (delimiter is spaces);

tabCalendar:
LOAD Date(MinDate+IterNo()-1) as Date
While MinDate+IterNo()-1 <= MaxDate;
LOAD Min(DateOn) as MinDate,
    Max(DateOff) as MaxDate
Resident Period;

Inner Join(Period)
IntervalMatch(Date)
LOAD
DateOn,
DateOff
Resident Period;

you could load distinct intervals in the intervalmatch load:

Inner Join(Period)
IntervalMatch(Date)
LOAD Distinct
DateOn,
DateOff
Resident Period;

hope this helps

regards

Marco

[#7]

This solved it perfectly.

Thank you very much.

[MarcoWedel]

You're welcome

regards

Marco