Autogenerate Your Data

[Jennell_McIntire]

Have you every used the Autogenerate clause in your load statement?  The Autogenerate clause is used in the load statement to automatically generate data in Qlik Sense or QlikView.  In the load statement you may see Autogenerate replace the From or Resident clause.  The key difference with the Autogenerate clause noted in Qlik Sense Help is that:

“The field list must not contain expressions which require data from an external data source or a previously loaded table, unless you refer to a single field value in a previously loaded table with the Peek function.”

I have seen the Autogenerate clause used to create a table of random numbers or to create a table with a range of dates.  Let’s take a look at how we can use it in the script.  Let’s assume I need a table of random numbers in my data model.  I can create a file, such as an Excel file, that has all the random numbers I need and load that into Qlik Sense or I can save myself some time and let Qlik Sense create the table for me.  In the script below, I am loading a table with an ID and a random number between 0 and 1 using the Autogenerate clause.  The 100 in parentheses lets Qlik Sense know how many records I would like to generate.  Note that the parentheses after Autogenerate are optional.

The resulting table looks like this:

That was pretty simple.  Now let’s see how we can generate a table with a range of dates.  If we want a table with all the dates in 2016, we can use a script (as seen below) to generate each date for the year.  The Autogenerate clause creates one date every time it reads the script until it gets to the end of the year (12/31/2016 in this example).

To begin the script, the variables for the date range are set.  The variable vMinDate stores the first day in the year and vMaxDate stores the last day in the year.  In the Load script, the TempDate is created.  If the first row is being created, then the date should be the value of vMinDate (1/1/2016).  If it is not the first row then increment the date by one day using the RowNo() function.  The While clause will repeat the script until the last day of the year is reached.  The resulting table looks like the image below with all dates from 1/1/2016 to 12/31/2016:

The Autogenerate clause in the load script provides an easy way to generate data in your Qlik Sense or QlikView data model.  You can control how many records you would like to add and, as done in the date example, you can repeat the script based on some condition.  So the next time you need to generate records that are not using an external data source or previously loaded table, try out the Autogenerate clause in your script.

Thanks,

Jennell

[karimeddini_sla]

Good post, but I'd recommend a simplification for the date LOAD:

LOAD DATE($(MinDate) + IterNo() - 1) AS TempDate
WHILE $(MinDate) + IterNo() - 1 <= $(MaxDate);

[#3]

The other way is you can generate the calendar with out variables as well.

Load

     MinDate + IterNo() - 1 as DateKey

While MaxDate <= MinDate + IterNo() -1

;

Load

     Min(Date) as MinDate,

     Max(Date) as MaxDate

Resident Transactions;

[#4]

Thanks Jennell for the post.

I would use Autogenerate for lot of purposes. We can also generate temp data for our code testing.

[phaneendra_kunc]

Wouldn't be nice if we can autogenerate names..

[simenkg]

Autogenerate is a nice function that I use frequently, so great job explaining it.

Your script for generating dates will not create 1/2/2016. if rowNo() is 1 then you create 1/1/2016, if not you take 1/1/2016 + rowNo() which for rowNo() = 2 creates 1/3/2016.

When I use autogenerate to create dates I use the following script:

Dates:
load Date($(vMinDate)+RowNo()-1) as TempDate autogenerate $(vMaxDate)-$(vMinDate) + 1;

[Alexander_Thor]

phaneendra.kunche‌

Granted this is for american/english sounding names only but I usually use this for female and male first names Top Names Over the Last 100 Years

Then I just combine that with a random surname from http://www.cs.princeton.edu/introcs/data/surnames.csv

[MarcoWedel]

Hi,

another one:

Temp:

LOAD Date(MinDate+IterNo()-1) as TempDate

While MinDate+IterNo()-1<=MaxDate;

LOAD '1/1/2016' as MinDate,

         '12/31/2016' as MaxDate

AutoGenerate 1;

regards

Marco

[rajareddyjkl]

nice  post  jennell

[sudeepkm]

good explanation. Thanks.

[santho_ak]

Hello,

Is there any script to load something like this in a column.

Column 'Status' should have random values like 'Received',  'Approved', 'Pending', 'Denied'.

Thanks in advance.