Generating random test data in QlikView with Mockaroo

By Barry Harmsen

Functions to create random data in QlikView


I often need random data. For example to use in an example file on this blog, to deliver a customer demo or just to test out something new. As I am probably not the only one with this need, here’s a short and sweet post on how to generate random data for use in QlikView.

I usually create random data by using variables that generate different types of random data. Some of these are shown in the image above. For dimension labels I use mapping tables containing, for example, first and last names or company names. Then I put together some load statements like the one shown below and voila, random data.


Random data load statement in QlikView


While this approach works well it can be quite a hassle, especially if you want to create dimension values that look plausible. You can imagine that I was pleasantly surprised when I caught a tweet by Qlik’s Michael Tarallo yesterday in which he mentioned an online tool for generating test data: Mockaroo.

Mockaroo - random test data generator

This tool lets you generate dozens of different data types, from random dates, string and numbers to cities, names and even shirt sizes. You can generate up to 100,000 rows of random data, which can be exported to many different formats, CSV being the most useful for QlikView. You can even save your schemas and retrieve them directly from Mockaroo in QlikView:


Random data from Mockaroo in QlikView


While the 100,000 row limit is on the low side when you want to test the performance implications of various approaches, this does seem to be a very useful tool for customer demo’s. Alternatively, you can use this tool to generate your dimension tables while using the ‘old’ approach to generate big fact tables. In any case, if you ever need some realistic test data then Mockaroo is definitely worth a look.

Some additional tips for nice, random data in QlikView:

  • You can use the NormInv() function in QlikView to generate random values that follow a normal distribution, in my opinion this makes for much nicer, more realistic charts.
  • You can use the Sin() or Cos() functions to create (relatively) realistic seasonal patterns in your data.