The QlikView Script Editor is the primary place of work for developing the scripting factors of QlikView utility. some beneficial features inside the Script Editor encompass sure menus. The menus defined in this bankruptcy are determined inside the menu bar at the top of the display screen. maximum instructions also can be configured as buttons within the toolbars. the subsequent post gives you an outline on the various menus:
The file menu
The file menu is a drop-down menu at the top of the display, and includes the subsequent commands. It contains instructions regarding saving the script and record, reloading, uploading scripts, hidden scripts, and get entry to to the desk Viewer.
keep whole document, save complete report As… and save outside Script record: these commands are available from the file command menu and the keep entire file command is to be had as an icon within the toolbar. The commands keep the corresponding files and let you save the document or script report as a substitute as a named report. The keep external Script document command permits you to keep the whole script as a .qvs record which can later be imported into other QlikView documents. ‘store early and frequently’, the pleasant exercise is to save (save whole report) earlier than each reload after enhancing the script in a manner. you can installation QlikView to routinely save the script before reloading the records inside the consumer alternatives dialog field, accessed from the principle QlikView screen via navigating to the Settings | person alternatives | store menu command and checking the store before Reload checkbox. allow’s have a take a look at some of the commands:
Reload: that is to be had from the record command menu or the toolbar icon. This command closes the Script Editor, reloads the script, and indicates the development of the script execution and any mistakes if encountered. If the script reload procedure is cancelled and the QlikView application already consists of previously loaded information, QlikView will ask in case you want to reload vintage statistics or now not. The first-rate exercise is to just accept the reload antique facts option, so sheet gadgets and charts show with data.
Open external Script file: This command is a available manner to import formerly stored script files (the .qvs documents) and textual content (.txt) documents containing scripts. while importing script files using this command, a newly imported script is delivered in a new script tab, with the call of the script record as the brand new tab call. This new script may be edited as wanted as soon as imported.
Desk Viewer: The desk Viewer is a fundamental device for supporting to create QlikView programs. The desk Viewer is also available (and less complicated to get admission to) from each the icon toolbar within the Script Editor and the main icon toolbar in the foremost QlikView application. This tool allows you to graphically view the desk structure, relationships, metadata, and institutions of source and model tables on your utility. The desk Viewer serves as the primary troubleshooting and visualization device to your statistics model.
The Edit menu
The Edit menu incorporates simple script modifying and tidying commands that make the script less complicated to read, or disable (via commenting) blocks of script code. permit’s have a study the instructions:
Undo, Redo, cut, reproduction, Paste, Delete, pick All, find/update, Indent, Outdent: those are the simple enhancing gear as observed in many software program packages including Microsoft word. Keyboard shortcuts for: Undo (Ctrl+Z) , Redo (Ctrl+Y), reduce (Ctrl+X), reproduction (Ctrl+C), Paste (Ctrl+V).
clear entire Script: This clears the lively (open) script tab and gets rid of the script tab (and can't be undone). This command does now not affect every other script tabs. you can additionally delete/ clean a script tab by using the use of the Tab | put off command.
Higher Case, lower Case, Invert Case, Capitalize: these commands manipulate the manipulation of textual content case. these commands can be useful in code agency and changing the case of case-sensitive script areas (a few regions of QlikView scripting are case sensitive, which include accounts and roles in section get admission to, (which must be uppercase).
Remark, Uncomment: these are very beneficial commands that permit you to upload descriptive textual content comments (overlooked by the script compiler) or disable quantities of the script by using using remarks. you could additionally use different commenting commands, with a purpose to be discussed in the next phase, Script commenting.
The Insert menu
The Insert menu incorporates effective instructions to insert many varieties of script elements into the script code, from variables to inclusive of files and connecting statements. allow’s have a have a look at the instructions:
Set declaration: This command lets in you to create and outline SET statements the usage of custom variables or many predefined variables (machine variables, mistakes variables, and so forth.).
environment Variables: This command inserts the standard list of environment variables which might be normally present in a script when a brand new QlikView document is created (money, time, date, and so on). those statements can be edited once inserted into the script.
Script files: This command lets in you to browse for a selected script documents (.sq., .txt, or .qvs) and insert the contents of the script document on the cursor area.
consist of announcement: This command inserts a connection with a selected record (.square, .txt, or .qvs). It differs from Insert | Script documents by using not inserting the content material directly into the QlikView script, however simplest referencing this report. The consist of report may additionally contain a reusable script, question, connection string, or subroutine you wish to apply to your script. The beauty of the consist of document command is that the statistics is reusable and does not need to be edited in QlikView itself. it is powerful when using connection strings, email lists, and comparable records that may change regularly.
Domain SID: This command inserts the area protection identification into the script. This fee is the identity variety of the NT domain in which the user has logged in. This cost can be used to set up phase get right of entry to whilst restricting customers by using NTDOMAINSID.
check Script: This command inserts a pattern test script to demonstrate the functioning of the QlikView script execution, objects, and debugging.
Load statement: This command permits for insertion of a load statement from both an external record (spreadsheet, text, XML, QVD, and so forth) or an inline wizard that permits for the advent of records internally in a desk.
section get entry to: This command inserts section access statistics both from the publisher Authorization area published through the QlikView management Console (normally in a layout much like HTTP:// localhost: 4780/ QMS/AuthTable) or inline segment get admission to. in case you are the usage of writer Authorization, be sure to make the URL at the server a trusted site in internet Explorer. in case you are the usage of inline phase get entry to, a wizard presentations, permitting you to go into the necessary records and additionally goals the sort of phase get admission to (username, NT domain, and so forth).
connect announcement, Disconnect assertion: The join command opens the connect to statistics supply dialog, where the data supply may be selected with the relationship credentials. this may insert a join string into the script. The Disconnect command will insert a disconnect command into the script, with a view to disconnect QlikView from the data source.
The Tab menu
As QlikView packages develop, the quantity of tabs that facts is spread across can develop hastily as properly. The Tab menu is useful in creating script tabs, moving the tabs to the left and right (selling and demoting tabs) in addition to renaming, merging, and removing tabs. remember the fact that tabs are a manner of organizing and ordering script execution, and the script execution runs from the pinnacle of the leftmost tab to the bottom of the rightmost tab. allow’s have a take a look at the instructions:
Add Tab, Insert Tab at Cursor: those commands add a brand new script tab at the stop of the tab set or at a place decided by using the location of the cursor in the script, respectively.
Rename: This command allows you to rename the lively script tab.
promote, Demote: those instructions flow the tabs to the left or right, respectively. Tab locations manipulate the execution of the script in that tab.
Merge with previous: This command merges the current energetic tab script with the preceding (tab to the left) tab script statistics. It appends the contemporary tab script facts to the lowest of the previous tab script.
remove: This command eliminates the modern lively tab and additionally all the script facts it includes.
The equipment menu
The gear menu assists with putting in place the suitable ODBC connection, placing the Script Editor possibilities, and offering a short syntax test function. allow’s have a have a look at the commands:
ODBC Administrator 64 bits, ODBC Administrator 32 bits: these instructions open the ideal ODBC records supply Administrator dialog that permits you to installation the best DSN records for the facts source connection. pick out the right conversation based totally on your environment conditions and database.
Editor alternatives: This command opens the person options conversation that permits you to configure the Script Editor features, which includes textual content font and size, help features, shortcuts, default scripting engine, and other fashion elements.
Syntax test: This command runs the QlikView syntax checker at the script code.
The help menu
The help menu presentations help data for the specific phase of QlikView that's energetic.The gear paneThe lower phase of the Script Editor window is referred to as the tools pane. This segment has four tabs: facts,features, Variables, and Settings. The gear pane of the Script Editor is wherein records sources are described and connection strings are created, capabilities and values are created and edited, and different system settings are made.
The facts tab: This tab permits you to create connections to databases and other facts resources, connection strings, choose statements, and customary report place settings (FTP or relative paths). Connections to ODBC and OLE DB information resources may be crafted from here in addition to custom facts sources if you have hooked up the right .dll connector as required. A connector that serves as a manner to create a QlikView Server administrative device, QVSAdminDataProvider.dll, is also furnished. This QVW metadata extractor may be used alongside, in place of, or collectively with different equipment such as the QlikView Governance Dashboard, to be had at no cost from QlikMarket (marketplace.QlikView.com).
The features tab: From this tab, you may find a characteristic kind which is appropriate for your software and paste it into the script. The functions tab also incorporates the precise syntax to useful resource you in its use.
The Variables tab: The custom variables you have created inside the application may be observed here and pasted into the script. you may also view and paste system variables into the script from this tab.
The Settings tab: within the Settings tab, you may set script privileges to examine and write from/to databases and execute outside packages (turning those on decreases protection and could reason an alert to be caused, therefore caution customers). in this tab, you could also permit the scrambling of the connection credentials in the connect line of the script (inserted into the script whilst you click at the join button within the statistics tab and select the information source and enter credentials).
Script commenting is a effective manner to arrange, describe, and disable/permit traces or an entire phase of your QlikView script. you can insert comments and comments within the script code, or deactivate parts of the script code by the usage of remark marks. as an instance, it's far QlikView’s exceptional exercise to encompass an explanatory remark before each Load script for a desk. adding remarks to code is also important for later code evaluate and modifying via different QlikView developers. regularly, code created one month ago might be hard to understand at some point of later months whilst being reviewed (even by the identical developer!). Commented code could be marked by using QlikView as green textual content. Following are a number of the strategies of script commenting to be had to you:
the use of rem (REM, Rem, or rem—it’s case insensitive) earlier than a line
two ahead slashes (//) earlier than a line of code remarks out that line within the script. All text on a line that follows to the proper of // (two ahead slashes) might be taken into consideration a comment and could not be completed when the script is run.
One forward reduce and asterisk before the line and one asterisk and ahead lessen after the lines (/*…< string >…*/) will remark out the complete enclosed code block
highlight the code you desire to mark as a comment or uncomment, right-click on, and pick out both remark or Uncomment from the shortcut menu.
The statistics load editor toolbar incorporates a shortcut to commenting or uncommenting code. The function works as a toggle, if the chosen code isn't always commented it is going to be commented, and vice versa.
The highlighted textual content can be commented out and uncommented by using deciding on the Edit | comment or Edit | Uncomment tabs, respectively, in the script editor command toolbar.
CommentingDo the following:
choose one or greater strains of code that are not commented out, or region the cursor at the beginning of a line.
click on …, or press Ctrl+k.
the selected code is now commented out.UncommentingDo the following:
choose one or more lines of code that are commented out, or vicinity the cursor at the beginning of a commented line.
click on …, or press Ctrl+k.
the chosen code is no longer commented out.
Growing the select statementAssume which you have any database for your pc or server (get admission to, MySQL, Oracle, or otherwise— these illustrations use the unfastened Microsoft get right of entry to 2010 database, Northwind1, on a gadget strolling the 32-bit workplace 2010), assure which you have the perfect driver to your gadget; in case you are running the 32 bit Microsoft office 2010 and accessing the more recent format of the get admission to .accdb file. you will want to download and install the 32-bit Microsoft access Database Engine 2010 Redistributable motive force. let’s stroll via developing a quick script in QlikView’s Script Editor window.To get to the choose assertion conversation, you have to first hook up with a database. the following example illustrates a connection to a Microsoft get admission to 2010 .accdb database document. modify it as you want in your database or records supply kind.
start by way of beginning a brand new QlikView document (click on on Cancel when the pick out records supply wizard appears). go to the Script Editor (navigating to document | Edit Script from the principle QlikView toolbar).
in the information tab in the tools pane of Script Editor, pick OLE DB and check the force 32 bit DB checkbox (we need QlikView to search for established 32-bit connections).
establish the connection to the database and insert the connect declaration in the script through clicking on the connect button at the statistics tab in the equipment pane, and proceeding via the records hyperlink dialog.
select Microsoft office 12.zero get right of entry to Database Engine. On older systems or to get entry to older access .mdb databases, pick the Microsoft Jet four.0 OLE DB driving force.
click on the relationship tab and input the total pathname and filename of the .accdb database. also, enter any connection login credentials if required.
test the information connection. If a success, click on on ok, after which connect statements can be displayed within the script window.
click on the pick out button within the gear pane to continue to the Create select assertion conversation. The Create pick announcement dialog can be populated with the database tables and fields on your selection. If the relationship turned into not successful, troubleshoot the relationship credentials and direction/ filename.
constructing the pick out assertion
once we’ve related to the database, the Create pick announcement dialog seems and allows you to customise and narrow your preferred assertion by using choosing diverse tables and documents.
by means of default, the first table in the listing is chosen and all columns (pick all or choose celebrity with an asterisk) are selected. every select announcement can load facts from one table and a couple of columns (database fields).
In this example, we can pick (Ctrl + click) from the customers table using the subsequent columns: address, city, employer, united states/vicinity, email address, First name, closing name, cell phone, state/Province, and ZIP/ Postal Code. depart checked the preceding Load checkbox and click on good enough.
the weight /pick announcement is displayed within the script area inside the script editor dialog. observe that QlikView syntax load declaration seems above the square select declaration, as shown within the following screenshot:
Strolling your script and viewing outcomes
click on shop and then at the Reload button. The Script Execution progress dialog seems in short and the Script Editor closes, with the principle QlikView display screen (sheet) open to the Sheet residences dialog. this is the collection you'll see every time after reloading a script. it's miles satisfactory to click on on Cancel in the Sheet houses dialog, except you would really like to feature one or more fields to the existing sheet.
Click on upload All and then on good enough to display all of the to be had fields at the sheet. All fields are displayed as listboxes on the main QlikView sheet. pick out the layout | Rearrange Sheet gadgets menu command to organize the listing packing containers. Make a few alternatives and see how the information changes primarily based on those picks. click on at the clear button to reset the selections.
Organizing tabs in scripts
there are many possibilities while selecting the script architecture, however the one maximum widely wide-spread, in the QlikView community, is dividing script tabs via statistics supply and additionally creating additional tabs for the grasp calendar, variables, mapping tables, and additionally cleanup strategies. take into account that the scripts are executed from pinnacle to bottom and from left to proper, so the tab scripts must be accomplished in the precise order.
Now that you’ve seen how to connect with a records source and load fields from database tables, you will be thinking about the way to arrange the tabs within the QlikView Script Editor window. it's going to assist to place a few concept into the script structure up front: how will the tabs be prepared— by using function or through the software tab? How approximately through statistics supply? how will you take care of variables? Mapping tables?
It’s as much as you, and it'll in all likelihood be a hybrid technique. There are two predominant colleges of notion in the QlikView network about tab employer: some say to organize them by using records source and some say to organize them through QlikView application’s (QVW) user interface sheet tabs (for example, every sheet tab can have a corresponding script tab).
The great way to arrange and upload tabs is possibly the department alongside statistics supply traces, without any consideration of the final consumer interface, QVW. remember the fact that the tab order is essential and connections and picks need to float in a logical order (promote or demote tabs as needed and upload new tabs using the Tab menu). you can want to feature different tabs as nicely, such as mapping tables, variables, cleanup techniques, and exit script.
Anything you do, make your tab names and tab order very descriptive and logical, and upload plentiful remarks to describe various parts of the script for less difficult protection later on.