Zu Hauptinhalt springen

Load

Der LOAD-Befehl lädt Felder aus einer Datei aus Daten, die im Skript definiert sind, aus einer zuvor geladenen Tabelle, aus einer Webseite, aus dem Ergebnis eines nachfolgenden SELECT-Befehls oder durch automatisches Generieren der Daten. It is also possible to load data from analytic connections.

Syntax:  

LOAD [ distinct ] fieldlist

[( from file [ format-spec ] |

from_field fieldassource [format-spec]|

inline data [ format-spec ] |

resident table-label |

autogenerate size ) |extension pluginname.functionname([script] tabledescription)]

[ where criterion | while criterion ]

[ group by groupbyfieldlist ]

[order by orderbyfieldlist ]

Arguments
Argument Description
distinct

You can use distinct as a predicate if you only want to load unique records. If there are duplicate records, the first instance will be loaded.

If you are using preceding loads, you need to place distinct in the first load statement, as distinct only affects the destination table.

Vorangehender Load-Befehl

fieldlist fieldlist ::= ( * | field{, * | field } )

A list of the fields to be loaded. Using * as a field list indicates all fields in the table.

field ::= ( fieldref | expression ) [as aliasname ]

The field definition must always contain a literal, a reference to an existing field, or an expression.

fieldref ::= ( fieldname |@fieldnumber |@startpos:endpos [ I | U | R | B | T] )

fieldname is a text that is identical to a field name in the table. Note that the field name must be enclosed by straight double quotation marks or square brackets if it contains e.g. spaces. Sometimes field names are not explicitly available. Then a different notation is used:

@fieldnumber represents the field number in a delimited table file. It must be a positive integer preceded by "@". The numbering is always made from 1 and up to the number of fields.

@startpos:endpos represents the start and end positions of a field in a file with fixed length records. The positions must both be positive integers. The two numbers must be preceded by "@" and separated by a colon. The numbering is always made from 1 and up to the number of positions. In the last field, n is used as end position.

  • If @startpos:endpos is immediately followed by the characters I or U, the bytes read will be interpreted as a binary signed (I) or unsigned (U) integer (Intel byte order). The number of positions read must be 1, 2 or 4.
  • If @startpos:endpos is immediately followed by the character R, the bytes read will be interpreted as a binary real number (IEEE 32-bit or 64 bit floating point). The number of positions read must be 4 or 8.
  • If @startpos:endpos is immediately followed by the character B, the bytes read will be interpreted as a BCD (Binary Coded Decimal) numbers according to the COMP-3 standard. Any number of bytes may be specified.

expression can be a numeric function or a string function based on one or several other fields in the same table. For further information, see the syntax of expressions.

as is used for assigning a new name to the field.

from

from is used if data should be loaded from a file using a folder or a web file data connection

file ::= [ path ] filename

Beispiel: 'lib://Table Files/'

If the path is omitted, Qlik Sense searches for the file in the directory specified by the Directory statement. If there is no Directory statement, Qlik Sense searches in the working directory, C:\Users\{user}\Documents\Qlik\Sense\Apps.

Directory

InformationshinweisIn a Qlik Sense server installation, the working directory is specified in Qlik Sense Repository Service, by default it is C:\ProgramData\Qlik\Sense\Apps. See the Qlik Management Console help for more information.

The filename may contain the standard DOS wildcard characters ( * and ? ). This will cause all the matching files in the specified directory to be loaded.

format-spec ::= ( fspec-item { , fspec-item } )

The format specification consists of a list of several format specification items, within brackets.

Formatoptionen

TipphinweisYou can use the URL is format specification to override the URL of a web file data connection, for example if you need to create a dynamic URL based on other data that was loaded.

Im Legacymodus für die Skripterstellung werden die folgenden Pfadformate ebenfalls unterstützt:

  • absolute

    Beispiel: c:\data\

  • relative to the Qlik Sense app working directory.

    Beispiel: data\

  • URL address (HTTP or FTP), pointing to a location on the Internet or an intranet.

    Beispiel: http://www.qlik.com

from_field from_field is used if data should be loaded from a previously loaded field.

fieldassource::=(tablename, fieldname)

The field is the name of the previously loaded tablename and fieldname.

format-spec ::= ( fspec-item {, fspec-item } )

The format specification consists of a list of several format specification items, within brackets. For more information, see Formatoptionen.

Informationshinweisfrom_field only supports commas as the list delimiter in when separating fields in tables.
inline inline is used if data should be typed within the script, and not loaded from a file.

data ::= [ text ]

Daten, die durch eine inline-Bedingung eingegeben werden, müssen in bestimmte Zeichen eingeschlossen werden – eckige Klammern, Anführungszeichen oder einfache umgekehrte Anführungszeichen. Der Text wird auf die gleiche Weise interpretiert wie der Inhalt einer Datei, d. h. er sollte auch genauso aufgebaut sein. Wenn Sie beispielsweise in einer Textdatei eine neue Zeile einfügen, sollten Sie dies auch im Text einer inline-Bedingung tun. Drücken Sie hierzu die Eingabetaste, wenn Sie das Skript eingeben.

In einem einfachen Inline-Ladevorgang wird die Anzahl der Spalten durch die erste Zeile definiert.

format-spec ::= ( fspec-item {, fspec-item } )

You can customize the inline load with many of the same format specification items that are available for other loaded tables. These items are listed in brackets. For more information, see Formatoptionen.

For more information about inline loads, see Using inline loads to load data.

resident Der Zusatz resident wird benutzt, um Daten aus einer bereits geladenen Tabelle zu laden.

table label is a label preceding the LOAD or SELECT statement(s) that created the original table. The label should be given with a colon at the end.

Laden von Daten aus bereits geladenen Tabellen

Tabellenbezeichnungen

autogenerate autogenerate is used if data should be automatically generated by Qlik Sense.

size ::= number

Number is an integer indicating the number of records to be generated.

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.

extension

You can load data from analytic connections. You need to use the extension clause to call a function defined in the server-side extension (SSE) plugin, or evaluate a script.

You can send a single table to the SSE plugin, and a single data table is returned. If the plugin does not specify the names of the fields that are returned, the fields will be named Field1, Field2, and so on.

Extension pluginname.functionname( tabledescription );

  • Loading data using a function in an SSE plugin

    tabledescription ::= (table { ,tablefield} )

    If you do not state table fields, the fields will be used in load order.

  • Loading data by evaluating a script in an SSE plugin

    tabledescription ::= ( script, table { ,tablefield} )

Data types are automatically detected in analytic connections. If the data has no numeric values and at least one non-NULL text string, the field is considered as text. In any other case it is considered as numeric.

You can force the data type by wrapping a field name with String() or Mixed().

  • String() forces the field to be text. If the field is numeric, the text part of the dual value is extracted, there is no conversion performed.
  • Mixed() forces the field to be dual.

String() or Mixed() cannot be used outside extension table field definitions, and you cannot use other Qlik Sense functions in a table field definition.

You need to configure analytic connections before you can use them.

Qlik Sense Enterprise: Creating an analytic connection (nur auf Englisch)

Qlik Sense Desktop: Konfigurieren von Analyseverbindungen in Qlik Sense Desktop

You can read more about analytic connections in the GitHub repository. qlik-oss/server-side-extension

where where is a clause used for stating whether a record should be included in the selection or not. The selection is included if criterion is True.

criterion is a logical expression.

while

while is a clause used for stating whether a record should be repeatedly read. The same record is read as long as criterion is True. In order to be useful, a while clause must typically include the IterNo( ) function.

criterion is a logical expression.

group by

Der Zusatz group by bestimmt, nach welchen Feldern die Daten aggregiert (gruppiert) werden sollen. The aggregation fields should be included in some way in the expressions loaded. No other fields than the aggregation fields may be used outside aggregation functions in the loaded expressions.

groupbyfieldlist ::= (fieldname { ,fieldname } )

order by order by is a clause used for sorting the records of a resident table before they are processed by the load statement. The resident table can be sorted by one or more fields in ascending or descending order. The sorting is made primarily by numeric value and secondarily by national collation order. This clause may only be used when the data source is a resident table.

The ordering fields specify which field the resident table is sorted by. The field can be specified by its name or by its number in the resident table (the first field is number 1).

orderbyfieldlist ::= fieldname [ sortorder ] { , fieldname [ sortorder ] }

sortorder is either asc for ascending or desc for descending. If no sortorder is specified, asc is assumed.

fieldname, path, filename and aliasname are text strings representing what the respective names imply. Any field in the source table can be used as fieldname. However, fields created through the as clause (aliasname) are out of scope and cannot be used inside the same load statement.

If no source of data is given by means of a from, inline, resident, from_field, extension or autogenerate clause, data will be loaded from the result of the immediately succeeding SELECT or LOAD statement. The succeeding statement should not have a prefix.

Laden von Daten aus bereits geladenen Tabellen

Beispiele:  

InformationshinweisBei der Dateierweiterung von DataFiles-Verbindungen wird zwischen Groß- und Kleinschreibung unterschieden. Beispiel: .qvd.

Hat diese Seite Ihnen geholfen?

Wenn Sie Probleme mit dieser Seite oder ihren Inhalten feststellen – einen Tippfehler, einen fehlenden Schritt oder einen technischen Fehler –, teilen Sie uns bitte mit, wie wir uns verbessern können!