topic Joining fixed values to a value range in QlikView

Joining fixed values to a value range

Wed, 23 Sep 2009 17:09:21 GMT

I have a table with a fixed numerical value and another table that defines these numerical values via a Min and Max range. I would like to join these tables. Does Qlikview provide any clever features to perform this join automatically or does the data have to be enriched with surrogate keys?

Many Thanks

Sandy

Joining fixed values to a value range

Wed, 23 Sep 2009 19:43:49 GMT

Hi Sandy,

Check the intervalmatch feature. It is capable of joining this kind of tables where you want to join a number with a range.

Thanks & Best Regards,

Kuldeep Tak

Joining fixed values to a value range

Wed, 23 Sep 2009 20:42:28 GMT

Hi Kuldeep,

intervalmatch is indeed something that may work.

However, I have realised that the actual numeric 'interval' actually consists of prefixes. e.g. discrete value may be 123456789 and range would be 1230--> 1239. The first 4 digits of 123456789 would match and should therefore join.

In the absence of any other cool Qlikview features to accommodate a 'prefix range' I will bulk out my prefix ranges to be the same length as my discrete values. I will have a play and get back with my results.

Thanks!

Sandy

Joining fixed values to a value range

Fri, 25 Sep 2009 22:39:58 GMT

Hi Kuldeep,

intervalmatch does indeed do the trick after I bulked out my prefix ranges to the same length as the fact data.

It does not, however, seem to cope with 16 digit numbers.

Thanks Again!

Cheers

Sandy

Joining fixed values to a value range

johnw — Tue, 29 Sep 2009 05:41:13 GMT

As best I understand it, QlikView itself doesn't deal with 16 digit decimal numbers. I believe it used IEEE double-precision floating point internally, which limits you to about 15 decimal digits (52 binary digits). However, some of the functions, such as num#(), appear limited to 14 decimal digits, so that's probably the practical maximum.

If your ranges are always the first four digits, you could load in a separate field with just the first four digits to perform the intervalmatch. Then the full 16 digit numbers would probably have to be stored as text to avoid losing the low order digits.