A very rounded function

The Round function in QlikView is similar to the equivalent function in Excel and other tools, but it is much more powerful.

I have used it for years, but rarely thought about its power until recently.

At a simple level, you can call the function like this:

   Round(3.14159)

Which will round the value to the nearest integer, thus giving me a nicely rounded 3.

To round to a significant number of places, instead of telling it the number of places that I want (as you do in Excel), I need to give it a step value to round to.  So, if I want to round to the nearest integer, the step value is 1.  Round to 3 places, the step value is 0.001:

   Round(3.14159, 0.001)

Which gives me 3.142 - the value is rounded to the nearest 0.001.

This is pretty much the way that I have always used the round function.  But you are not mandated to have only 1s in your step value!  How about this:

   Round(3.14159, 2)

This would yield a value of 4 - pi rounded to the nearest 2.

How about this:

   Time(Round(Time#(Times, 'HH:mm'), (1/(24*4))))

Here I a parsing a text field called "Times" that has a time value in "HH:mm" format.  I then round it to the nearest quarter hour.  Would you find that useful for log file analysis?

There is also a third parameter that we can give to Round - an offset.  So, for example, if I want to generate some random numbers:


   Load
Round(Rand()*100, 1) as RandVal
   AutoGenerate(1000000);


This would generate a range of numbers between 0 and 100.  If I look at the spread of those numbers I can see that 0 and 100 have only half of the occurrences of all the other numbers:

This happens, of course, because the range of values that will produce either 0 (0-0.4999) or 100 (99.5-99.9999) are half of the range of values that would produce and other number (e.g. 5 = 4.5-5.4999).

So I will offset each of the values by 0.5 and round those.  This will give me the result I need.

   Load
Round(Round(Rand()*100, 1, 0.5)) as RandVal
   AutoGenerate(1000000);

Simplistic example, but interesting.

---

Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner
Follow me on Twitter: @stephencredmond