WIXLIB

C REATING CONNECTIONS – STATIC F ILESIn order to extract data, connections as to where the data is located need to be establishedQlik Sense can read a wide variety of data types from a multiple different sources. For anynew source of data, the connection can be configured by clicking the “connection” button inthe top right:From there, connection properties allow Qlik Sense to located the required informationFor static files (Excel Documents, CSV files, XML files, et. al.) stored in folders, theconnection tells Qlik Sense where to find the file,which it will then read and pull into the application.

C REATING CONNECTIONS – L IVE S OURCESFor live sources (like databases accessed through ODBC or OLE DB connections), theconnection lets Qlik Sense access the database directly, querying the database to extractdataStep 1:ChooseType ofDatabaseStep 2:EnterSourcePath &CredentialsStep 3:LoadDatabaseSchema,SelectSpecificDatabase,Name &Create

E XTRACTING DATAFor advanced users, or those who are familiar with their data source, Qlik Sense allows youto manually create the scripts to extract data. However, Qlik Sense also has its own dataloading wizard to help identify, transform, and extract data from sources, which is mucheasier to do for already existing data: After setting up a connection, click on the “Select Data” icon( ) below the connection Similar to opening a folder, this will open a view of the availablefiles at the location you set for that connection (if it is adatabase connection instead of a folder, the wizard will insteadinsert a script to connect to the database) After the file has been selected, Qlik Sense provides a previewof the data In the example shown on the right, you identify the file format(.xlsx), whether the field names are embedded, how many rowsthe header is, which worksheet to use, and which fields to bringin A preview of the data appears at the bottom, along with thescript that will be inserted

BASIC S CRIPTING – LOAD STATEMENTSimilar to a database, Qlik Sense organizes the data it loads into tables. Whether scripted freehandwithin the app or loaded in from a file using Qlik Sense’s wizard, the script syntax to create a table is thesame. Every table loaded into Qlik Sense has several main elements, as detailed below: Title – The top line before the load statement is thetable’s title LOAD / SELECT – This statement tells Qlik to beginloading data from a static source, or to run an SQL queryon a database Field Names – These are the names of the fields from thesource (e.g. column headers in Excel, field names in adatabase) From statement – Defines the data’s source

BASIC S CRIPTING – W HERE C LAUSES & F IELD N AMESOnce you understand the basics of creating a table in Qlik Sense, you will be able to quickly graspadditional methods of adding to and manipulating that data.Many useful functions are build around the load statement shown on the previous slide. Two examplesare the WHERE clause and giving aliases to field names. We’ll continue with our example table to discusshow these work:Renaming Fields For several reasons, you might wantto call a field something differentthan the name it had in the originaldatasetTo rename a field, load the originalfield name and put “as (new fieldname),” after itRemember that field names must beone word, or contained in quotes(“”) if you need the field name tohave spacesWhere Clause Often, you only want to load acertain subset of your dataThe where clause tells Qlik Sense toonly load the data in the precedingtable where a case is trueIn the example here, the whereclause says only load data whereField4 is equal to “Manager”Note that this references the fieldname of the original data

J OINS / CONCATENATESAnother way to link data is to join it to the same table. This is where Qlik Sense’s join comes in – insteadof starting a new table with one shared field, a join can be scripted to add new fields to an existing table A left join keeps all of the data on the original (“left hand”) table, and adds data in the new fields where both the old and the newdata have the same value for the shared field A right join does the opposite – all data in the new fields are kept, while data in the old data is kept where shared An inner join only keeps data from both sets of fields where the values in the shared fields are the same An outer join keeps all values from both tablesIf two sets of data share the same set of fields, data from one source can be appended to another via theconcatenate functionExtract 1Extract 2ResultingTable

AGGREGATIONWhile data values can be calculated on the front end of Qlik Sense in charts and graphs, newfields can also be created in the script that aggregate from the source dataThe syntax for creating these fields is similar to renaming a field from the source data –function(FieldName) as NewFieldNameSome common functions are: Sum() – takes the sum of the fieldCount() – counts the number of records in the fieldAvg() – takes the average value of the fieldMin() – finds the minimum value of the fieldMax() – finds the maximum value of the fieldWhen using aggregation functions in the script, a GROUP BY aggregation field(s)clause is needed at the end of the load function so Qlik Sense knows over whichfields to aggregate the data

AGGREGATION E XAMPLEThe group by clause must include all “dimensions” in the table – in other words, every fieldthat is not an aggregated value is put into the group by statement Note that the field names are the original names from the data source, not the new names that willshow up in the final table

R ESIDENT TABLESAs has been alluded to in previous slides, in addition to loading data directly from data sources, QlikSense can create new tables by loading data that has already been pulled into the applicationThis is called a Resident Load. Resident Loads can be useful to perform additional calculations ormanipulations on already loaded data (like the aforementioned aggregations) or to bring togetherdata from different sourcesThe syntax for a Resident Load is the same as a normal load except for the “FROM” statement –instead of telling Qlik Sense to load from a table, the user tells it load from a “Resident” and then thetable name:Depending on the purpose of the Resident Load, it is often useful to drop the old table once the newone has been createdNOTE: In some instances, it is less beneficial to load data from a Resident Table. For example, QVDfiles are loaded faster directly. However, you cannot perform ORDER operations on QVD files, so aResident Load is required.

M AKING QVD SQVDs are Qlik specific data files. They allow the application to run more efficiency as thedata is compressed and easily processed by Qlik Sense. Any table loaded into Qlik Sense canthen be then stored in a QVD and loaded the same way as other local files.

W HY U SE QVD S? Qlik’s associative data model enables faster loading than many otheralternative data loaders, but can still be a lengthy process when dealing withvast amounts of data. For example, Qlik can only load data from a database asfast the data can be read (i.e. the data loading takes as long as producing thefull results set) To improve the load time, Qlik utilizes a custom type of file called a “QVD” QVDs are compressed data files that can be created within the Data Load Editor of aQlik Sense app and saved like any other file. Qlik Sense can read these files 10-100times faster than other types of data files. This is because data is compressed similarlyto a columnar database—unique values are only stored one, taking up significantly lessmemory One of the best uses for QVDs is when regular queries must be run against adatabase (such as a nightly refresh scenario). In that case, it is helpful to create oneQlik Sense app that runs the query and saves the loaded data into QVD files, andthen a second Qlik Sense app that will analyze the data and create visualizations. Ifthe data is static from day-to-day (e.g. yesterday’s data will not change today), youcan store the full dataset in a QVD and speed up the load process by only loadingincremental changes. That way, the QVD loader can be run each night, and the Analysis app can be refreshedonce the QVDs are created.

D EBUGGERQlik Sense’s built in debugger helps to review the script for errors before a data load isattemptedThe debugger is activated by clicking on theicon in the top right cornerThe debugger shows three views: Output – the output of the script being run, which is the same as the output of a normal data load Variables – any variables created in the script Breakpoints – any place in the script that caused the data load to breakThe debugger runs when the user hits the play button. This performs a “safe” data load thatthe user can monitor for issuesThe debugger also provides the option to limit the data load to a certain number of rowsper table so as to identify issues more quickly, without having to wait to load all of the dataat each step

H ELPFUL TOOLSThe menu of icons at the top of the scripting window offer a series of tools to help inloading dataSearch Function ( ) – The search function offers users a quick way to find certain textwithin the script, either in one tab or in the entire application. It also offers a convenientfind & replace functionComment function ( ) – The comment button allows multiple rows to be commented oruncommented at the same time. By highlighting rows and clicking this button, users cancomment them out (make them invisible to the data load) or uncomment themIndent functions () – Similar to the comment function, users can highlight rows andclick an indent button to tab the rows over to the right or left. This is convenient fororganizing long scriptsHelp mode ( ) – Users can activate help mode, which will link every Qlik Sense command(text in blue) to its relevant page in the Qlik Sense help guide

DATA M ODEL P ITFALLS As discussed in the previous section, Qlik will automatically link fields indifferent tables with the same name. So a general best practice is to keepfield names in a table unique to that table unless deliberately trying tocreate a link. Doing this helps to avoid several common issues: Synthetic Keys – when two tables are auto-linked by more than one field, Qlikcreates a new table bridging the two and combines the linked fields into one“synthetic” key Circular References – if connections between multiple tables travel in a loop, itcreates an issue in the data model.For example, if Table A connects to bothTables B and C, but B and C also share aseparate connection, the result is a loop(circular reference) around all three tables.To avoid Synthetic Keys and CircularReferences, give field names aliases basedupon their intended use.

K EY DATA LOADING F UNCTIONSIn order to aid when more complex data needs arise, here are some key data loadingfunctions, for your reference:“Let VariableName “ – Sets a variable based on a function“Set VariableName “ – Sets a variable based on a fixed value“IF ENDIF” – Set a condition, can be used to conditionally load a set or conditionallyset a variable based on other factors“LEFT JOIN, OUTER JOIN, JOIN” – Works like SQL joins“DROP TABLE” – Drops a table, useful when building QVDs or using temporary tables“ROWNO()” – Returns the row number, can be used to develop keys“CONCATENATE” – Add a full load set onto another“NOCONCATENATE” – Do not allow the data to concatenate with existing dataLOAD INLINE – Hard codes data in manually. Syntax is:TABLE NAME:LOAD * INLINE [FIELD 1, FIELD 2, FIELD 3FIELD 1 Value, FIELD 2 VALUE, FIELD 3 VALUEFIELD 1 Value, FIELD 2 VALUE, FIELD 3 VALUE];

A PPENDIX 2 – K EY DATA LOADING F UNCTIONS CONT ’ DDate Functions: MONTH(), YEAR(), WEEK(), MONTHNAME(), DATE(), DATESTART(),WEEKSTART(), et al. These allow you to format dates to summarize them at different levelsString Manipulation: UPPER(), LOWER(), RIGHT(), LEFT(), NUM(), et al. These allow you to change the format of values or only pull certain partsAggregation Functions: MAX(), MIN(), MAXSTRING(), MINSTRING(), SUM(), et al. Aggregate data based on certain factors Note: These require a GROUP BY function for fields not being aggregatedIf Statements Choose the value to show based on certain conditions Ex: IF(Field1 “x”, Field2, 0) If Field 1 x, then uses the value from Field2, otherwise, enters 0ORDER BY Choose how to order the dataAlias: AS An AS statement allows you to rename a field This is especially useful when you use an above function, it will make the field a more user friendly nameThere are many other functions, please contact me (robert.cooper@kenwayconsulting.com)or consult the Qlik Community (http://community.qlik.com/welcome) if you run into a taskyou believe can be accomplished in the script, but do not know the syntax to do it, chancesare, someone else has had the same problem!

TABLE OF CONTENTS Introductions What is Qlik Sense? Loading Data Building Visualizations Using Qlik Sense Questions

V ISUALIZATIONSAs mentioned in the navigation section, visualizations can be added to theApp Overview by clicking “edit” and then dragging one of the visualizationsfrom the left hand tool bar into the main page. Qlik Sense offers thefollowing types of visualizations: Bar charts & Line charts Scatter Plot KPIs & Gauges Filters Pivot tables Distribution (Pie, Tree Map) Maps

V ISUALIZATIONS – T HE BASICSTo customize a visualization, select it in edit mode, and use the righthand toolbar to make adjustments. Options change by chart type butare generally broken down into: Data – which data fields to display. Generally dimensions are used for theindependent axis, and measures are plotted on the dependent axis. Sorting – how the data values on the independent axis should be sorted. Canbe by measure or dimension, numeric value or alphabetical. Add-Ons – Depending on the chart, additional customization Appearance – Enables the formatting of color, axes, legends, labels, etc.Varies by chart but this is the section where you would insert the expressionto color-code data points or flip the y-axis to the right hand side of the chart.

V ISUALIZATIONS – BAR & L INE C HARTSData can be displayed in a bar chart, line chart, or a combination When the chart is selected, the “data” section on the right lets you choose whichdimensions to plot on the axis and which measures to calculate for the bars/lines To switch between bar, line, and combo charts, drag the other type of chart on top ofthe existing visual and select “convert to” Change the orientation of the chart in the “appearance” tab on the rightBar ChartLine ChartCombo Chart

V ISUALIZATIONS – S CATTER PLOTSData points can be plotted on a scatter plot When the chart is selected, the “data” section on the right lets you choose whichdimension to use; for scatter plots, the dimension is an individual dot The first measure is displayed on the x-axis axis, while the second measure is displayedon the y-axis; the (optional) third measure defines the size of the dot

V ISUALIZATIONS – G AUGES & KPI SKey values can be displayed on a KPI or a Gauge KPIs (Key Performance Indicators) show one measure in bold numbers The size and format of the number being displayed can be adjusted, just like values ona table Gauges show where a measure lies on a range from a minimum to maximum value Gauges can be displayed as radial or a straight bar, and the min/max range and thelimits of each segment can be customizedGaugeKPI

V ISUALIZATIONS – F ILTERSAdd filters to a page for easy reference While any table/chart in Qlik Sense can be filtered by clicking on the data beingdisplayed, it is often useful to add a specific filter object for quick reference or toenable filtering on a dimension not displayed in a chart The filter object displays box that can be clicked on to display possible values to filter By default it displays the name of the underlying dimension; a different label can beusedFilters above a barchart

V ISUALIZATIONS – P IVOT TABLEData can be arranged in pivot tables to enable drill-down views Just like in Excel, data can be put into a pivot table so it can be drilled into Dimensions can be ordered into rows, while multiple measures can be added ascolumns Each dimension can be expanded by clicking the “ ” icon

V ISUALIZATIONS – D ISTRIBUTIONSDimension breakdowns can be illustrated by pie charts or treemaps Pie charts in Qlik Sense work like you would expect – it shows how much of eachsegment comprises the whole picture in a pie-shaped visual Pie charts can be displayed as a solid “pie” or as a ringed “donut’ Treemaps work similarly, but can show an additional level of detail by displayingdifferences in color as well as sizePie ChartTreemap

C USTOM V ISUALIZATIONSQlik Sense has exposed a set of APIs and SDKs that allow users to createtheir own objects in Qlik Sense.Pre-made Qlik Sense add-ons are available on the Qlik Market athttp://market.qlik.com/

C USTOM V ISUALIZATIONS (CONT ’ D)These new objects can be incorporated into Qlik Sense dashboards thesame way as the aforementioned objects!The featured product here is called Narratives for Qlik by Narrative Science, more information on them is in the Appendix.

S ET A NALYSISAggregation functions normally aggregate over the set of possible recordsdefined by the current selection: Sum(SALES)But an alternative set of records can be defined by a set expression: Sum({ Region {‘E’} }SALES)In a nutshell: Conceptually similar to a filter selection Provides a method of defining groups (sets) of information that areindependent of current selections Must be used in an aggregation function Always begin and end with curly brackets { }Additional Information is available in the Appendix

S ET A NALYSIS (CONT ’ D)The syntax is broken down into 3 c

What Is Qlik Sense? Qlik Sense is a data visualization tool by Qlik, a business intelligence (BI) software company founded in 1993 Qlik Sense builds upon the successes of another Qlik product, QlikView, to combine a powerful data processing engine with a user-friendly user interface: