ThousandSep

Overview

Open the Data load editor and add the load script below to a new tab.

The load script contains:

• A dataset which is loaded into a table named Transactions.

• Use of the default ThousandSep variable definition.

Load script

Transactions:
Load
date,
id,
amount
Inline 
[
date,id,amount
01/01/2022,1,10000000441
01/02/2022,2,21237492432
01/03/2022,3,41249475336
01/04/2022,4,24313369837
01/05/2022,5,47873578754
01/06/2022,6,24313884663
01/07/2022,7,28545883436
01/08/2022,8,35545828255
01/09/2022,9,37565817436
01/10/2022,10,3454343566
];

Results

Do the following:

1. Load the data and open a sheet. Create a new table and add this field as a dimension:date.

2. Add the following measure:

   =sum(amount)

3. In the properties panel, under Data, select the measure.

4. Under Number formatting, select Number.

In this example, the default ThousandSep definition, which is set to comma format (‘,’), is used. In the results table, the format of the amount field displays a comma between thousand groupings.

Overview

Open the Data load editor and add the load script below to a new tab.

The load script contains:

• The same dataset from the first example, which is loaded into a table named Transactions.

• Modification of the ThousandSep definition, at the start of the script, to display a '*' character as the thousands separator. This is an extreme example, and is used solely to demonstrate the functionality of the variable.

The modification used in this example is extreme and not commonly used, but is shown here to demonstrate the functionality of the variable.

Load script

SET ThousandSep='*';

Transactions:
Load
date,
id,
amount
Inline 
[
date,id,amount
01/01/2022,1,10000000441
01/02/2022,2,21237492432
01/03/2022,3,41249475336
01/04/2022,4,24313369837
01/05/2022,5,47873578754
01/06/2022,6,24313884663
01/07/2022,7,28545883436
01/08/2022,8,35545828255
01/09/2022,9,37565817436
01/10/2022,10,3454343566
];

Results

Do the following:

1. Load the data and open a sheet. Create a new table and add this field as a dimension:date.

2. Add the following measure:

   =sum(amount)

3. In the properties panel, under Data, select the measure.

4. Under Number formatting, select Custom.

At the start of the script, the ThousandSep system variable is modified to a '*'. In the results table, the format of the amount field can be seen to display a ‘*’ between thousand grouping.

Overview

Open the Data load editor and add the load script below to a new tab.

The load script contains:

• A dataset which is loaded into a table named Transactions.

• Data which has its numerical field in text format, with a comma used as the thousands separator.

• Use of the default ThousandSep system variable.

Load script

Transactions:
Load
date,
id,
amount
Inline 
[
date,id,amount
01/01/2022,1,'10,000,000,441'
01/02/2022,2,'21,492,432'
01/03/2022,3,'4,249,475,336'
01/04/2022,4,'24,313,369,837'
01/05/2022,5,'4,873,578,754'
01/06/2022,6,'313,884,663'
01/07/2022,7,'2,545,883,436'
01/08/2022,8,'545,828,255'
01/09/2022,9,'37,565,817,436'
01/10/2022,10,'3,454,343,566'
];

Results

Do the following:

1. Load the data and open a sheet. Create a new table and add this field as a dimension:date.

2. Add the following measure:

   =sum(amount)

3. In the properties panel, under Data, select the measure.

4. Under Number formatting, select Number.

5. Add the following measure to evaluate whether or not the amount field is a numerical value:

   =isnum(amount)

Once the data is loaded, we can see that Qlik Sense has interpreted the amount field as a numerical value, due to the data conforming to the ThousandSep variable. This is demonstrated by the isnum() function, which evaluates each entry to -1, or TRUE.