Intervalmatch - value outside of interval

[#1]

Hello everbody!

Im using the intervalmatch which is great! Is there a way to set all the values that are outside of an interval to something?Id like to give it a value so i can group all of my IDs that didnt not match the interval.

/Anders

[rwunderlich · Accepted solution]

If you Join the intervals to the data, the unmatched data will have nulls for the interval range fields. You can test for these nulls or remap them in subsequent statements. Here's a rough idea:

NullMap:
MAPPING LOAD
 null(), 'Unmatched'
AUTOGENERATE 1;
data:
LOAD * INLINE [
KeyA, Val
A1, 1
A2, 2
A3, 3
A4, 4
A5, 5
]
;
intervals:
LOAD * INLINE [
KeyB, Start, End
B1, 1, 3
B2, 5, 10
B3, 2, 3
]
;

LEFT JOIN (data) IntervalMatch (Val) LOAD Start, End RESIDENT intervals;
MAP Start USING NullMap;
RIGHT JOIN (data) LOAD DISTINCT * RESIDENT data;

-Rob

[#3]

Nice Rob! Thanks

But to make it even better i want to set the 'Unmatched' to the KeyB in the intervals.

Shouldnt the KeyB have a null value when it dosent match?

Tried changing to this but it didnt work 🙂

MAP KeyB USING NullMap;
RIGHT JOIN (data) LOAD DISTINCT * RESIDENT data;

Hmm and the Start, End makes synthetic keys.

[rwunderlich]

Anders,

IntervalMatch by itself will generate a synthetic key. I usually get rid of the syn key by joining all the fields from the interval table to the fact table and then DROP the interval table. So to get rid of the syn key and get "Unmatched" for KeyB, change the end of the script to:

LEFT JOIN (data) IntervalMatch (Val) LOAD Start, End RESIDENT intervals;
LEFT JOIN (data) LOAD DISTINCT * RESIDENT intervals;
DROP TABLE intervals;
MAP Start, KeyB USING NullMap;
RIGHT JOIN (data) LOAD DISTINCT * RESIDENT data;

An example is attached.

-Rob

[#5]

Hello Rob,

In your example the Intervalmatch and Load in the script are not highlighted or blue in colour(confirming that the script is correct)

It is still correct? If I jonly write Intervalmatch it is blue in colour and in your script it is otherwise..

Left Join (data) Intervalmatch (Val) load start, End Resident intervals;

Thanks in advance..

-sravan

[johnw]

The editor appears to have a problem correctly rendering the intervalmatch. The one in the application I'm working on is also not blue, but it works just fine.

[#7]

Thanks John for your reply..

[#8]

Hello Rob,

Great example! It works Great!

Regards,

Thijme de Haan