Generate random data in Qlik Sense & QlikView

June 29, 2020

This simple snippet provides a randomised data set for testing purposes.

Random Data 1024x439

The Data

The field definitions, based on a data set of 5 million rows:

Field NameUnique ValuesDescription
TransID5mIncrementing integer
TransLineID1Single integer
NumberUp to 26Integer between 1 and 26
Dimension1Up to 26Random upper-case letter
Dimension2Up to 26Random lower-case letter
Dimension3Up to 5mRandom four letter string
Dimension4Up to 5mRandom string (hash128)
Expression1Up to 5mRandom value between 0 and 1000000 (2dp)
Expression2Up to ~1kRandom value between 0 and 1000 (0dp)
Expression3Up to ~100kRandom value between 0 and 1 (5dp)

Sample based on using the script to generate 5 million rows

The Script

// Generate some random data
// This is roughly based off of the Qlik Ctrl+O+O default script, but with a bit more variety
SET vRecordCount = 50000;

Transactions:
Load
	IterNo().                             as TransLineID,
	RecNo()                               as TransID,
	mod(RecNo(),26)+1                     as Number,
	chr(Floor(26*Rand())+65)              as Dimension1,
	chr(Floor(26*Rand())+97)              as Dimension2,
	chr(Floor(26*Rand())+pick(round(rand())+1,65,97))
	   &chr(Floor(26*Rand())+pick(round(rand())+1,65,97))
	   &chr(Floor(26*Rand())+pick(round(rand())+1,65,97))
	   &chr(Floor(26*Rand())+pick(round(rand())+1,65,97)) as Dimension3,
	Hash128(Rand())                       as Dimension4,
	round(1000000*Rand(),0.01)            as Expression1,
	Round(1000*Rand()*Rand())             as Expression2,
	Round(Rand()*Rand()*Rand(),0.00001).  as Expression3
Autogenerate $(vRecordCount);

// Add comments to describe each field
Comment Field Dimension1 With "Random upper-case letter";
Comment Field Dimension2 With "Random lower-case letter";
Comment Field Dimension3 With "Random four letter string";
Comment Field Dimension4 With "Random string (hash128)";
Comment Field Expression1 With "Random value between 0 and 1000000 (2dp)";
Comment Field Expression2 With "Random value between 0 and 1000 (0dp)";
Comment Field Expression3 With "Random value between 0 and 1 (5dp)";

This snippet is also available on GitHub here.


Profile picture

From Dave, who writes to learn things. Thoughts and views are his own.

© 2024, withdave.