Skip to main content Skip to complementary content

Wild cards in the data

It is also possible to use wild cards in the data. Two different wild cards exist: the star symbol, interpreted as all values of this field, and an optional symbol, interpreted as all remaining values of this field.

The star symbol

The star symbol is interpreted as all (listed) values of this field, that is, a value listed elsewhere in this table. If used in one of the system fields (USERID, PASSWORD, NTNAME or SERIAL) in a table loaded in the access section of the script, it is interpreted as all (also not listed) possible values of this field.

The star symbol is not allowed in information files. Also, it cannot be used in key fields, that is, fields used to join tables.

There is no star symbol available unless explicitly specified. For information on how to make a star statement in the script, see Star.

OtherSymbol

In many cases a way to represent all other values in a table is needed, that is, all values that were not explicitly found in the loaded data. This is done with a special variable called OtherSymbol. To define the OtherSymbol to be treated as "all other values", use the following syntax:

SET OTHERSYMBOL=<sym>;

before a LOAD or SELECT statement. <sym> may be any string.

The appearance of the defined symbol in an internal table will cause Qlik Sense to define it as all values not previously loaded in the field where it is found. Values found in the field after the appearance of the OtherSymbol will thus be disregarded.

In order to reset this functionality use:

SET OTHERSYMBOL=;

Example:  

Table Customers

CustomerID Name
1 ABC Inc.
2 XYZ Inc.
3 ACME INC
+ Undefined

Table Orders

CustomerID Name
1 1234
3 1243
5 1248
7 1299

Insert the following statement in the script before the point where the first table above is loaded:

SET OTHERSYMBOL=+;

Any reference to a CustomerID other than 1, 2 or 3, e.g. as when clicking on OrderID 1299 will result in Undefined under Name.

Information noteOtherSymbol is not intended to be used for creating outer joins between tables.

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – let us know how we can improve!