Qlik script processor

Creates or transforms inputs using Qlik script.

The Qlik script processor allows you to directly enter Qlik script in a text editor to prepare data, or load data when used as input. Aimed at more advanced users, you can benefit from the whole Qlik syntax if you prefer coding certain operations manually when manipulating tables.

Usage

• In order to use the Script processor in a data flow, the code that you add in the editor must be consistent with the schema of the incoming data, and the preparation actions that have been performed until this point. Also, the script needs to follow certain rules:

• The script must contain a resident statement to use the table coming from the input flow.

  resident tablename

  For more information, see Load.

• You need to include a NoConcatenate statement.

  NoConcatenate LOAD A,B

  For more information, see NoConcatenate.

• After adding the script for the specific operation you want to perform, you must include a drop table statement at the end to drop the incoming table and keep only the generated output.

  drop table  tablename

  For more information, see Drop table.

• When using the processor as input node, you can have a maximum of two output flows, to load two different tables.
• When using the processor as intermediary node, you can have multiple input flows and a maximum of two output flows.
• You can use a maximum of 50 Qlik script processors in a data flow.

Properties

MyTable:
NoConcatenate Load *
Resident [name of input table];
Drop Table [name of input table];

To rename the processor or edit its description, point your mouse over the name or description to change in the Properties panel and click the Edit icon.

Using the script editor

Clicking the Open editor button in the configuration panel opens a powerful Script editor, to help you write your Qlik script. Easily insert tables, field names or functions, open documentation, insert script from other .qvf files, and check the validity of your expressions.

In the left panel, you can view the tables and fields currently loaded in your data flow and browse a wide range of Qlik script functions. This panel is organized in three tabs:

• Tables, where the tables loaded in the flow are listed. Point your mouse over any of the table names and click + to directly insert it in the script.

• Fields, where the fields available at this point of the flow are listed. Point your mouse over any of the field names and click + to directly insert it in the expression.

• Functions, where you can browse Qlik functions. You can use the search field to look for a specific function, or use the Function category drop-down to filter them.

  Point your mouse over any of the functions and click + to directly insert it in the expression.

• If you select the Explainer guide toggle, a short description and a link to the documentation will be displayed when clicking a function.

In the top panel, you can enable or disable three tools to help and guide you while writing script, use a shortcut to insert script from other files, as well as undo and redo buttons to fix potential mistakes.

• Search and replace, to automatically look for specific values and replace them if needed.

  

• Help mode, to turn any function you write in the script into clickable links that will take you to the documentation.

  

• Autocomplete, to display suggestions as you type, with a short description and example of a function.

  

• Include script, to open your catalog and browse scripts that are available to you as .qvf files before inserting them.

  

The bottom panel describes any potential error with your script, such as incorrect field names or invalid expressions.

When the status panel shows a green Ok, your expression is valid. Click Confirm to use this expression for your Qlik script processor and go back to the data flow editor. You can then validate the processor configuration.

Examples

• Loading multiple tables with the same schema, into one input for your flow. This example uses two .txt files from your catalog, with the same fields, but with a different content. I addition, this expression will add a field to track what data comes from which table, and generate an integer as id number:

  [MyTable_1]:
  LOAD
  'table1' as source_table,
  [firstname],
  [lastname],
  [nationality]
  FROM [lib://DataFiles/MyTable_1.txt] (txt, codepage is 28591, embedded labels, delimiter is ',', msq);
  
  [MyTable_2]:
  CONCATENATE (MyTable_1)
  LOAD
  'table2' as source_table,
  [firstname],
  [lastname],
  [nationality]
  FROM [lib://DataFiles/MyTable_2.txt] (txt, codepage is 28591, embedded labels, delimiter is ',', msq);
  
  [NewTable]:
  LOAD
  recno() as id,
  * RESIDENT MyTable_1;
  
  drop table MyTable_1;

  For more information, see Concatenate.

• Create an inline table with editable data as input for your flow:

  MyTable:
  Load * Inline [
  Country, Year, Sales
  Argentina, 2014, 66295.03
  Argentina, 2015, 140037.89
  Austria, 2014, 54166.09
  Austria, 205,182739.87
  ];
  

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

For more examples of script expressions, see Regular statements and Script and chart functions.