Qlik Community

Ask a Question

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Announcements
May 18th, Changes to the way you login: using email vs. username. READ DETAILS/WATCH VIDEO
Jennell_McIntire
Employee
Employee

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.

Script1.png

The resulting table looks like this:

Table1.png

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).

Script2.png

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:

Table2.png

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

29 Comments
jason_michaelid
Luminary Alumni
Luminary Alumni

‌try this:

Map:

MAPPING LOAD * INLINE [

ID, Status

1,Received

2,Approved

3,Pending

4,Denied

];

Data:

LOAD

ApplyMap(‘Map’,Ceil(Rand()*4)) AS Status

Autogenerate xx;

Hope this helps,

Jason

16,320 Views
santho_ak
Partner
Partner

Thanks Jason.

But I am getting error.

c1.JPGc2.JPG

Tried removing prefix 'Mapping' as per this linkApply Map error:map_id not found

Still getting error.

0 Likes
16,320 Views
MarcoWedel

another one might be:

Pick(Ceil(Rand()*4), 'Received',  'Approved', 'Pending', 'Denied')

0 Likes
16,160 Views
MarcoWedel

can you open a thread and post your application ?

0 Likes
16,160 Views
santho_ak
Partner
Partner
0 Likes
16,160 Views
rwunderlich

It looks like you copy/pasted from the blog post and got backticks instead of single quote around 'map'.  It should be:

ApplyMap('Map',Ceil(Rand()*4)) AS Status

16,160 Views
iisa_jose_gonza
Partner
Partner

I'd suggest the next script:

tmpDATE:
LOAD
              Date($(vMinDate) + RowNo() - 1,'DD/MM/YYYY') as tmpDate
AUTOGENERATE 1
WHILE $(vMinDate) + IterNo() - 1<= $(vMaxDate);

12,174 Views
EeroS
Contributor II
Contributor II

This is just great stuff. Thanks guys and cheers !


@Jennell_McIntire wrote:

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.

Script1.png

The resulting table looks like this:

Table1.png

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).

Script2.png

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:

Table2.png

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


This is Great Stuff ! Thanks Guys 🙂 !

0 Likes
12,135 Views
wade12
Partner
Partner

// using a for loop
Let vMinDate18 = num('01/01/2018');
Let vMaxDate18 = num('31/12/2018');

For counter = $(vMinDate18) to $(vMaxDate18) step 1

Calendar2018:
LOAD
Date($(counter)) as TempDate18
Autogenerate (1);

Next;

0 Likes
9,100 Views
Martijn1
Contributor
Contributor

Hi Wade12,

when i executed your script I get an error. Any idea what the problem could be?

 

I changed the script by changing the year from 2018 in 2019.

 

Script used:

// using a for loop
Let vMinDate19 = num('01/01/2019');
Let vMaxDate19 = num('31/12/2019');
For counter = $(vMinDate19) to $(vMaxDate19) step 1
Calendar2019:
LOAD
Date($(counter)) as TempDate19
Autogenerate (1);

 

Error:
Next;Script editor.PNG

0 Likes
7,953 Views