Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Not applicable

Intervalmatch - value outside of interval

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

Tags (2)
1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

Intervalmatch - value outside of interval

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

View solution in original post

7 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Intervalmatch - value outside of interval

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

View solution in original post

Highlighted
Not applicable

Intervalmatch - value outside of interval

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.

Highlighted
MVP & Luminary
MVP & Luminary

Intervalmatch - value outside of interval

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

Highlighted
Not applicable

Intervalmatch - value outside of interval

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

Highlighted
MVP
MVP

Intervalmatch - value outside of interval

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.

Highlighted
Not applicable

Intervalmatch - value outside of interval

Thanks John for your reply..

Highlighted
Not applicable

Intervalmatch - value outside of interval

Hello Rob,

Great example! It works Great!

Regards,

Thijme de Haan