max 1 match in interval match function?!

[#1]

hi all,

I'm using an interval match in my script which works good except for 1 thing:

In some occasions the interval match finds 2 results, because of this there are 2 records created with the same data except for the intervalmatch result. I don't want this...

Is it possible to limit the number of results in an interval match? (the first result will do).
(the solution is not to make the records in the table where my intervalmatch gets its results unique because i need all data in that table..)

thx!

david

[Miguel_Angel_Baeyens · Accepted solution]

Hello David,

Check the following piece of script:

// The last two lines are for the same ID, I onlye want to keep the highest (1890)
Facts:
LOAD * INLINE [
ID, VALUE 
1000, 400
2000, 900
3000, 1250
4000, 1010
4000, 1890]; 
Intervals:
LOAD * INLINE [
RANGESTART, RANGEEND, RANGENAME
100, 500, A
501, 1000, B
1001, 1600, C
1599, 2000, D]; 
FactsWithIntervals:
INTERVALMATCH (VALUE) LOAD RANGESTART, 
     RANGEEND
RESIDENT Intervals;
DROP TABLE Intervals; 
OnlyOneRecordPerInterval:
RIGHT KEEP LOAD ID, 
     Max(VALUE) AS VALUE
RESIDENT FactsGROUP BY ID;
JOIN LOAD *
RESIDENT FactsWithIntervals; 
DROP TABLE Facts;
DROP TABLE FactsWithIntervals;

Kind of cumbersome with the cleanup to avoid unwanted synthetic keys and leave only corresponding data, though.

Hope that helps.