Creating a good script takes time and knowledge. In this section, I will show how a real script is put together in a real scenario for your reference. It is good to describe functions and best practices, but I believe showing everything come together in an example is even more valuable.

The following code example will show you in a very simple way how to:

• Split your code into different sections
• Create a simple stats table
• Check if your source file exists and create one if it doesn't use control statements
• Apply section access to the application

The sample script is split into sections that you can create in Script Editor.

For the script to work, you need to create a Create a new Connection in one Folder called Environment and another called Source.

In the Environment location, create a text file called Environment.txt with the following line of code in it:

SET vEnvironment=Live;

In the Source folder, create an Excel file called SectionAccess.xlsx with two columns, ACCESS, and USERID. You can add your own user to run the example.

You are now ready to copy the following sections into the Script Editor.

[Variable Setup] section:

/*
In this section we initialize all variables used in the script.
*/

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='£#,##0.00;-£#,##0.00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';
SET FirstWeekDay=0;
SET BrokenWeeks=0;
SET ReferenceDay=4;
SET FirstMonthOfYear=1;
SET CollationLocale='en-GB';
SET CreateSearchIndexOnReload=1;
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

//Including variable containing current environment (Development/Live); 

$(Must_Include=lib://EnvironmentEnvironment.txt);

TRACE Current environment is $(vEnvironment);

[Stats Table] section:

/*Creating the statistics base table */

[Stats]:
LOAD 
  *
Inline [
Stats.Table, Stats.Description, Stats.Records, Stats.Start, Stats.End
];

/*Creating subrouting that we will call when we create a new table. */

sub Stats

concatenate(Stats)
LOAD 
  *
Inline [
Stats.Table, Stats.Description, Stats.Records, Stats.Start, Stats.End
$(vTable), $(vTableDescription), $(vRecords), $(vStart), $(vEnd)
];

End Sub

[Main Script] section:

sub Main

/*Variables used in the stats table. */
LET vTable = 'SourceTable';
LET vTableDescription = 'This table contains main source data';
LET vStart = Timestamp(Now());

/* If source file doesn't exist I create it by loading and storing the data. */

If FileSize('[lib://SourceMyData.qvd]') THEN

TRACE Source file exists. Load data from source;

[$(vTable)]:
LOAD * FROM [LIB://SourceMyData.qvd] (qvd);


ELSE
TRACE Source file doesn't exist. Create source and store it.;

[$(vTable)]:
LOAD 
  *
Inline [
Month, Sales
Jan, 100
Feb, 110
Mar, 90
Apr, 95
May, 103
Jun, 99
Jul, 102
Aug, 130
Sep, 135
Oct, 132
Nov, 115
Dec, 102
];

TRACE Storing file...;

STORE $(vTable) INTO [LIB://SourceMyData.qvd] (qvd);

ENDIF;

/* We call the Stats subrouting to store table stats. */

LET vEnd = Timestamp(Now());
LET vRecords = NoOfRows('$(vTable)');

Call Stats;

End Sub

[Section Access] section:

Sub SectionAccess

Section Access;

LOAD 
  UPPER(ACCESS) AS ACCESS,
    UPPER(USERID) AS USERID
FROM [lib://Source/SectionAccess.xlsx]
(ooxml, embedded labels, table is Sheet1);

Section Application;

End Sub; 

[Script Execution] section:

Call Main;
Call SectionAccess;

The block of code presented in this section is simple but powerful. I encourage you to add your own touch and make it grow with more functionalities. Use this code as a base and expand it. With Qlik Sense, the sky is the limit!