Image_Size
U. S.,USA America,USA ];
NoConcatenate
TheNoConcatenate prefix forces two loaded tables with identical field sets to be treated as two separate internal tables, when they would otherwise be automatically concatenated.
Syntax:
NoConcatenate( loadstatement | selectstatement ) Example:
LOAD A,B from file1.csv;
NoConcatenate LOAD A,B from file2.csv;
Outer
The explicitJoin prefix can be preceded by the prefix outer in order to specify an outer join. In an outer join all combinations between the two tables are generated. The resulting table will thus contain combinations of field values from the raw data tables where the linking field values are represented in one or both tables. The outer keyword is optional.
Syntax:
Outer Join [ (tablename) ](loadstatement |selectstatement ) Arguments:
Argument Description
tablename The named table to be compared to the loaded table. loadstatement or selectstatement The LOAD or SELECT statement for the loaded table.
Example: Table1 A B 1 aa 2 cc 3 ee Table2 A C
1 xx
4 yy
SQL SELECT * from table1; join SQL SELECT * from table2;
OR
SQL SELECT * from table1;
outer join SQL SELECT * from table2; Joined table A B C 1 aa xx 2 cc - 3 ee - 4 - yy
Replace
Thereplace prefix is used to drop the entire Qlik Sense table and replace it with a new table that is loaded or selected.
Partial reload is currently only supported by using the Qlik Engine API.
Syntax:
Replace [only](loadstatement |selectstatement |map...usingstatement)
Thereplace prefix can be added to any LOAD, SELECT or map...using statement in the script. The replace LOAD/replace SELECT statement has the effect of dropping the entire Qlik Sense table, for which a table name is generated by thereplace LOAD/replace SELECT statement, and replacing it with a new table containing the result of thereplace LOAD/replace SELECT statement. The effect is the same during partial reload and full reload. Thereplace map...using statement causes mapping to take place also during partial script execution.
Arguments:
Argument Description
only An optional qualifier denoting that the statement should be disregarded during normal (non- partial) reloads.
Example Result Tab1:
Replace LOAD * from File1.csv;
During both normal and partial reload, the Qlik Sense table Tab1 is initially dropped. Thereafter new data is loaded from File1.csv and stored in Tab1.
Tab1: Replace only LOAD * from File1.csv;
During normal reload, this statement is disregarded.
During partial reload, any Qlik Sense table previously named Tab1 is initially dropped. Thereafter new data is loaded from File1.csv and stored in Tab1.
Tab1:
LOAD a,b,c from File1.csv; Replace LOAD a,b,c from File2.csv;
During normal reload, the file File1.csv is first read into the Qlik Sense table Tab1, but then immediately dropped and replaced by new data loaded from File2.csv. All data from File1.csv is lost.
During partial reload, the entire Qlik Sense table Tab1 is initially dropped. Thereafter it is replaced by new data loaded from File2.csv.
Tab1:
LOAD a,b,c from File1.csv; Replace only LOAD a,b,c from File2.csv;
During normal reload, data is loaded from File1.csv and stored in the Qlik Sense table Tab1. File2.csv is disregarded.
During partial reload, the entire Qlik Sense table Tab1 is initially dropped. Thereafter it is replaced by new data loaded from File2.csv. All data from File1.csv is lost.
Right
TheJoin and Keep prefixes can be preceded by the prefix right.
If used beforejoin it specifies that a right join should be used. The resulting table will only contain combinations of field values from the raw data tables where the linking field values are represented in the second table. If used beforekeep, it specifies that the first raw data table should be reduced to its common intersection with the second table, before being stored in Qlik Sense.
Were you looking for the string function by the same name? See: Right (page 620)
Syntax:
Right (Join | Keep) [(tablename)](loadstatement |selectstatement ) Arguments:
Argument Description
tablename The named table to be compared to the loaded table. loadstatement or selectstatement The LOAD or SELECT statement for the loaded table.
Table1 A B 1 aa 2 cc 3 ee Table2 A C 1 xx 4 yy QVTable:
SQL SELECT * from table1;
right join SQL SELECT * from table2; QVTable
A B C
1 aa xx
4 - yy
QVTab1:
SQL SELECT * from Table1; QVTab2:
right keep SQL SELECT * from Table2; QVTab1 A B 1 aa QVTab2 A C 1 xx 4 yy
The two tables in thekeep example are, of course, associated via A. tab1:
LOAD * from file1.csv; tab2:
LOAD * from file2.csv; .. .. ..
Sample
Thesample prefix to a LOAD or SELECT statement is used for loading a random sample of records from the data source.
Syntax:
Sample p ( loadstatement | selectstatement ) Arguments:
Argument Description
p An arbitrary expression which valuates to a number larger than 0 and lower or equal to 1. The number indicates the probability for a given record to be read.
All records will be read but only some of them will be loaded into Qlik Sense.
Example:
Sample 0.15 SQL SELECT * from Longtable; Sample(0.15) LOAD * from Longtab.csv;
The parentheses are allowed but not required.
Semantic
Tables containing relations between records can be loaded through asemantic prefix. This can for example be self-references within a table, where one record points to another, such as parent, belongs to, or
predecessor. Syntax:
Semantic( loadstatement | selectstatement)
The semantic load will create semantic fields that can be displayed in filter panes to be used for navigation in the data.
Tables loaded through asemantic statement cannot be concatenated. Example:
Semantic LOAD * from abc.csv;
Unless
Theunless prefix and suffix is used for creating a conditional clause which determines whether a statement or exit clause should be evaluated or not. It may be seen as a compact alternative to the fullif..end if statement.
Syntax:
(Unless condition statement | exitstatement Unless condition )
Thestatement or the exitstatement will only be executed if condition is evaluated to False.
Theunless prefix may be used on statements which already have one or several other statements, including additionalwhen or unless prefixes.
Arguments:
Argument Description
condition A logical expression evaluating to True or False.
statement Any Qlik Sense script statement except control statements. exitstatement An exit for, exit do or exit sub clause or an exit script statement.
Examples:
exit script unless A=1;
unless A=1 LOAD * from myfile.csv; unless A=1 when B=2 drop table Tab1;
When
Thewhen prefix and suffix is used for creating a conditional clause which determines whether a statement or exit clause should be executed or not. It may be seen as a compact alternative to the fullif..end if statement. Syntax:
(when condition statement | exitstatement when condition )
Thestatement or the exitstatement will only be executed if condition is evaluated to True.
Thewhen prefix may be used on statements which already have one or several other statements, including additionalwhen or unless prefixes.
Argument Description
condition A logical expression evaluating to True or False.
statement Any Qlik Sense script statement except control statements. exitstatement An exit for, exit do or exit sub clause or an exit script statement.
Example 1:
exit script when A=1; Example 2:
when A=1 LOAD * from myfile.csv; Example 3:
when A=1 unless B=2 drop table Tab1;