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

[Jason_Michaelides]

‌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

[santho_ak]

Thanks Jason.

But I am getting error.

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

Still getting error.

[MarcoWedel]

another one might be:

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

[MarcoWedel]

can you open a thread and post your application ?

[santho_ak]

Sure @Macro Auto generate function

[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

[iisa_jose_gonza]

I'd suggest the next script:

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

[EeroS]

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.

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

---

This is Great Stuff ! Thanks Guys 🙂 !

[wade12]

// 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;

[Martijn1]

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;