Qlik Sense Tutorial: 3.5 Hours of Beginner to Advanced Qlik Sense Training

subscribe and click the Bell icon to turn on notifications we've made the files the instructor uses in this tutorial available for free just click the link below in the video details to get these in this day and age when Big Data is practically everywhere visualization is also in high demand since its vital in conveying data's meaning and extracting analysis without it we will have to go through hundreds of lines of text to understand a specific point visualization makes it easier for us to detect patterns Trends and outliers in a mountain of data through Visual context data becomes comprehensive and easier to process for the human mind many companies have developed software and tools for visualization and one of the leading platforms is qlik sense qlik sense is a self-service business intelligence and visual analytics platform developed by click it helps you load and modify your data models easily via drag and drop without manually editing scripts and it enables you to explore your data freely with the help of its unique associative engine it has a simple user interface that lets you create and personalize visualizations on the fly with a responsive design that automatically fits dashboards into your phone tablet or laptop this platform has also been recognized by Gartner as a leader in its magic quadrant for analytics and business intelligence platforms based on a neural analysis of different vendors and evaluated using a uniform set of criteria qlik sense has a lot of similarities to its predecessor click view but it also has its differences click view is built for guided analysis wherein end users are provided with an app that contains the previously defined data models with different visualizations it also offers better control over the design and customizability of its applications thus making it suitable for Technical and trained developers qlik Sense on the other hand is built for self-service analytics where the user is given the opportunity to integrate individual data and create their own apps it is user friendly and easier to use making insights quick and easy to produce even for beginners in terms of its front-end click view is.net based while qlik sense is developed with HTML5 in using filters click view requires it to be derived and allocated but that is not the case for qlik sense for their visualizations click view has a pixel by pixel design layout with unlimited space to cover while qlik sense has limited space options and you can only resize its grid click view has more options to customize the charts with layering containers and objects while qlik sense is created for a simpler design and modern charts which gives more focus on the analysis side click view has object specific expressions and dimension while qlik sense introduces the concept of Master library for Dimensions while is useful for fields that are always used in the visualization click view uses online analytical processing olap capabilities to perform analytical operations which is not available on qlik sense click view does not have qlik senses storytelling and data mining functionality storytelling lets you combine reporting presentation and exploratory analysis techniques to create and collaborate you can take snapshots of the data discovered and edit it with effects in order to emphasize specific points of interest additionally qlik sense has a smart search function that lets users type in phrases to locate reports and it can also generate possible charts via Insight advisor click view also does not have the augmented intelligence feature of qlik sense click view has great features on Advanced analytics and integration but qlik sense is more expensive you can also create and add extensions to qlik sense written with HTML or JavaScript using qlik sense in your own projects or deploying it for your organization provides a lot of advantages qlik sense makes analytics easy even non-technical people can learn the tool easily with its clean drag and drop interface and a large selection of charts tables and different visualizations it is also perfect for small and large corporations as it has no limits on the number of data sources and data volumes its multiple connectors allow you to connect to different data sources and gives your organization a well-rounded view of its data it instantly outputs actionable insight qlik sense enables users to use the search function to look at possible visualizations from its loaded data in-memory technology enables the data to be processed in real time so you don't have to wait long for delegated analysis using the association engine you can also view how each data set are related to one another with the platform you can easily make new discoveries and identify proper actions to be made efficient collaboration sharing your insights to colleagues is fast and easy with the interactive mobile analytics that are available both online and off presenting to stakeholders is also a breeze with the storytelling functionality which lets you create slides to communicate your findings embedded Analytics qlik sense gives users the flexibility to integrate it to different types of applications portals and workflows users can quickly access relevant data and insights without having to switch to a different analytics application its open apis also lets you customize analytics Solutions and develop apps and extensions the platform's Insight advisor can also use the predictive capabilities of other data science tools such as R and python secure centrally managed and can scale across the cloud qlik sense offers a lot of functionalities without compromising on security it is centrally managed and users can create their visualizations that draws on secure libraries and governed data it can also be combined to hybrid multi-cloud and on-premise deployment with the use of its multi-cloud architecture click offers three types of environments for qlik sense qlik sense desktop qlik sense Enterprise on Windows and Cloud Editions qlik sense desktop is a single user solution it is computer-based and you can only run it on your local computer the use of multiple screens or tablets is not supported you can download it for free and install it on your PC however you cannot share its apps to other users aside from copying the file to their PC there is also no autosave function so you will have to save your apps manually and dynamic views are not supported duplicating apps is also not supported on the desktop environment qlik sense Enterprise is an organization-wide solution it supports a full spectrum of analytics use cases on a multi-cloud platform it can either be server-based or SAS and is mobile friendly it needs a license and all applications will be centrally located on the server This Server will be the one responsible for security governance and other admin functions the registered business users will have access to it through their web browsers or Via mobile app it has an automated data refresh and rules based security that enable you to ensure everyone sees what they need and nothing more in an easily managed way you can also attach data to it to create an app but only admins can upload qvf files into the Hub Cloud editions of qlik sense is a SAS service it does not have a central dedicated server for your organization but it can be accessed by your users via web browser same as the Enterprise version users can share or collaborate create their dashboards and upload their qvf files it is also available from any device and you do not have to deploy it or maintain it unlike qlik sense Enterprise you can't automate the data refresh and all users can see every created app scheduling automated data reloads is also available in this environment all three environments use the associative engine and can create and interact with your own visualization always make sure to keep in mind the number of users scope of bi projects and the size of your organization before choosing the environment that you will deploy starting up qlik sense directly sends you to the hub The Hub is always the starting point and it is where you can find all of the apps that you have access to app is the term used to describe a qlik sense file which is created when you develop a visualization or a dashboard in navigating The Hub the top portion contains the toolbar that has the link to the navigation menu the search box and the global menu clicking on the search text box lets you type a keyword which filters out the list of apps that contain the keyword that you entered in their title or description the three dots icon shows the global menu this has the links to navigate to either the dev Hub which is used to create widgets extensions and mashups The Click help page and the about section which contains the qlik sense version an environment that you are currently using as well as the list of third-party software that is vital to keeping qlik sense running clicking on the three bars icon lets you toggle on or off the navigation menu in this navigation menu the first part shows you login user ID clicking on it lets you navigate to your profile and lets you log out of your account under the user ID it has the personal and streams tab for qlik sense desktop users you will only see the personal tab since it is a local deployment for qlik sense Enterprise users you will have access to both personal and streams tab but the stream's location will depend on which ones you have access to in the personal tab it lists all of your created apps under the works section if you have published the app to a stream it will also be shown under the publish section publishing means to move an app to a stream where other users can access or view it qlik sense desktop does not support publishing so its users will not have the published section the stream is the location where access is restricted to specific users set by your administrator they can also add more sections to the stream to support what categories your organization will need finally The Hub has the work area this is where the list of apps is located the options on the top has the button for creating a new app this will only be available if you have permission to create apps next to it is the sort button the arrow can be toggled to either sort it to ascending or descending you can also set it to sort by app name alphabetically or based on created by date the last two buttons let you edit the view of apps either as a grid or as a list grid view only provides the name of the app and its image you will have to click the info button of each app to view additional information such as the last date loaded and its owner enabling the list view on the other hand shows the complete information for each app selecting or opening one of the apps displays the app overview this is where you can view and manage the contents within the app the top toolbar contains the navigation menu a toggle which displays and hides the app info and a navigation for prepare analyze and narrate tab below it we can see the app information and a button for editing this lets you change the app name and the description the app options button below it lets you customize the appearance and the styling of the app below the app overview is a set of tabs that lets you show the sheets bookmarks and stories you created within the app to its far left you also have the buttons for creating a new sheet and a toggle for Grid or List View selecting one of the sheets leads you to the sheet view this is where you can develop or edit your apps analyze and take snapshots of the visualizations always keep in mind if your app is already published or not because it will affect the functionalities here after publishing an app you will not be able to navigate to the data manager or data load editor add data or edit the app and open app options in this view we have a selection bar the bar is used when we are working with selections at the same time you can save your selections as a bookmark using the upper right bookmark button from the toolbar so you can use or filter the visualization automatically to your desired selection when you get back to it on a later date besides the bookmark button is the edit sheet this lets you modify the current visualization sheet and you can add more charts and custom objects to it the next button shows the list of all sheets available in the app you can also create a new sheet from here if you have the access to do so the last button lets you navigate Between the Sheets available on the app the arrow left icon lets you go back to the previous sheet while the arrow right icon lets you view the next sheet foreign over a chart in the sheet shows three additional buttons the first one is for taking snapshots this will take a screen capture of the current chart that you can use later for stories next is the exploration menu by clicking it you can enlarge the current chart and view the dimensions and measures used to create it you can also change its visualization properties such as showing or hiding the labels without making selections or editing the sheet itself lastly we have the full screen button which lets the chart cover the full sheet this lets you explore the data on a detailed level and lets you work with one visualization at a time let's click on edit button to explore the edit View in this view we have the assets panel located on the left this provides a set of items we can use for our visualizations it also contains the list of charts available in qlik sense as well as the master items and custom objects for each selection there is a search box on top which helps you find certain Fields charts and objects based on the keyword below the custom objects tab there is also a variable overview that lets you view the current variables in the app or add a new one you can also show or hide the assets panel by clicking on hide or show assets button on the bottom on the far right is the properties panel this shows the sheets property if no charts are selected but it will show the chart properties if you have selected one this panel lets you edit the data sorting appearance and other options of each chart the bottom toolbar contains buttons to edit items here you can cut copy paste and delete the objects in the visualization the two far right button is for undoing or redoing the actions you've done while editing the sheet right clicking on a chart will show the options menu this reveals a quick link for editing the item or adding the current chart to the master items list do take note that the options will change whether you are editing or viewing charts and it can also change based on the access that you have the prepare tab contains the views for preparing the data that you will use in the visualization let's click on its data manager tab to expand on this View the data manager lets us add edit and delete data sources you can also associate the data if you added multiple sources it has two types of views that you can toggle using the button on the right the associations view shows the tables as bubbles one table is shown as one bubble and its size represents the size of its data the right panel of this view shows the recommended associations for the data source in this example we don't have any recommendations because we are only using one data source on the other hand the table view shows the data tables in the app it also shows its file name and the number of fields available for both views a data preview panel can be seen on the bottom below this is the set of table actions you can use this to edit reload and delete a table let's click on the edit button from the table actions to show the table editor the top table has the table editor toolbar you can rename the table add calculated fields and update the table from the data source the next panel contains the data preview here you can see all the tables available from the selected menu you can also rename the fields change the field type and other actions using the general and field menu button for each column below the data preview is the data profiling card this shows you the summary of the data let's look at another prepare view by navigating to the data load Editor to do so click on the prepare tabs drop down and select data load editor the data load editor lets us View and edit the script that connects the app to the data source the script can be edited manually or generated automatically by the data manager the upper right toolbar has the debug button for debugging the script and a load data button to run the script in order to load the data on the right hand side is the data connections you can use it to create and save shortcuts to sources that you commonly use in the center is the script editor this is where we can edit the script and set additional formats or actions the script is divided into sections as seen on the left side panel then let's head to the data model viewer click on the prepare tabs drop down and select data model viewer the data model viewer shows an overview of the structure of the loaded data the metadata is also available here the toolbar buttons let you collapse reduce size and expand the table View you can also toggle between the internal table view which shows the qlik sense model with synthetic fields and the source table view which only shows the data model of the source data table the layout menu beside the toggle button lets you modify the layout of the tables to either a grid or Auto or just restore the layout to original form the bottom panel is the preview pane that contains the details of the selected table it contains sample data and the metadata let's head on to the Insight view under the analyze drop down select insights the insights view lets you search and display visualizations using a keyword the Insight search field is where you can type Fields questions or field names to search for charts clicking on search will generate charts that are related to the keyword you typed you can also use the assets panel from the left side to select a particular field or Master item and generate the possible visualizations using that field the properties panel on the right contains configurations for the Insight charts finally we have the storytelling view from the narrate tab the storytelling view lets you create or open a story under the app here you can build stories for presenting play a story or publish one the format is similar to that of a PowerPoint presentation you can click on the plus button below to add a new slide to your existing one on the top right corner it contains the list of stories available and it also lets you create your own by clicking on create new story the center blank canvas contains the story's current page or slide you can select the page from the story timeline which is available on the left part of the screen this contains the thumbnails and overview of the story in order to play the story you can click on the green button above the timeline the right panel contains several tools and libraries to help you customize and create your story you can also insert the snapshots you have saved from the sheet view the bottom item toolbar lets you cut copy paste or delete a selected item in the slide there's also a redo and an undo button on the bottom right to modify your actions starting up qlik sense directly sends you to the hub The Hub is always the starting point and it is where you can find all of the apps that you have access to app is the term used to describe a qlik sense file in the personal tab it lists all of your created apps under the works section if you have published the app to a stream it will also be shown under the publish section selecting one of the sheets leads you to the sheet view this is where you can develop or edit your apps analyze and take snapshots of the visualizations the data manager lets us add edit and delete data sources you can also associate the data if you added multiple sources it has two types of views that you can toggle using the button on the right the right panel of this view shows the recommended associations a data preview panel can be seen on the bottom below this is the set of table actions you can use this to edit reload and delete a table the top table has the table editor toolbar you can rename the table add calculated fields and update the table from the data source the data load editor lets us View and edit the script that connects the app to the data source the insights view lets you search and display visualizations using a keyword the Insight search field is where you can type Fields questions or field names to search for charts clicking on search will generate charts that are related to the keyword you typed you can also use the assets panel from the left side to select a particular field or Master item and generate the possible visualizations using that field before creating any kind of visualization the first thing we need to have is data without a proper data model we won't be able to develop insightful visualizations loading data into an app is quick and easy in qlik sense let's start by creating a new app in the hub click on the create new app button if you're using qlik sense Enterprise make sure that you are under the personal work tab then type in a name for the app once done click the create button then click on open the app on doing so the platform prompts us to the app preview where we have two buttons for adding data the first one is the add data from files and other source this lets us quickly add data from the different data sources without having to type a script the second one script editor directly takes us to the data load editor where we can edit and type a new script let's try using the add data from files and other source first in the new window we are presented with different ways to add data the first tab lets us connect to a new data source using various connectors available in qlik sense you can use the search box to type in a connection name and you can also change the view to make the tiles larger or smaller the in-app tab lets you select from the data sources that are available in your app this includes files that you have already attached to your app we also have the option to create a new source using manual entry the file locations tab lets us select data from a specific path in our local or network drive for cloud and Enterprise users this can also be from a shared Drive defined by the administrator qlik sense supports different types of data files such as delimited Excel HTML KML fixed record files click view data files and data exchange files and XML files the data content tab lets us select from The Click data Mart's normalized data which is taken from public and Commercial databases let's select the new tab first to upload a new file from your file explorer drag the file into the upload area this will show a data preview window in this data preview window we can select the unnecessary Fields by deselecting The Columns you can also search for the field name if there is a lot of fields in your data set on the second tab we are given the option to add a filter to our data this will restrict the data that will be used for the app to add a filter you need to specify the table the field condition and the value that will be filtered we will not use this function for now and head back to our data preview the functions on top of the preview window are helpful if you have a data set that has extra headers and has a different delimiter depending on the file type qlik sense presents different options to correct the data format issues for example this comma delimited file has three extra headers on top in order to properly load every table we need to have the field names specified on the top row to fix this we need to specify under field names it has embedded field names and then enter the header size as 3 to remove the three extra headers now we can finally load this data into our app the add button has a three dots icon beside it where we can toggle to enable or disable the data profiling if it is enabled qlik sense can automatically derive date groupings from fields that has data or time data for example it can automatically create the year month and quarter grouping for you without having you specify each and every grouping you need for every data field it can also automatically derive geodata from city names and other geographical data which helps you plot the data properly on a map we will keep data profiling enabled and click on add data enabling the data profiling takes us to the data manager view the data manager view shows us associations between data sets by using Bubbles and Association paths let's add another source file and look at how we can associate our current data set with the other to add a new data source click on the plus button on the upper left part of the screen doing so leads us back to the add data window let's select file location then navigate the path to our data file once we are on the correct folder select the file to head to the data preview check your files if it is correctly formatted enable data profiling then click on add data then do the same steps again for the third data file now we have three unconnected bubbles that represent our three data sets before associating them let's rename the table first to avoid confusion to rename a table select one of the bubbles then click on the edit this table button on the bottom of the toolbar now that the table editor is visible double-click on the table name on the upper left corner above the file name our table is currently named as sheet1 let's rename this to orders as this data file contains the order information once done click the stop editing button on the bottom toolbar let's do the same process for the other data sets click on the sheet1-1 bubble then edit table double click on the table name and rename it to bike products once done click on the stop editing to save the changes and that takes us back to the data manager view do the same steps for the third data set but rename it to shops associating the tables helps us relate both data sets and use them properly in our visualization for example I will not be able to look at each order's price if it is not related to the bike products data set since it contains the price list for each type of product there are two ways to associate a table first is by the drag and drop method let's click and hold on the bike products bubble notice that upon clicking on the bubble a ring appeared on the other two bubbles this represents how strong the recommended Association to each table is green means that qlik sense is very confident in the association yellow means fairly confident and red means it has no recommended association between the selected table and the other tables since we are going to associate the bike products to the orders bubble we will drag the bike products bubble to the orders bubble and release this creates a link between the two bubbles which indicates that these two are now Associated the data preview window shows us The Columns for both data sets and the key used to associate both tables is indicated in the center here we have the byte.id as the field present on both tables and the colored ring that indicates that qlik sense is very confident of this Association the second way of associating tables is by using the recommended Association panel we have one Association recommended where it states that we can associate the shop table to the orders table using the shop ID field clicking on this Association shows you a preview of how the two tables will be linked and the level of confidence that it has for this Association the preview Association is marked in blue and you can click on each of it to show the confidence level of the recommended Association to apply it we only need to click the apply button now that we have the needed Association let's load the data into our app the asterisks on their name indicates that these tables have not been loaded yet the load data button is also colored green to signify that you need to load the tables into the app to include them in your visualization click on the load data then edit the sheet let's look at how the association affects a table and why we need to relate our data drag a bar chart into the sheet set the dimensions to model and the measure to sum of quantity the model field comes from the bike product table well the quantity field comes from the orders table once both Dimension and measure has been indicated the bar graph is now visible notice that the quantity for each model has been marked with the proper sum of quantities sold since we have related both tables but let's look at what happens if we remove the association let's go back to the data manager view by clicking on the prepare tab to remove the association drag the bike products bubble away from the orders until the link is removed then click on load data to apply all changes back to our sheet notice that the sum of quantity is now the same throughout the different bike models since we have removed the association between the two Source tables our bar graph does not have the correct data to properly plot the height of each model thus resulting in an incorrect visualization this shows how correct Association can greatly affect the visualization you make here are a few things to take into consideration when associating multiple tables if you have loaded two Fields containing the same data but with a different field name from two different tables it's probably a good idea to name the fields identically to relate the tables the data manager treats Fields with the same name and data type as something that should be Associated and will mark it with high confidence if you have loaded two Fields containing different data with identical field names from two different tables you need to rename at least one of the fields to load them as separate fields adding data sources or tables from the data manager automatically generates a script code this can be used on a separate section of the data load editor click on the drop down of the prepare Tab and navigate to the data load editor in the data load editor a new section has been created called auto-generated section it has a lock icon that indicates that you cannot edit the script unless you unlock the section doing so lets you add more script sections and develop code that enhances and interacts with the data model you have created in data manager but do take note that some script codes can also interfere with the data manager's data model and create some problems we will tackle such cases in future lessons now let's try adding data by using the script editor create a new app then add data using the script editor button this takes us directly to the data load editor all newly created apps have a main section by default it contains several set statements that has reserved variables which are used for the default settings of the app in order to create or load a new data source to this app using this view we will use the data connections panel on the right click on create new connection button this opens a window where we can either use data connectors or navigate to a folder click on folder to show a new window in this window we need to specify the path of the folder where our data source files are saved navigate the directories until you reach the folder for your files as you can observe the files itself are not clickable this is because we are still establishing the connection into the folder type in a name for your connection then hit create once done the new connection to the folder will now be added to the data connections list there are three icons under each connection the first is for selecting the data next is for editing the connection and third is for deleting the listed connection to add the files to our created connection click on the select data icon under the connection that we have just created this prompts us to a window where we can select the specific field that we want to load click on the file and then click select doing so shows us a window similar to the data preview except for the fact that this one has an additional window to show the script preview changes on the data preview will directly affect the script we have below for example if we deselect F1 it removes F1 from the load script and if we rename quantity by double clicking on it and typing in qty it will add an as function to the script which will rename it as qty click on insert script once you're done with the data preview we will repeat the same steps to add the byte products table and the shop table the script for the file we have loaded have been directly appended below the set statements in the main section this data load script represents the instructions for connecting to data sources and retrieving data from those sources in the script the tables and Fields to load are specified we can also cut this section of the load script and put on a separate section to make it more organized select the load scripts press Ctrl and X keys to cut in then click on the plus button to add a new section name the new section as bike data Excel file then paste the script by using Control Plus V key do take note that this script has not been executed yet so we still have to run it to load the data into our app to run the script you've created click on the load data button in the upper right corner of the window this will show the data load progress window where you can see how long the script was run the name of the sheet fetched from the script and if there were any errors in the script load you can also review this text by clicking on the output button on the lower part of the window in order to add associations to the tables navigate to the data manager since we added the data via script the data manager does not recognize it immediately you will need to click on run load script to view the table's bubbles this preview panel indicates that the tables were added using data load script which limits the capabilities of the data manager you need to click on synchronized scripted tables to apply the association function of this view upon doing so the recommended associations will be automatically applied finally click on load data to finish the process another way of loading data in qlik sense is through manual entry this is used if you want a limited set of data from a different source you can also use it if you have a small amount of data that you wanted to add to your visualization wherein it would be faster to insert manually rather than importing from another data source for example I have a bar graph which has the total sales per branch code I wanted to display the branch location instead of code a b and c so instead of going back to my data file and replacing the branch code I'll need to create a new data source that will contain the specific location for each branch code in order to do so we have to add the new data source first if you are navigating from the sheet view you can navigate to the add data window by clicking on edit sheet selecting fields on the assets panel and click on the add button once you are on the add data window select manual entry on the in-app section doing so takes us to The View where we can type in our data first type in a new table name we will name it as Branch information then we are presented with two black columns and one row for the data the top row should contain the column name let's name the First Column or field as Branch for the code and the next column as branch location typing in a new field name for the second column automatically shows as a new blank column if you wanted to add more Fields once we're done with the field names let's type in the data type in a under Branch type in Rosario for the location for the second row type in B under branch and type noveleta for the location finally type in C on the third row for branch field and Cavite as its location tables that we created manually can be edited in a later time to add new data or remove the typed content you can also copy paste directly from a web page or an Excel sheet to load the manually entered table click on add data this takes us to the data manager view where we can set the association between the tables the recommended Association list shows that we can associate the two tables by using the branch field the green ring indicates that it found a very confident association between the two tables drag the branch name bubble into the supermarket sales to associate them once done click on load data let's edit the sheet to check if we can now use the branch location field the first Branch field that was originally the dimension of the bar graph is now marked as invalid because it now has a prefix of table name in its name let's replace the invalid Dimension with the branch location that we entered earlier go to the assets panel select field and drag the branch location field into the bar graph doing so shows a menu where we can select what action to apply to our field select replace invalid Dimension now we have the branch location added using the manual entry click on done to finish another way of creating a manual entry table is via inline load script on the data load editor navigate to the data load editor and create a new section called manual source the first part of the script is to write the table name type in product underscore category followed by a colon the color coding of the script should indicate the table name in purple next type in load asterisks inline this is a script code which indicates that we will type data within the script the words load and inline should be in blue to show that it is part of the program script code then type in an Open Bracket followed by the column names for our field names enter product code space comma space segment this means that we have two field names called product code and segment the next lines will contain the data press enter key to go to the next line and type in 0 1 space comma space Hardware this serves as our first row of data press enter to go to the next line and type 0 2 space comma space software then press enter again and type 0 3 space comma space services to signify the end of our data close it by using a close bracket then a semicolon once done click on load data to add it to your app and check if there are any errors having multiple data sources on your app can be confusing to manage the data model viewer of qlik sense helps you understand how your current model looks it gives you an overview of the data structure of your app view its detailed metadata and create master dimensions and measures from the fields let's start by creating a new app and adding our data source our department store file currently has nine sheets to load all of them select the check box on each sheet and click on add data qlik sense reads each sheet that we have added as a separate table thus we have nine bubbles on our data manager View the recommended associations tab has suggested seven associations to our data set let's click on apply all and then click on load data once done click on edit sheet and navigate to the data model viewer the data model viewer presents each of our table using a box the table name is the title in Gray and the fields are listed Inside the Box the fields that have a key on its name are the key Fields used for associations while the lines show which tables are associated to each other let's move the tables first to get a clear view of our data model to move a table click and hold on one and drag it to a better spot once you are satisfied with the layout of the tables you can click on the lock button located on the left side to lock its position and size so that you don't have to fix the layout again when you add a new data source to the current APP notice that locking it will disable the expand and collapse button together with other layout options to unlock it simply click the button again the collapse all button will minimize all of the tables and show the table name and lines only the show linked fields on the other hand will show the table name and the linked or key fields of each table the expand all button will show all of the fields in the table these three resize buttons will be useful if you have several fields on your tables and want it to have a simpler view of the data model you can also automatically adjust the layout of the table instead of having to drag each and every table to their proper position under the layout menu on the top right you can choose between automatically arranging it either as a grid or Auto grid layout will arrange it as a grid while the auto layout will arrange it to fit all in the window the restore layout will revert it to the layout State present when the data model viewer was last opened selecting one of the tables will let you inspect its details under the preview pane it also highlights the other tables that are associated to it clicking on the table name will display details of the current Table such as number of rows Fields keys and a preview of the data it holds selecting one of the fields within the table will provide the metadata of the field for example let's click on the order ID under the order details table density is the percentage of records that have non-null values as we can see our order ID does not have null values which is necessary since we are using it as a key field for our association the subset ratio below it is the number of distinct values of the field found in this table as compared to the total number of distinct values of this field in other tables in the data model basically it shows the percentage of unique values within the field again this is necessary for the key fields the preview panel also shows us the amount of distinct and non-null values together with the tags of the field it also shows a preview of its data highlighted in the table preview on the right this information is valuable not just for checking the key Fields but also for understanding how your data can affect your visualization for example not everyone from the employees table has a sales Target because its density is 19.1 percent only some of the employees listed may not be sales Personnel that have specific quota to reach this also means that you cannot make a kpi chart for the Target sales for every employee but you can filter them later on the preview pane also lets you add the selected field as either Master Dimensions or Master measures for example the order date will probably be reused several times not just on my own app but by other uses as well to add it to the master items click on either add as Dimension or add as measure it will show a new window where you can customize the name labels tags as well as a description for the new Master item once done hit create this item will now be available on the master items tab of the assets panel when you edit your sheet users will also have access to these Master items but they will not be able to modify them before creating any kind of visualization the first thing we need to have is data without a proper data model we won't be able to develop insightful visualizations loading data into an app is quick and easy in the hub click on the create new app button then type in a name for the app once done click the create button then click on open the app upon doing so the platform prompts us to the app preview where we have two buttons for adding data the first one is the add data from files and other source the second one script editor directly takes us to the data load editor where we can edit and type a new script in the new window we are presented with different ways to add data the first tab lets us connect to a new data source using various connectors available in qlik sense the in-app tab lets you select from the data sources that are available in your app this includes files that you have already attached to your app we also have the option to create a new source using manual entry the file locations tab lets us select data from a specific path in our local or network drive from your file explorer drag the file into the upload area this will show a data preview window in this data preview window we can select the unnecessary Fields by deselecting The Columns you can also search for the field name if there is a lot of fields in your data set on the second tab we are given the option to add a filter to our data the data manager view shows us associations between data sets by using Bubbles and Association paths to add a new data source click on the plus button on the upper left part of the screen there are two ways to associate a table first is by the drag and drop method let's click and hold on the bike products bubble notice that upon clicking on the bubble a ring appeared on the other two bubbles this represents how strong the recommended Association to each table is since we are going to associate the bike products to the orders bubble we will drag the bike products bubble to the orders bubble and release this creates a link between the two bubbles which indicates that these two are now associated the second way of associating tables is by using the recommended Association panel we have one Association recommended where it states that we can associate the shop table to the orders table using the shop ID field now that we have the needed Association let's load the data into our app the asterisks on their name indicates that these tables have not been loaded yet now let's try adding data by using the script editor create a new app then add data using the script editor button this takes us directly to the data load editor all newly created apps have a main section by default it contains several set statements that has reserved variables which are used for the default settings of the app click on create new connection button this opens a window where we can either use data connectors or navigate to a folder click on folder to show a new window in this window we need to specify the path of the folder where our data source files are saved type in a name for your connection then hit create once done the new connection to the folder will now be added to the data connections list there are three icons under each connection the first is for selecting the data next is for editing the connection and third is for deleting the listed connection since we added the data via script the data manager does not recognize it immediately you will need to click on run load script to view the table's bubbles you need to click on synchronize scripted tables to apply the association function of this view finally click on load data to finish the process having multiple data sources on your app can be confusing to manage the data model viewer of qlik sense helps you understand how your current model looks qlik sense reads each sheet that we have added as a separate table thus we have nine bubbles on our data manager view the recommended associations tab has suggested seven associations to our data set let's click on apply all and then click on load data once done click on edit sheet and navigate to the data model viewer the data model viewer presents each of our table using a box the table name is the title in Gray and the fields are listed Inside the Box the fields that have a key on its name are the key Fields used for associations while the lines show which tables are associated to each other selecting one of the tables will let you inspect its details under the preview pane it also highlights the other tables that are associated to it creating a new app is easily done with the push of a button from The Hub for qlik sense Enterprise you can click on the create new app button this will show a window where you are prompted to type in the name of the new app once you have the name click on the create button to direct you to the app page for the cloud version of qlik sense click on the add new button in the upper right corner and select new analytics app under the create new content section this prompts you to a window where you can set the name place where the app will be published an optional description and tags once done click create to go to the app page to upload an existing app file created outside of the environment in the cloud version of qlik sense click on the add new button from The Hub and select upload apps it will show you a new window where you can drag and drop the app file or use the browse button to navigate into the path that contains your app apps read by qlik sense have a qvf or qvw file extension for qlik sense Enterprise users cannot upload an external app without having admin access the admins are the only ones able to import apps through the click Management console users are still able to publish to a stream duplicate and delete their apps by right-clicking on a specific app and choosing one of the actions from the pop-up menu you can further customize the look of your app through the app information panel since we want to see how each sheet will look while customizing it is better to edit the app information on the sheet view where you can see your visualizations click on the three dots icon or the app name to show the app information then click on the gear icon on the right to open the app Options under the appearance section you can toggle the reading order from right to left or left to right this will move the labels titles and Legend from either the left or right alignment below this option is the selection for three different app themes sense classic provides a more compact view of the charts and has limited space between each item sense Focus has more padding and spacing for the objects it also has designated spaces for Titles since Breeze is the same layout as that of sense Focus but provides a different color range the sheet title can also be customized from this panel the first option lets you set either a single block of color for the title's background or a gradient of two colors the colors can be chosen from the default palette selection by typing a hex color or by using the color wheel you can also insert an image or a logo to your title by clicking the image icon this will take you to the media library where you can select existing images from in-app and default images or upload one from your local PC the alignment of these images will depend on the selection you choose from the options you can place it at the left middle or right of the title text finally the font color option lets you change the title text color same as earlier you can choose from the default palette type in a hex color or use the color wheel you can also replace the default thumbnail to make your app easier to distinguish to change it click on the edit button above the gear icon then click the image icon on the thumbnail this leads you to the media library window where you can choose from the in-app and default images or upload your own from your PC once you've chosen your new thumbnail click on the plus button when you hover on the image to add it as the thumbnail the optimal aspect ratio of the image is 8 to 5 based on width to height click sense will only read images in PNG JPEG and GIF formats from this edit option you can also change the app's title and description by replacing the text click the check button to apply your changes let's get back to the app that we have newly created let's add the data that we will use for the app for this lesson we're going to use the department store data in Excel and focus on four sheets namely the category products order details and Order headers once checked enable the data profiling and click on add data in the data manager view we are presented with two associations the product table to the order details using product ID as the key and the category to products table using the category ID both recommendations have strong confidence so let's apply both and load the data upon loading the data a new sheet has been created for us click on the edit sheets button to start when we are creating a new app it's a good practice to First build a structure of empty sheets where each sheet represents an idea or a goal this is important if you have a large data set and have a big dashboard in mind structuring the sheets beforehand will give you a good overview of your app for your first sheet we will name it as sales since I wanted to focus this sheet on the numbers that the department store gained and how it has changed throughout the years to edit the sheet name from the edit sheet view click on show properties then navigate to the sheet properties and type your sheet name under title we will then have the other sheets focused on other categories such as the orders and the shipping information so instead of having one big sheet with all of the data condensed to a page we can focus on one purpose for each and highlight the important information a sheet is where charts graphs and objects are placed for data visualization a qlik sense app can contain many sheets but regardless of the amount the selections that you make can affect the other visualizations on a separate sheet if you wanted to create a sheet just click on the sheet drop down to view the existing sheets and click the plus button or the create new sheet type in a sheet name or title we will name our second one as orders and click outside the box to apply the changes each sheet has several properties you can configure them using the properties panel on the right under the title text box is the option to set an expression this can make your sheet title Dynamic according to the type of expression you have inserted here for example I've inserted an expression that takes the minimum and maximum year of our orders date so the new title now has the text sales from 2011 to 2015 which can change according to the earliest and latest date in your data set we will learn more about how to create this type of expression as we go through the course if there is no expression applied the sheet name will still take the fixed title the next option is where we can set the sheet thumbnail the Sheet's thumbnail is visible from the sheet Navigator and also on the app overview personalizing the thumbnail Pro sheet makes it easier to distinguish to change it click on the image icon which will take you to the media library same with that of the app thumbnail you can choose from the available in-app and default or upload your own image click on the plus button of your selected image to apply it you can also change the sheet thumbnail from the sheet Navigator menu from the opened sheet Navigator click on the information icon which will show you a new panel for the sheet information then click on the edit button clicking on the thumbnail icon will lead you to the media library to change it next is the option for grid sizing the grid is the yellow lines on the sheet that guides you on where you can place the objects and how big Each corner can be stretched to you can adjust it to fit more visualizations and control how they are positioned there are three options to choose from wide which is the default medium and narrow the narrow size is denser as the grids get smaller the next option is for customizing the sheet size by default this is set to responsive to adjust the sheet to the dimension of the user's screen but you can set a custom size to it just select custom then type a specific width and height in pixels you can set anywhere between 300 to 4000 pixels doing so will ensure that your sheet or dashboard is presented in exactly the same way you have created it for now let's put it back to the responsive size the small screen layout is only available for responsive size sheets this will control how your sheet objects are laid out in a small screen such as the smartphone selecting the list view will show the objects as a long list while the grid view will show a small preview of the objects the last configuration under sheet properties is a toggle to extend the sheet turning this on will extend the sheet area with 50 of its original size vertically this is useful if you need to add more objects then what can fit in the screen but keep in mind if you have visualizations that scroll this can interfere with scrolling of an extended sheet you should leave some empty space that allows for scrolling when you design the sheet creating a new app is easily done with the push of a button from The Hub to upload an existing app file created outside of the environment in the cloud version of qlik sense click on the add new button from The Hub and select upload apps it will show you a new window where you can drag and drop the app file or use the browse button to navigate into the path that contains your app apps read by qlik sense have a qvf or qvw file extension for qlik sense Enterprise users cannot upload an external app without having admin access the admins are the only ones able to import apps through the click Management console users are still able to publish to a stream duplicate and delete their apps by right-clicking on a specific app and choosing one of the actions from the pop-up menu you can further customize the look of your app through the app information panel since we want to see how each sheet will look while customizing it is better to edit the app information on the sheet view where you can see your visualizations when we are creating a new app it's a good practice to First build a structure of empty sheets where each sheet represents an idea or a goal structuring the sheets beforehand will give you a good overview of your app to edit the sheet name from the edit sheet view click on show properties then navigate to the sheet properties and type your sheet name under title the next option is where we can set the sheet thumbnail the sheets thumbnail is visible from the sheet Navigator and also on the app overview personalizing the thumbnail Pro sheet makes it easier to distinguish to change it click on the image icon which will take you to the media library click on the plus button of your selected image to apply it next is the option for grid sizing the grid is the yellow lines on the sheet that guides you on where you can place the objects and how big Each corner can be stretched to you can adjust it to fit more visualizations and control how they are positioned there are three options to choose from wide which is the default medium and narrow the next option is for customizing the sheet size by default this is set to responsive to adjust the sheet to the dimension of the user's screen but you can set a custom size to it just select custom then type a specific width and height in pixels you can set anywhere between 300 to 4000 pixels selecting the list view will show the objects as a long list while the grid view will show a small preview of the objects the last configuration under sheet properties is a toggle to extend the sheet turning this on will extend the sheet area with 50 of its original size vertically but keep in mind if you have visualizations that scroll this can interfere with scrolling of an extended sheet now that we know how to create an app and have added a new sheet let's add our first visualization there are three ways to insert a chart in qlik sense through the inside advisor using chart suggestion Assistance or without any assistance at all the first method is through the Insight advisor the advisor can be used to analyze your data and generate visualizations based on your searches or selections Insight advisor relies on the click cognitive engine and a logical model of your data to create visualizations The Logical model contains the relationships and uses of fields in your data model by default Insight advisor builds a logical model by learning from precedence from the app and published apps with similar data to navigate to the Insight advisor click the drop down of the analyze Tab and select insights the Insight advisor greets us with a green button in the center that states generate insights clicking this button will make Insight advisor analyze the entire data model using clicks cognitive engine to generate possible charts and visualizations from the fields and master items you currently have clicking one of the chart results will show additional functions in the upper right the first button lets you edit the specific insight editing insights will show your inside properties on the right panel which contains the chart type dimensions and measures used in the visualization as well as other details the chart type or analysis type will depend on the inputs and the characteristics of your data The Click cognitive engine will then determine the best analysis type for it for example in this kpi chart which contains the total sum of shipping costs it was classified as a fact wherein it is used to focus on one or two measures using a specific aggregated total other types of analysis you might encounter using Insight advisor our ranks Trends comparisons correlations and other types indicated in this table next you can also rearrange and change the dimensions and measures used for the chart to reorder them click and drag the icon next to the field name and place them in your preferred order to change the field that was used click on the field name to show a drop down where you can select a new one you can also do the same for the measures aggregation method finally we have the details this contains the information as to why the chart was generated and the Precedence Insight advice has learned from it clicking one of the details shows two buttons where you can choose to reject the proposed action or hide the chart in future analysis Insight advisor can also learn from your preferences for that chart if you add it to a sheet or click learn after editing the chart precedence set by a user only apply to that user's instance of insight advisor the next button is for exporting you can choose from exporting as an image to a PDF or export the data only the last button is for enlarging the Insight chart this is useful if the data has been compressed on the preview and you need more space to see the whole chart if you have decided to include one of the proposed Insight charts just click on the add to sheet button and specify the sheet where it will be inserted if you want a more specific Insight from the advisor you can select one of the fields or Master items from the assets panel you can also use the search bar on top of the panel to find fields and items easier selecting one or more Fields will generate inside charts using the specified field as a dimension or a measure let's select category name and sales and look at the generated results since we have pointed the Insight advisor to a specific field the proposed visualizations are now minimized to the charts and it is used as the focus data let's add the sum Open Bracket sales close brackets by category name to our sale sheet by clicking on the add sheet button and selecting sales the last way of finding Insight charts is by using the search field in the center of the window you can use natural language to type in a question for example let's type in what are my sales per order date and click on submit question Insight advisor answers the question through charts that you can then add to your sheet let's select the line graph sum Open Bracket sales close brackets over order date and click on add to sheet to add it to our sales sheet now we have three charts created using Insight advisor do take note that for published apps only Master items are available for use in Insight advisor Insight advisor chat can use fields from published apps when creating Insight charts if you go to Insight advisor using from explore this further your search may not be available the second way of adding charts to your sheet is by enabling chart suggestions chart suggestions enable you to select data fields and let qlik sense choose the dimensions measures and visualization types as you add or remove Fields the suggested visualization adjusts itself based on your changes you can customize a suggested visualization with a focus set of properties let's go back to the sheet view and edit the order sheet from the assets panel select the field quantity and drag it into the sheet holding the field over the sheet lets you select the location on where it will be placed from the four quadrants separated by a grid let's drop the quantity into the upper left quadrant upon doing so the field automatically changed into a kpi chart wherein the quantity is aggregated by the total sum from the properties panel we can see the chart suggestion was enabled this means that qlik sense will automatically choose a type of chart for you depending on the data type and the fields you have inserted let's look at what will happen if we add another field to this chart from the assets field panel select and hold the product name and drop it to the kpi chart as we can observe the kpi chart was changed into a vertical bar chart which now contains the quantity per product ordered let's expand the chart to show more of the data on the lower left corner drag the object outline until it covers half of our created sheet the grid will be the guide on where we can place our charts and objects and show the limits for the size of each item although we have created this chart using the chart suggestions function this does not mean that we can no longer change it if you want to replace either the dimensions or measures used for the chart just click on its field name which will show more options that we can change to change the field click on the FX or expression button this will show a new window where we can edit the expression the default expression is set to equals product name since it is our current dimension on the chart double click on product name or highlight it then select a different field from the panel on the right for this example let's set this to category name then click the insert button this will replace the equals product name expression into equals category name you can confirm that your expression is working by looking at message on the lower part of the window it should contain the message OK if you have no errors in your expression finally click apply to show the changes on the visualization now we have the quantity of order items per category in the bar chart you can also choose from other types of charts that are compatible with the dimension and measure that we have indicated under the chart suggestion toggle is the chart type indication which shows that the object we have selected is a bar chart that was automatically applied to view the other chart options click on the Arrow beside the chart type this will show you the preview of other recommended chart types selecting a different one from the list will change the chart type let's change this to a pie chart the Third Way of adding a chart to the sheet is by creating one with no assistance to do so select a chart type from the assets panel under charts select and drag a bar chart into the lower left part of the sheet and release upon doing so it will prompt us to add a dimension and measure click on ADD Dimension this will show a small window where you can select from the available fields on the data set select product name then click on the add measure this will show another window where we can indicate the measure to use for the visualization select quantity doing so will show as a list of aggregations that can be applied for our measure choose sum to apply it to the Chart now that we have both Dimension and quantity in our chart we can view the plotted bar chart do take note that additional properties are enabled when you create charts without assistance on the chart properties panel we have additional tabs that we can optionally configure to further improve our visualization there is a new tab called data which lets us add additional fields to the Chart without changing the chart type we can also add alternative Dimensions from this tab the other tab available is called appearance we're we can change the look of the chart such as the color access label and Tool tip comparing this list to the Chart properties of the pie graph we have created using chart suggestions earlier both the data and appearance tab are not available these two are some of the limitations when using chart suggestions other limitations are as follows you cannot enable chart suggestion for a master visualization you cannot enable chart suggestion for filter panes histograms or Maps charts and charts suggestions enabled are not supported in Click and printing reports to include charts created using chart suggestions in Click and printing reports disable chart suggestions you can only change the aggregation of a measure when its label is the default label you cannot drag a master Dimension to measures you cannot drag a master measure to Dimensions you can only drag a field for measures to Dimensions if its expression is simple we will further discuss how expressions are created as we go through the course there are certain conventions and limitations you need to be aware of when working with qlik sense here are some guidelines when creating a visualization on this platform as well as best practices when designing visualizations the maximum number of visualizations there can be on a sheet is limited to the number of cells on a sheet which is 288 or 24 by 12 grids the maximum practical number will be less than this because of the limited use for visualizations that are made up of only one cell there is a maximum length limit on naming names like titles and dimensions can be up to 255 characters descriptions has the maximum length of 512 characters expressions are 64 000 characters Max each tag can be up to 31 characters and tags are limited to a maximum of 30 per Master item lastly text and image charts are 12 000 characters Max when naming items it is best to avoid the following characters to avoid potential errors colon equal sign open and close bracket open and close curly brackets and the dollar sign if you use long names for your dimensions and measures they will be displayed truncated three dots will be shown as suffix to denote that part of the name is hidden for Expressions the maximum number of characters that can be written is sixty four thousand creating more will truncate it you do not have to pack all the data into a chart having several measures will confuse the users try to spread it out to smaller visualizations which also allow users to efficiently compare and contrast visualizations that are side by side you can also use alternative dimensions and measures to allow the reader to quickly switch between measures without overcrowding a visualization always remember that less is more if your users will be working with limited screens and different devices try to test your apps on other screens you can also use the responsive design mode in Firefox to check how your apps look on a different screen resolution check if there are truncated titles and names missing Legends or collapsed menus a mini chart with scroll bar will also be generated under a visualization if it exceeds the width of the screen ensure color accessibility some people may not be able to distinguish certain colors properly and interpret your visualizations differently try to include symbols which create performance indicators such as arrows for indicating an increase or a decrease lines bars and Pie slices can be difficult to distinguish when the colors are distorted keep proper object placement in mind most languages are read from left to right so people tend to look at the left side first making the right side less noticeable you can put the most important charts and functions on the right to emphasize them this is also applied vice versa for those who read from the right to left you can also Place icons and filters on the top of the sheet to give more space to the charts observe information hierarchy larger designs and fonts are given emphasis and the first page or sheet is usually seen as the most important apply this to the data you want to highlight and give more emphasis to add context to kpis since kpis usually only show numbers you can include captions in smaller text next to it or below it to indicate what it means and how the number was generated you can also provide a simple small bar chart to show the current Trend do not overdo colors wrong colors can also cause confusion rather than Clarity Reds are usually used for showing an increase or bad results yellows are mostly in between or average and greens are usually used for positive indication do take note that some colors might mean different things in other parts of the world so it is a good thing to know your audience first avoid having pie charts side by side to compare you also do not have to use a pie chart if you have more than four categories in your dimension try not to squeeze too much information into them take note of visual clutter too much information defeats the purpose of clarity use a maximum of 9 kpis and remove all visual clutter style over substance a beautiful visualization is not necessarily the most effective use design best practices at all times a suitable chart type can highlight your data more than a visually pleasing one spot and correct issues with your data before you present it do not let your visualization take the blame for bad information let's continue adjusting the appearance of our visualization we will be using the pie chart on our order sheet as an example the next subsection of the appearance settings is for the colors and legends the first option lets us toggle between a custom or automatic selection of colors for the graph as we have observed upon creating a chart qlik sense automatically selects colors for the visualization as they are added into the sheet you can always change this to meet your preferences to do so set the color toggle to custom this lets you manually adjust to either color by single color color by multiple color color by Dimension color by measure or color by expression When selecting two color by single color only one color will be used throughout all of the objects in the chart as we can see on the sheet all colors were set to Blue except for the 13 which contains null categories using single colors are best used for charts that only have a single Dimension and measure such as bar charts or line charts changing the single block of color is done by clicking the drop down for color to display the Color Picker then choosing one from the palette entering a hex color or using the color wheel if you want to set a consistent color for the master items you can set a color assigned to them when creating a new one in cases where visualization uses both a master Dimension and a master measure with assigned colors the color assigned to the mass Dimension is used by default you can also assign colors to individual Master Dimension values to ensure the colors of different values are consistent across visualizations When selecting color by Dimension all values in the chart will be colored by the corresponding value of the dimension field that was used it is best to color by Dimension if you want to distinguish certain Associated information in the charts such as coloring multiple charts by the dimension of category names to clearly see the values related to each product category in each chart when not configured qlik sense will automatically use the primary dimension for choosing the color but you can still change the dimension used for the color scheme by selecting the drop down on the dimension and choosing one from the list of the available fields you can also change which field or expression to use when naming labels on the chart below it you can enable the persistent color option when selected colors persist between selection States if cleared colors will be changed and reassigned for different dimension values as selections are made in the visualization next to it is the selection for the code scheme you can choose from either 12 colors or 100 colors the 12 colors are reused when the dimension has more than 12 unique values and this color scheme can be recognized and distinguished by people with color vision deficiency in contrast the 100 colors will also be reused if the selected Dimension has more than 100 values but this color scheme is not suitable for people with color vision deficiency When selecting color by measure the values will be colored by a gradient color scheme which is based on the intensity of the value of the chosen measure coloring by measure is useful when you want to clearly see objects colored by their corresponding measure value same as the color by Dimension the visualization color is based on the primary measure by default which you can change by selecting a different measure on the drop down you can also change the labels to a different measure or expression by using the expression editor below it you can choose the color scheme from four palettes sequential gradient has smooth transitions between color groups made using different shades of colors sequential classes show transitions between different colors using a distinct different color diverging gradient is for values that are sorted from low to high such as heat Maps diverging classes can be seen as two sequential classes combined with the mid-range shared the two extremes high and low are emphasized with dark colors with contrasting Hues and the mid-range critical values are emphasized with light colors you can always reverse the selected color scheme to switch the color of low and high values by enabling the reverse colors you can also choose the range or the minimum and maximum value used for the color gradient if the range is toggled to Auto qlik sense will base the range on the detected minimum and maximum value found in the data set setting the range to custom lets you decide the exact value or expression When selecting color by expression you can set the color using a specific expression when used you can choose both the color and the data value for it which in turn gives you more control on how colors are used in the visualization for example you might highlight values of particular interest or differentiate between values within different value ranges coloring by expression can also be used to color a visualization by values not included within a visualization such as coloring products and the sum of their monthly sale by the country of origin for the product here are the types of visualization that supports using color by expression bar combo chart line chart map pie chart pivot scatter plot table and tree map when adding an expression there is a checkbox which lets you set if the specific expression is a color code or not if it is enabled your expression must have a color code on the script that is in a supported format to indicate which colors to put in the chart values for example in this pie chart I wanted to color the categories that have more than 9000 in order quantity as Cadet blue and those who are not will be in goldenrod yellow to do so I will input this expression if open parentheses sum open parentheses quantity close parentheses greater than 9000 comma Cadet blue comma Goldenrod close parentheses this expression uses the IF function to check the value the condition is set to the sum of the quantity that is greater than nine thousand if the value is found to be true for the condition it will set the color to Cadet blue but if not it will set it to Goldenrod checking the message below the expression editor signifies the expression is correct hit apply to show the changes to our pie chart if the script or expression has an error the chart will not show the correct colors and will be colored in Gray instead let's see how it looks if we do not enable the color codes uncheck the expression is a color code then on the expression editor edited into some open parentheses quantity close parentheses greater than 9000 then hit apply unlike the earlier expression qlik sense automatically selected the color to put on the chart which still depends on the script we have inserted when creating the expression you can use several functions available in qlik sense such as the conditional if statement we have used you can also use multiple nested if statements to set colors for multiple values and use various aggregation methods in your conditions for color codes you can't just indicate a random color name into your expression here are the following formats supported by qlik sense RGB the RGB colors let you set an integer value between 0 to 255 for each of the colors red green and blue many of it also has corresponding plain text such as blue red yellow you can also use this hexadecimal numbers a r g b the argb color model has the same support as the RGB color model but extends it with an additional Alpha value to set the opacity of a color hsl the color is defined by a hue value a saturation value and a Luminosity value you have values between 0 and 1 representing Hue saturation and lightness color keywords click sense can also use color keywords recommended by w3c and some CSS color keywords qlik sense color functions the following color functions can be used within expressions after the color options is the toggle for showing or hiding the legend do take note that Legends are only available for values with multiple colors indicated enabling the legend lets you also set its placement in the chart you can set it to Auto right bottom left or top you also have the option to hide the Legend's Title by checking or unchecking the show Legend title the next subsection of appearance setting is the tool tip you can use the switch to toggle between using the basic or Custom Tool tip basic only shows the current Dimension and measure used in the chart to view the tooltip click on done and hover over a value on the chart if you want to add additional fields or provide more context you can also set this via custom let's go back to editing the sheet set the tooltip toggle to custom this provides additional options such as hiding the basic rows that we saw earlier and adding additional measures to the tool tip finally we have the access option for charts that has X and Y axis select the bar chart below the pie graph and adjust the x axis you can set it to plot a continuous axis for line charts and set it to either Auto or custom below it you can set to either show or hide the label and titles on the access you can choose to show the labels and titles titles only labels only or none sometimes it may feel unnecessary to have labels and or title because the visualization is pretty self-explanatory and then it would be good to be able to hide them furthermore when you create a visualization that is very small for example three by three squares the labels are automatically hidden the next option lets you set the orientation of the label to Auto horizontal or Tilted keep in mind the length of your label when setting this option below it is the option to set the position of the labels which you can set to either bottom or top last we have the drop down for setting the number of axis value this will be the number of points or bars that will be shown in the graph you can set this to Auto to let qlik sense decide set to maximum to accommodate all values or custom to enter a specific value for the y-axis option we have the same set showing or hiding the label and title and setting the label position the scale option lets you resize the y-axis to either wide medium or narrow finally the range toggles the range of the axis by default the range is adjusted according to the highest positive or the lowest negative measure value but if for example a single measure value is much larger than all the other values you may want to set a range that is suitable for the lower values to set a specific range set the range toggle to custom and specify either a minimum or maximum value clicking on one chart will show the chart properties panel the first tab called Data lets us select the dimension and measure used for the chart for the kpi it only needs at least one measure in comparison line charts need to have at least one dimension and one measure like the order date and sales let's try adding another measure to this chart and observe how it changes the chart this will show a mini window where we can choose which measure to add from our available Fields select the field cause and set the aggregation to sum upon doing so a new red line has been added to the Chart which indicates the cause per date aside from adding a new measure we can also add the field used in our Dimension click on the order date to show the additional options under the field click on the expression button to edit the expression the appearance tab of the chart properties offer several options to set and modify the appearance of a visualization its first subsection is the general section here you can show or hide the title along with other details the next option for setting alternative states are used if you are going to create a comparative analysis alternative states allow you to make different selections on the same Dimension and compare the Selections in a single visualization or in two or more visualizations side by side the next tab is for editing the presentation of the charts checking the show button will put the label above the kpi number next is the layout behavior of the text you can select from responsive fluid or fixed clicking on the kpi will let the users go to the specific sheet without having to use the sheet window or the navigation button going back to the appearance tab there is an option to set the scroll bar type for charts that extends longer than the width of the screen next you have the option to choose the action applied if there are missing values in your line graph below the drop down is a checkbox for showing the data points finally we have the grid line spacing option you can toggle from either Auto or custom the next subsection of the appearance settings is for the colors and legends the first option lets us toggle between a custom or automatic selection of colors for the graph as we have observed upon creating a chart qlik sense automatically selects colors for the visualization as they are added into the sheet changing the single block of color is done by clicking the drop down for color to display the Color Picker then choosing one from the palette When selecting color by Dimension all values in the chart will be colored by the corresponding value of the dimension field that was used When selecting color by measure the values will be colored by a gradient color scheme which is based on the intensity of the value of the chosen measure you can also change the labels to a different measure or expression by using the expression editor sequential gradient has smooth transitions between color groups sequential classes show transitions between different colors using a distinct different color diverging gradient is for values that are sorted from low to high such as heat Maps diverging classes can be seen as two sequential classes command lined with the mid-range shared once we are done configuring our dashboard it's time to publish it for others to see publishing an app is the way we share contents with other users published apps are located in a stream which is a space to organize a collection of apps to make it available to a specific group of users from the qlik sense Hub we can see the contents organized in streams however not all users of the streams have same levels of access some users might only be able to read the content in the Stream While others might have the right to publish their content to the stream generally all users have their own spaces under the personal tab work for the unpublished apps and published the apps that have been designated to a stream these links enable you to keep track of your published work and perform tasks such as moving or duplicating a published app more easily for example we have the department store app that is currently unpublished in order to publish it right click on the app and select publish this will show a published app window where you can set which streams the app will be published to and change the name of the app that will be displayed to all users in the streams drop down let's select sales and marketing do take note that the available streams along with setting the users who can access them is handled by your system administrator from the stream list we can also see the stream called everyone by default this stream shows its contents to all current users of qlik sense if your app was created for everyone to use or view you can use this stream to share it in we will not change the app name for now but do take note that it is possible to publish many apps with identical names in a stream click sense will indicate when there are published apps with the same name in a stream click on the publish button to finish the process once done your app will no longer be visible on the work stream to view it you can go to the publish stream or to the sales and marketing stream the app is also marked with the stream icon on the upper right to indicate that it has been published to a stream do take note that there are certain functions removed once you have already published your app let's open our published app and observe the changes from the app overview notice that we can still edit the name description and thumbnail of the app this is because we were the owner of the app but for viewers this function will not be available from the prepare tab on the top toolbar we can only navigate into the data model viewer its drop down was also removed thus we can no longer visit the data load editor and the data manager View if you are not the owner of the app the prepare tab will no longer be visible on the listed sheets below there are now two subsections called public sheets and my sheets by default if you publish an app all of the sheets within it will be set to public you can also unpublish sheets from this view to do so right click on one of the sheets for example let's select orders then click on unapproved this will then move the sheet into a new section called published by me then right click on the sheet and select unpublished this will remove the sheet from the public seat section and will be moved into the my sheets section the my sheets section contains sheets that were not approved or unpublished other users can also create or duplicate your sheets and customize their own which you as the owner can then approve or unapprove to move it to the public sheets this enables the collaborative design content in a published app where you and your colleagues can add and improve the app you can also do the same for stories but keep in mind that you cannot edit the public sheets and public stories of the app but you can make them private sheets and stories if you want to edit them here's a quick rundown of the sheet view sections that you can see in the app preview of a published app publish sheets or public stories are sheets available to all users slash viewers of the app Community has sheets bookmarks and stories that someone else has created and published to the app that you have access to published by me are sheets bookmarks and stories that you have created and then published so that all the users of the app can access them my sheets slash my stories are sheets and stories that you have created but not published no one else can see these let's click on the public sales sheet we can still create selections and save it to a personal bookmark for example selecting the sportswear category on the bar chart also filters the rest of the visualization in this sheet you can save this selection as a bookmark by going to the bookmark icon and selecting create new type in a name and an optional description and click on the check button to save it these bookmarks can also be published like the sheets and apps to show it to the rest of the app users on this published sheet you can still use the search bar to analyze or look for certain information to do so click on the smart search button in the upper right then type in a keyword to the text box while typing qlik sense will display suggested fields or items that contain the specific keyword clicking on one will filter the visualization according to the selected item or data looking at the upper right toolbar we no longer have the edit button to make changes to the visualization if you wanted to make further tweaks once the sheet has been published you can either unpublish the sheet or duplicate it to create a new one that will replace the published sheet let's unpublish the sales sheet to add a table to it click on the sales drop down then on the sales menu right click on the sale sheet and click on approve then right click on it again and select unpublished this will move the sale sheet into the my sheet section which hides the sheet from the viewers doing so also enables the edit button click on the edit button even on the private view of the sheet we will still not be able to edit the data itself from the data load editor or data manager since it's not available on the navigation in addition the variable window is hidden from this View also you cannot add new master items from the assets panel always keep these things in mind when deciding to publish your app let's fill in the blank space in your sheet by creating a table from the charts tab select table and drag it to the sheet place it below the kpi chart and expand its size until it's in line with the end of the bar chart then add a dimension called Product name doing so creates the table list of all unique products available in the data set let's add another column to it by clicking on the add column button under the data section of chart properties select measure then find and select the sales field from the list once done set the aggregation to sum now that we have our table click on done to finish editing the sheet let's make this edited sheet available to the viewers by publishing it again click on the sheet window then right click on the sale sheet of the my sheet section then click on publish this will display a prompt to remind you the sheet will be shown publicly to the users click on publish to confirm then right click again and select approve to move it to the public sheet section publish apps can be moved between streams but they cannot be deleted if you wish to make changes on how the data is formatted or apply more changes to the base app without removing the app itself from the stream you can republish it to do so duplicate the app by right-clicking on it and select duplicate this will create a duplicate version of the app into your personal work stream that you can edit once you are satisfied with the changes you can republish it to remove the existing app in the public stream right click on the app and select publish set the stream where it was placed before then check that the app name has no numbers on it make sure to enable to replace the existing app function then click on publish this will update the app on the stream without losing the community in private content hi publishing an app is the way we share contents with other users from the qlik sense Hub we can see the contents organized in streams however not all users of the streams have same levels of access some users might only be able to read the content in the Stream While others might have the right to publish their content to the stream generally all users have their own spaces under the personal tab work for the unpublished apps and published for the apps that have been designated to a stream these links enable you to keep track of your published work and perform tasks such as moving or duplicating a published app more easily for example we have the department store app that is currently unpublished in order to publish it right click on the app and select publish this will show a published app window where you can set which streams the app will be published to and change the name of the app that will be displayed to all users once done your app will no longer be visible on the work stream to view it you can go to the publish stream or to the sales and marketing stream the app is also marked with a stream icon on the upper right to indicate that it has been published to a stream let's open our published app and observe the changes from the app overview notice that we can still edit the name description and thumbnail of the app this is because we were the owner of the app but for viewers this function will not be available from the prepare tab on the top toolbar we can only navigate into the data model viewer its drop down was also removed thus we can no longer visit the data load editor and the data manager View if you are not the owner of the app the prepare tab will no longer be visible on the listed sheets below there are now two subsections called public sheets and my sheets by default if you publish an app all of the sheets within it will be set to public you can also unpublish sheets from this view to do so right click on one of the sheets for example let's select orders then click on unapproved this will then move the sheet into a new section called published by me then right click on the sheet and select unpublished this will remove the sheet from the public sheet section and will be moved into the my sheets section the my sheets section contains sheets that were not approved or unpublished other users can also create or duplicate your sheets and customize their own which you as the owner can then approve or unapprove to move it to the public sheets this enables the collaborative design content in a published app where you and your colleagues can add and improve the app let's fill in the blank space in your sheet by creating a table from the charts tab select table and drag it to the sheet place it below the kpi chart and expand its size until it's in line with the end of the bar chart then add a dimension called Product name doing so creates the table list of all unique products available in the data set let's add another column to it by clicking on the add column button select measure then find and select the sales field from the list once done set the aggregation to sum now that we have our table click on done to finish editing the sheet let's make this edited sheet available to the viewers by publishing it again click on the sheet window then right click on the sale sheet of the my sheet section then click on publish this will display a prompt to remind you the sheet will be shown publicly to the users click on publish to confirm then right click again and select approve to move it to the public sheet section exercise one follow the steps in loading a static table and a manual entry table to an app you will be using the customer list dot xlsx file using the data manager insert the customer file to the app create a manual entry table using this information create an automatic association between the two tables and load the data from the qlik sense Hub create a new app called exercise one once created open the app from the app overview click on the data manager tab then click the plus button to open the add data window from your file explorer select the data set file and drop it into the drop a file area make sure you've selected the data sheet with all the columns selected once done click on add data once the data is loaded let's rename the static table that we have inserted click on the edit button below then rename the table to customer info next close it click on the edit button once again open the add data window and select manual entry this manual entry table will be named card info then click on the field underscore 1 to change it to the First Column name called card space type next for the next column name set it as card space name for the data put in am as the first card type then for the corresponding name type in American space Express next for the second row type in m and the corresponding name is Mastercard for the last row type in v for the card type and visa for the card name after completing the manual entry table click on add data now we need to create the association between the two tables grab the customer info bubble and drag it into the card info the automatic association should show the card type as the link between the two tables once done click on load data to check the output navigate to the data model viewer under the prepare tab this will show the link or association between the two tables that we have inserted into the app we've made the files the instructor uses in this tutorial available for free just click the link below in the video details to get these in order to create an effective visualization you must be able to understand how your data is structured and how it can affect the charts that you create the types of data you have in your tables and Fields also impacts whether they can be used as Dimensions or measures as well as what sorting options are most effective as we have discussed in the first chapter of this course different charts require different numbers of dimensions and measures we have also demonstrated how you can use the dimension and measures to change how your visualization is presented but what exactly are these data assets data assets are available in the assets panel which is located on the leftmost part of the window in the edit sheet view in this lesson we will tackle the four types of data Assets in qlik sense feels Dimensions measures and master items Fields hold the data that is used in qlik sense feels can be thought of as the data loaded from the load script when loaded from the load script Fields can be represented as a table visualization for example in the department store data that we loaded we have four tables available the products category order details and Order headers in the data model viewer each table is represented by a rectangle where the table's name is written in bold on top each of the rows within each table is called a field in the case of the ID Fields such as category ID can appear in more than one table as we can see both products and category table contain the field category ID Fields contain one or more values called field values and at the basic level correspond to columns in a database table but they can also exist in more than one table in the viewer let's click on one of the fields to see a preview of its content select product name from the preview panel we can see the list of the field values inside the field of product name Lenin jean shorts Chagall socks and Tuxedo are all field values field values consist of numeric or alphanumeric data the product name that we have highlighted has text values while the fields cite it such as category ID and supplier ID have numerical field values these values or data can be categorized according to their properties certain data may work as dimensions and sum as measures similarly as Dimensions or measures certain kinds of data may work better as a dimension in some visualizations better than others or as a measure with certain aggregation functions at the highest level data can be categorized as either quantitative or qualitative quantitative data deals with numbers and things you can measure objectively it answers the question what or how many we describe or measure them using numbers so another term used for quantitative data are numerical data it is data that can either be counted or compared on a numerical scale quantitative data can be further grouped into either Ratio or interval data ratios or quantitative data that you can perform arithmetic operations on this type of numerical data has an absolute zero meaning having a count of zero is equal to none for example you can sum sales values for the month to get totals zero sales mean that there were no sales for that month other examples are age price and weight intervals are quantitative data that you can perform arithmetic operations on its numerical order and difference between values are given importance for example you cannot calculate the sum of temperatures during the week but you can calculate the average temperature per day and determine if the temperature has been high or low another example are credit scores those with lower score have lower buying power but those with higher scores can purchase additional products and services on the other hand qualitative data cannot be measured numerically but can be described through language qualitative data is also known as categorical data data that can be arranged categorically based on the attributes and properties of a thing or a phenomenon it's pretty easy to understand the difference between qualitative and quantitative data qualitative data does not include numbers in its definition of traits whereas quantitative data is All About Numbers qualitative can be further classified into nominal or ordinal data nominal are Fields with nominal data that have distinct categorical values but without a set order they are usually used for labeling for example product names or customer names are nominal data as they have distinct values but do not have a required order other examples are countries titles and blood types ordinal are Fields with ordinal data which have qualitative values that have a ranked or positioned value ordinal data should be sorted by its order as opposed to alphabetically for example low medium high are ordinal values small medium and large are also ordinal values ordinal values are also used to answer measures of satisfaction happiness and so on another example is how we usually rate answers with highly likely up to very unlikely to questions such as How likely are you to recommend our service it is essential to be able to categorize the data in your tables as this can guide you on how to aggregate the values and how you can further improve your visualization here's a table of Click recommended aggregations for each data type these recommendations should not be considered absolute and you should always take into consideration the output that is suitable for your data set for nominal data it is best to use count and avoid average medium and sum with ordinal data you can use either count or median but stay away from average and sum for numerical data that our ratio you can use count average and median lastly for Interval data you can use count average medium and sum when creating visualizations you use fields to create your dimensions and measures however it's easy to get the two confused when working with them you are able to switch some fields from measure to Dimension or vice versa but there is a distinct difference between the two that will affect your visualization let's look at how dimensions and measures differ Dimensions determine how the data and visualization is grouped they can also be used to define the level of detail to perform an aggregation on you typically find a dimension as the slices in a pie chart or on the x-axis of a bar chart with vertical bars for example total sales per country or number of products per supplier Dimensions display the distinct value from the field selected as a dimension qualitative fields are usually the ones used as dimensions dimensions are descriptive and categorical but sometimes numeric Fields can be used as dimensions in our department store dashboard sales sheet we have used the category name as the dimension the category name is a qualitative nominal field that sets a value of the products by category since we have used it as a dimension we are able to properly categorize and separate each unique category in a bar and differentiate the sales for each we also use the same field in our pie chart but with a different measure in this pie chart each Dimension is separated by slice and color to distinguish the different category of the products measures are calculations used in visualizations typically represented on the y-axis of a bar chart or a column in a table measures are created from an expression composed of aggregation functions such as sum or Max combined with one or several fields think of it as something that can be collected counted or combined in some way to return a single value quantitative fields are the ones used as measures in the bar chart that we have created we've used sales as the measure field aggregated using sum this plot of the sales for each category with the y-axis serving as the guide and the indicator of the amount the height of each bar shows how many sales each category was able to make we also use the same quantitative field to sort the order from most sales to least on the order sheets pie graph we have used quantity as the measure field and sum as the aggregation method categories with greater quantities of orders covers a bigger slice area since the labels of the graph was set to the percentage of the measure we can see how much each slice contributes as compared to the total number of orders from the store when you create and build your visualizations you can save these assets to reuse them in other visualizations and on other sheets you can save visualizations dimensions and measures as Master items in the assets panel Master items are dimensions measures or visualizations that can be reused in other visualizations and sheets in your app Master items also include special dimensions such as drill down dimensions and calendar measures any updates you make to the master item are applied everywhere the master item is used Master items also have design options available you can for example assign colors to a Master Dimensions distinct values so that the distinct values are consistent across visualizations in order to add a visualization as a master item you need to go to the edit sheet view let's use our department store app and then use the sales sheet click on the edit button to show the edit sheet view select the table object and right click this will show an actions menu select the add to master items a new window will pop up to fill in the details needed fill in the name of the master item let's name it as product table and set an optional label and description to the text box and expression window you also have the option to add a maximum of 30 different tags to the master item tags are used for organizing Master items you can also use these to act as keywords when using the smart search function let's add in a tag product and press enter to add it as a tag this will show a small pill label below which confirms that it has been successfully tagged with product clicking on the x mark on the pill label will remove the tag you can also tag Master items with synonyms for insight advisor to do so use the formal alt colon followed by the synonym term for example let's type in alt colon merchandise and press enter to add it finally click on the add button to show the object title on the master items visualization section clicking on the name will show a preview of the visualization its tags and additional actions to embed cut edit duplicate add to sheet and delete the item you can also add a chart to the master items by simply dragging the chart object and dropping it to the master items panel let's add the master item we created to the order sheet drag the product table from the master items and place it on the left side of the sheet then edit the object by clicking on the product table's name on the master item panel and click the edit button then select ok to confirm let's add a new measure column on the right by clicking on ADD column select measure and use the quantity field set its aggregation to sum click the done button to stop editing the master item then click the done button to stop editing the sheet the master item now has an additional column to show the total quantity sold for the item let's go back to the sale sheet by using the next sheet button the product table for the sale sheet was also updated as we added to the master item now let's try creating a master measure edit the sheet and show the assets panel navigate to the master items and select the section for measures click on the create new button this will show a new window where you can set the options for creating a new master measure on the expression type in equals sum open parentheses cos close parentheses this takes the aggregated sum of all cost of sales if you need additional support on creating the expression you can use the Expression editor then for the measure's name type in cost of sales same as the visualization you can also add an optional label and description both Master Dimension and master measure lets you set a unique color to it using either the default color palette entering a hex color or using the color wheel let's set the cost of sales as purple the segment colors tab also lets you assign a color gradient or segment scheme to a Master measure enabling you to color visualizations by measure using colors other than the default color schemes if you choose to color by single color the master measures color is used instead of the color scheme on the segment colors toggle the switch button to custom this enables the rest of the options for the tab then select a template scheme you can choose from diverging classes basic sequential gradient sequential classes or diverging gradient let's set this to diverging classes the next option lets you set the segment format the segment format determines how colors will be applied percentage adds value classes based on where values fit into the percentile of their value fixed value uses defined values to set the limits of each segment let's set this to percentage finally we can add a limit you can create additional segments by adding limits when a new segment is created a pointer marks the value of its limit let's leave this to the default 90 limit and hit the create button to add the new Master measure let's add the cost of sales Master measure to the line chart drag the cost of sales into the line chart and set it as an additional measure upon doing so the line 4 Cause is automatically set to the single purple color we have indicated this shows that you could use a Master measure in as many of your visualizations as you like while only having to update it in a single instance to update all instances of the measure in your visualizations one of the purposes of creating and maintaining Master items is for other users to explore their own ways and directions in the data on top of what you have provided in the app has pre-made sheets with visualizations the users will be able to create their own visualizations with your pre-made Master dimensions and master measures for example when your app gets published your master items will be available to others as ready to use visualizations dimensions and measures in this lesson we will tackle the four types of data Assets in qlik sense Fields Dimensions measures and master items Fields hold the data that is used in qlik sense Fields can be thought of as the data loaded from the load script in the data model viewer each table is represented by a rectangle where the table's name is written in bold on top each of the rows within each table is called a field feels contain one or more values called field values and at the basic level correspond to columns in a database table but they can also exist in more than one table here's a table of Click recommended aggregations for each data type these recommendations should not be considered absolute and you should always take into consideration the output that is suitable for your data set for nominal data it is best to use count and avoid average medium and sum with ordinal data you can use either count or median but stay away from average and sum for numerical data that our ratio you can use count average and median lastly for Interval data you can use count average medium and sum let's look at how dimensions and measures differ Dimensions determine how the data in a visualization is grouped they can also be used to define the level of detail to perform an aggregation on you typically find a dimension as the slices in a pie chart or on the x-axis of a bar chart with vertical bars for example total sales per country or number of products per supplier Dimensions display the distinct value from the fields selected as a dimension qualitative fields are usually the ones used as dimensions dimensions are descriptive and categorical but sometimes numeric Fields can be used as dimensions in our department store dashboard sales sheet we have used the category name as the dimension the category name is a qualitative nominal field that sets a value of the products by category since we have used it as a dimension we are able to properly categorize and separate each unique category in a bar and differentiate the sales for each we also use the same field in our pie chart but with a different measure in this pie chart each Dimension is separated by slice and color to distinguish the different category of the products measures are calculations used in visualizations typically represented on the y-axis of a bar chart or a column in a table measures are created from an expression composed of aggregation functions such as sum or Max combined with one or several fields think of it as something that can be collected counted or combined in some way to return a single value quantitative fields are the ones used as measures in the bar chart that we have created we've used sales as the measure field aggregated using sum this plot of the sales for each category with the y-axis serving as the guide and the indicator of the amount the height of each bar shows how many sales each category was able to make we also use the same quantitative field to sort the order for most sales to least on the order sheets pie graph we have used quantity as the measure field and sum as the aggregation method categories with greater quantities of orders covers a bigger slice area when you create and build your visualizations you can save these assets to reuse them in other visualizations and on other sheets you can save visualizations dimensions and measures as Master items in the assets panel any updates you make to the master item are applied everywhere the master item is used in order to add a visualization as a master item you need to go to the edit sheet view let's use our department store app and then use the sales sheet click on the edit button to show the edit sheet view select the table object and right click this will show an actions menu select the add to master items a new window will pop up to fill in the details needed fill in the name of the master item let's name it as product table and set an optional label and description to the text box and expression window you can also tag Master items with synonyms for insight advisor to do so use the formal alt colon followed by the synonym term for example let's type in alt colon merchandise and press enter to add it finally click on the add button to show the object title on the master items visualization section clicking on the name will show a preview of the visualization its tags and additional actions to embed cut edit duplicate add to sheet and delete the item you can also add a chart to the master items by simply dragging the chart object and dropping it to the master items panel let's add the master item we created to the order sheet drag the product table from the master items and place it on the left side of the sheet then edit the object by clicking on the product table's name on the master item panel and click the edit button then select ok to confirm let's add a new measure column on the right by clicking on ADD column select measure and use the quantity field set its aggregation to sum click the done button to stop editing the master item the master item now has an additional column to show the total quantity sold for the item let's go back to the sale sheet by using the next sheet button the product table for the sale sheet was also updated as we added to the master item now let's try creating a master measure edit the sheet and show the assets panel navigate to the master items and select the section for measures click on the create new button this will show a new window where you can set the options for creating a new master measure on the expression type in equals sum open parentheses cos close parentheses then for the measures name type in cost of sales same as the visualization you can also add an optional label and description let's add the cost of sales Master measure to the line chart drag the cost of sales into the line chart and set it as an additional measure upon doing so the line 4 Cause is automatically set to the single purple color we have indicated this shows that you could use a Master measure in as many of your visualizations as you like while only having to update it in a single instance to update all instances of the measure in your visualizations click is a leader in Gartner's 2021 analytics and bi intelligence platforms this year's ABI platform functionality included the critical capability areas of manageability cloud analytics data source connectivity data prep automated insights data visualization data storytelling natural language query natural language processing and your standard reporting this year there was a particular emphasis on augmented analytics my name is Andrew Pearson I'm the founder and managing director of intelligencia limited a software consulting company based in Hong Kong and Macau we've been a click partner for over eight years we've been working with several long-term clients and we've seen the click tool evolve quite radically during that time even separating into two different products click View and click sense which we will be discussing here in our course part one starts with an introduction to variables then we explain the full use of these variables in sessions one and two we then move on to the process of dynamic loading and split that across two sections script files and subroutines is in section 1.5 and using Advanced script functions rounds out the first section we are using qlik sense Enterprise August 2022 so welcome to the course I'm very excited to have you here and I'll see you in the first module variables are used in numerous Fields such as research programming and arithmetic they are used to hold a value that can change depending on conditions or on information passed the variable is then processed to make use of the value or utilized in conditional processes same is also true for variables in qlik sense a variable in qlik sense is a named entity containing a data value you can assign a specific fixed value to it or make it Dynamic using an expression when a variable is used in an expression it is substituted by its value or the variable's definition variables are defined using the variables dialog or in the script using the data load editor any change to the variable is applied everywhere in an app that the variable is used to create a variable using the variables dialog navigate to the edit sheet window and click the variables menu in the lower left portion here we are using the bank churners data set in the newly created qlik Sense app by default the app already has four variables listed in the dialog name index current name and matches these four variables are added using the load script as indicated by the script icon beside its name to create a new one click the create new button in the upper right this opens the new variable window where we can input the variable name description definition and tags type in v dataset as the variable name then type Bank churner as its static definition Bank churner is enclosed in the single quotes since it is a string you can also open the expression editor if you are going to store a calculation into the variable if the first character of a variable value is an equal sign click sense will try to evaluate the value as a formula and then display or return the result rather than the actual formula text do take note the variables are case sensitive so lettercase is important description and tags are optional but you can add them to give more information to new users as well as optimize the variable for searching click create to show the new variable in the list if you opt to disregard any changes done in this window you can press cancel to close the new variable window here are some guidelines from click on choosing a variable name it is important to keep these in mind during variable creation as you cannot change the name once created use a letter as the first character do not use a number or a symbol for the earlier example we've used the small letter v as the first character to easily distinguish variables from fields it is not recommended to name a variable identically to a field or a function in qlik sense do not use the following characters when naming a variable dollar sign Open brackets close brackets open square brackets and close square brackets the name must be unique you cannot name a variable using a name used for a reserved variable or a system variable the variables are not listed in the variables dialog but if you are not allowed to use a certain name even though you cannot find a duplicate in the variables dialog a reserved variable or a system variable already has this name a long name is not recommended if a variable's name is too long the name cannot be fully displayed in the variables overview next let's show how to add a variable using the data load editor window navigate to the prepare tab click the downward arrow and select data load editor under the main section we can see 19 lines of number interpretation variables which are automatically included upon creating an app number interpretation variables are system defined the variables are included at the top of the load script and apply number formatting settings at the time of the script execution they can be deleted edited or duplicated number interpretation variables are automatically generated according to the current Regional settings of the operating system when a new app is created if the qlik sense server you are accessing is set to Sweden the data load editor will use Swedish Regional settings for dates time and currency these Regional format settings are not related to the language displayed in the qlik sense user interface qlik sense will be displayed in the same language as the browser you are using to create a variable in the data load editor you need to insert a prefix of either set or let before the variable name set is used if you're going to assign static values set is also used when creating system variables value handling variables and number interpretation variables let is used for assigning expression or calculations as the value of the variable the let statement evaluates an expression to the right of the equal sign at script runtime and assigns the result of the expression to the variable to show how these two prefixes differ let's use both prefixes in the main section of the data load editor and show it on the sheet as text create a new line of script and type set space V to day set space equals space today Open brackets close brackets semicolon once done press enter to type in another line input Let space V to Day Let space equal space today Open brackets close brackets semicolon we are using the same function for both variables to compare their output notice that both set and let prefixes have the same syntax type in set or let then add equals and input the value or expression that you want for the variable hit the load data button in the upper right for qlik sense to process the new lines after loading the data navigate to the sheet Tab and edit the sheet insert text by navigating to chart text and drag it to the sheet we only need to fit the text to a small portion of the sheet since our variable values are not that long next add a title so we can distinguish the two variables select the text object and navigate to the right pane for the properties to change the title click appearance General and type in the title box input equals open single quotation V today set space and sign space V to day let close single quotations after adding the title add the text value navigate to data measure add measure open the expression editor window then type in equals V today's set space and sign space single quotation space single quotations space and sign space V today let you can also insert the variables using the variable drop down menu on the right once done make sure no errors were found in the expression and hit apply as we can see from the text value the output of V today set is the function name converted to a string but for V today let it return the current date based on the system clock which is the output of the today function so even if you use a function on a set prefix variable it will always return the static string as output as we've demonstrated earlier inserting a variable to an object is as easy as adding its name to the expression but aside from using the variable directly on an object's expression editor you can use variables in the data load editor for example let's add a new variable by using a Field's value it is important to place the let prefix after the load data script if you're going to use the fields value since the data has to be loaded first but first we need to unlock the selection click unlock in the upper right corner once done create a new line after the load script then type Let space V client count space equals space single quotations count Open brackets distinct space open square brackets client num close square brackets close brackets single quotation marks once done load the data and proceed to the sheet remove the measure expression in the text object and change it to equals dollar sign Open brackets V client count close brackets using the dollar sign is another way of reading in a variable which is called the dollar sign expansion the text Will Now display the new variables value which is 10 127. aside from the number interpretation variables mentioned earlier there are other types of variables used in qlik sense system variables some of which are system defined provide information about the system and the qlik sense app a few examples of system variables are QV path collation Locale and wind path value handling variables are used to tell qlik sense how to handle certain data values such as null values this is very important when we want to display null values as something different that isn't null and when we want to be able to select those null values a few examples are null display null value and null interpret and finally the variables we've created earlier are called user-defined variables these are the variables that are not automatically available in a qlik sense app but are created by the users to add flexibility to the existing functions in order to properly delete a variable we need to remove it from the script or the back end as well as the front end or the variable dialog for example let's remove the script first and observe the changes afterwards in the main section of the data load editor delete the two lines of script that creates V today's set and V to day let once done load the data after running the script go to the edit sheet view and open the variables dialog notice that the two variables are still listed here even if we already removed the two variables from the script to remove it totally select the two variables and click the delete button a new window will pop up to confirm the decision click delete to approve it do remember that any variables deleted in the front end will be recreated if there are variables defined in the script and the script is executed so it is crucial to check both ends when removing variables as discussed in the previous lesson variables can be utilized to store a line of information may it be a fixed value a set of concatenated characters or even an expression your app can benefit from the use of variables by increasing its efficiency as well as providing flexibility of functions let's look at several use cases of variables in a qlik sense app by demonstrating how each process is built for our first example we are going to use variables to change the dimension used for this bar chart the user will have the option of using either an attrition flag gender income category or marital status as the dimension to display in the bar chart in order to create a drop down of a specific list of Dimensions we're going to make use of the variable input control the variable input control is an extension included in the dashboard bundle of qlik sense which lets users set the value of a variable the variable input control can be displayed as a button a drop down an input box or a slider we are still going to use the bank churner's data set used in the past lesson but imported into a new app so we can start with a clean slate as the first step let's prepare the variables that we need to use navigate to the data load editor once there create a new section of the script by clicking the plus button in the left pane let's just leave the section name to the default next create the first line of the script for the new section we're going to create a variable that will serve as the storage for the list of Dimensions that will be used for the bar chart all right since we're going to treat the variable as a field for the bar chart make sure to use the same letter case and spelling with the field as seen on the auto generated section type in Let space V list Dimension space equals space open single quotations gender space pipe symbol space marital underscore status space pipe symbol space attrition underscore flag space pipe symbol space income underscore category close single quotations semicolon each value is concatenated by a pipe symbol once the variable input reads this value and places it into the drop down the value will be shown as four separate lines you can also assign the label to each value by using this syntax value 1 tilde label 1 space pipe space value 2 tilde label two the whole string is enclosed in single quotes to save it as a string next insert another line on the script then type Let space V Dimension selection equal open single quotation space close single quotations semicolon we're going to use this variable to hold the select value from the drop down and assign this as the dimension for the bar chart we've inserted space as the placeholder to avoid errors in the edit sheet window since no values are selected by default upon creating the dashboard with the variables created we can now proceed with building The View navigate to the analyze sheet Tab and edit the sheet insert a variable input by going to custom objects click dashboard bundle and dragging variable input into the sheet The Click dashboard bundle is automatically included in the qlik sense Enterprise setup since the November 2018 release first select the method of showing the input data navigate to appearance variable show as and select drop down from the list then add the values to the drop down go to the next Sub menu values and select Dynamic variable Dynamic variable lets us use items that we have defined earlier in the data load editor wherein each value is separated by a pipe symbol if you choose the fixed value you need to add each item manually using the add alternative button and then add a value and label for each item after setting Dynamic open the expression editor for the dynamic values insert the variable V list Dimension and then close the expression editor if no errors are found this assigns the content of the variable or the dimension names as the value inside the drop down next we need to assign where to save the selected Dimension navigate to the variable submenu under appearance and set the variable name to V Dimension selection this places the selected value into a variable that we can use for building the bark chart later on if we do not save the selection to a variable no changes will be applied to the sheet since we simply asked for a dimension and did nothing afterwards add a title to the variable input and insert a clear instruction for the user navigate to appearance General toggle the show titles to on then type selected dimension for the bar chart colon in the titles text box proceed with creating the bar chart insert one by going to charts bar chart and placing it below the variable input remove the title by going to appearance General toggle show titles to off next insert the selected value as the dimension by using the variable as the value navigate to data dimensions and click add Dimension open the field expression editor and type in equals dollar sign Open brackets V Dimension selection close brackets we're using the dollar sign expansion method in order to direct click sense to use the field value counterpart of the selected Dimension instead of the string that was sorted hit apply to add the dimension next open the labels expression editor we want it to have the field name as its label so type equal V Dimension selection we simply inserted the variable name since we only want to use the dimension as the label with the dimension part configured proceed with adding the measure of the bar chart click add measure and choose the field client num with the aggregation method count this will count the total number of client IDs per Dimension or bar finally configure the bar's color navigate to the bar chart's appearance colors and legends and toggle color to custom set the color by measure using the sequential gradient then remove the color Legend once the configuration is complete click the done editing button to test our drop down variable by choosing a different dimension from the drop down we can change the dimension displayed in the bar chart just make sure to adjust the size of the chart to take into consideration Dimensions which have more members for our second example let's create a table that will hold the top product categories by sale the catches that we need a slider that limits the number of Records shown in the table so if the slider is set to 5 the table should have five records the input we're going to use is the superstore sales data sets order sheet we only need the feels product subcategory as well as sales to start building the table navigate to the sheet tab edit the sheet then insert a table from the charts menu next add data navigate to the tables properties pane data and click add column dimension select product subcategory from the list next add the measure click add column measure select the field sales and use sum as the aggregation method next let's add another column to show the rank of each product subcategory based on sales to serve as a guide later click add column measure and open the expression editor type in aggr Open brackets rank Open Bracket sum Open brackets sales close brackets close brackets comma product subcategory in square brackets close brackets this expression calculates the value by first summing up the sales then applying rank based on the sales value and finally aggregating it by product subcategory make sure there are no errors where found and hit apply to check if the value shows up on the table now that we have the values visible in the table make sure that it is sorted properly under the text tables sorting tab drag sum of sales to the top of the list now we need to create a slider that will take in the user's desired number of records and essentially filter the table from the custom objects drag a variable input and place it on top of the table then remove the title from the slider to hold the slider's value we will create a new variable using the variables dialog open the variables dialog and click create new set the name as V top limit then for its definition we will place a value of 1 as the placeholder once the variable has been created navigate back to the slider properties under the variable submenu set the variable V top limit and show as slider tick update on drag so that the variable value gets updated every time the slider moves for the values set Min to 1 to prevent negative values for the max open the expression editor and type equals count Open brackets space distinct product subcategory in square brackets close brackets this makes the max value Dynamic based on the distinct number of product subcategories available in the connected data also tick the slider label to display the slider value for the final steps we need to apply the limit to the table select the table to open its properties and navigate to data columns product subcategory under limitation choose fixed number and select top for the value itself open the expression editor and insert V top limit once the limit has been set let's add a title to our table I'll still under the table properties navigate to appearance toggle show title on then open the title text expression editor type in equals open single quotations top space close single quotations and sign space dollar sign Open brackets v-top limit close brackets space and open single quotes space product space sub Dash categories close single quotes once all configurations are complete click the button done editing in the upper right to check our output moving the slider to the right should increase the number of Records in the table the number in the slider should also match the number indicated in the text table importing a data source is not always a straightforward process wherein we only need a single file or table and then simply place it into qlik sense to use it for dashboards most of the time an insightful visualization is built only after the painstaking process of finding the essential tables from different types of sources cleaning the data creating the required lookups then combining them into a chart having a way to load data dynamically can prove to be beneficial as it increases the number of ways that we can import data into an app let's look at these methods one by one first we will demonstrate how to load multiple files into a folder using the data load script we're going to load all six files from this customer sample table into qlik sense first create a new app and start from scratch we're going to name this as 1-3-dynamic space loading open the app once created and navigate straight to the data load editor after reaching the main section our first step is to create a new data connection to insert the script click the button create new Connections in the upper right under load data select file location folder then navigate to the file path that contains the files we need you can also paste the complete path in the text box to make it easier once we're in this specific folder type a name for the connection we're going to name this connection as customer folder hit create once done now that the new connection is available in the data connections pane click on the select data button and choose any one of the files available in the folder here we are going to select customers underscore1.csv and hit select to confirm once selected we can now insert the created load script for this file do make sure that all the files to be imported have the same structure and file extension if some of the files have a different field it may cause errors during script execution click insert script to add the new load statement to our main section now we need to adjust this load script to accommodate other files in the customer folder data connection since the file name of our files has the schema customer underscore number we simply need to replace the number with a wild card replace the one in customers underscore 1.csv to an asterisk to create customers underscore asterisks.csv this way our new data connection will look at all CSV files that have the prefix customer underscore and included upon loading the data to our app click load data to check how many records are imported as we can see from the output message the app has fetched six files from customer underscore 1 up to customer underscore 6. the message also shows the running total numbers of Records fetched we started with a thousand records from the first file and ended with a total of 5500 records from all six files in the folder if you wanted to show the file name for each record you can add a function in the load script after the married column insert a new line and type comma file name Open brackets close brackets space as space file tag then load the data from the function name itself this adds the file name to our data you can check this by going to the sheet tab edit sheet then drag the field file tag into the sheet this should create a table which contains a list of all the files that we've imported using the wildcard in our load script for our next example we are going to apply incremental load using a MySQL table connection but first what is an incremental load an incremental load is applied to a new or updated records are retrieved from the database while old or historical data is retrieved from a local storage if your app contains a large amount of data from database sources that are continuously updated reloading the entire data set can be time consuming basically this is the method preferred when it is not feasible to reload the entire data set into a click application by using the incremental loading process you can save time and resources as well as reduce the burden on the database especially if the app is frequently refreshed or reloaded here are four examples of how incremental load can be used append only typically used for log files insert only no update or delete insert and update no delete insert update and delete let's look at each use case one by one foreign is used when records are only appended and never deleted as previously mentioned the simplest example would be log files where we simply add new records but are not removing updating nor deleting other historical data the following conditions apply when using append only one the database must be a log file or some other file in which records are appended and not inserted or deleted which is contained in a text file odbc olaydb all other databases are not supported two qlik sense keeps track of the number of records that have been previously read and loads only records added at the end of the file insert only is when the data resides in a database other than a simple log file so if you need to save data in a source file other than a local file insert only would be the easiest alternative to append only the following conditions apply when using insert only one the data source can be any database two qlik sense loads records inserted in the database after the last script execution three a modification time field or similar is required for qlik sense to recognize which records are new insert and update is applicable when data in previously loaded records may have changed between script executions you can use this for data that needs updating but has no deletion such as customer records the following conditions apply when using this method one the data source can be any database two click sends loads records inserted into the database or updated in the database after the last script execution three the modification time field or similar is required for qlik sense to recognize which records are new for a primary key field is required for qlik sense to sort out updated records from the qvd file and five this solution will force the reading of the qvd file to standard mode rather than optimized which is still considerably faster than loading the entire database insert update and delete is the most difficult case to handle since records are actually deleted from The Source database between script executions data sets that need deletions such as inventory records will benefit with using this type of incremental load case the following conditions apply one the data source can be any database two qlik sense loads records inserted into the database or updated in the database after the last script execution three qlik sense removes records deleted from the database after the last script execution for a field modification time or similar is required for qlik sense to recognize which records are new 5. a primary key field is required for qlik sense to sort out updated records from the qvd file 6. this solution will force the reading of the qvd file to standard mode rather than optimized which is still considerably faster than loading the entire database all four use cases are used in conjunction with qvd files a qvd click view data file is a file containing a table of data exported from qlik sense qvd is a native click format and can only be written to in read by qlik sense or click View the file format is optimized for Speed when reading data from a script but it is still very compact reading data from a qvd file is typically 10 to 100 times faster than reading from other data sources a qvd can be created in two ways explicit creation and naming using the store command in the script state in the script that a previously read table or part thereof is to be exported to an explicitly named file at a location of your choice here are the steps we took to apply explicit creation of qvd using the store command first we've created a new folder called qvd file to serve as a saved location then a dummy text file called one.txt was created temporarily so we can insert the script for the saved path with the folders prepared a new connection called qvd file is created in qlik sense the save path is pasted in this window to connect the app to the folder we've created earlier once done the one dot txt file script is inserted into the load editor just to get the full Library save path then we've saved the original load script to a table called customer underscore Consolidated we will use this table and place it into the qvd using the store function the access load statement from the1.txt file is removed leaving only the lib path without the txt file name the script replacing it into the file is store space customer underscore Consolidated space into space open square brackets path forward slash filename.qvd close square brackets open square brackets path forward slash file name Dot qvd close square brackets Open brackets qvd close brackets semicolon after storing the file you can simply drop the table since it is already in the qvd the script syntax would be drop space table space table name after loading the data you can now see the output file in the selected folder automatic creation and maintenance from script when you precede a load or select statement with the buffer prefix qlik sense will automatically create a qvd file which under certain conditions can be used instead of the original data source when Reloading Data in this data load script we are simply going to add the function buffer before the load statement the qvd will then save into the Buffer's subfolder which is typically C program data slash click slash sense slash engine slash buffers or C users slash user in curly brackets slash documents slash click slash sense slash buffers since we haven't loaded the data no files were currently saved in the folder click load data to see the qvd files after running the script six qvd files were created since we loaded six customer files into qlik sense a separate buffer or qvd file was created for each for this lesson we're going to look at how we can apply the insert and update use case of incremental load to a qlik sense app as a review insert and update incremental load is applicable when data in previously loaded records may have changed between script executions so we are not simply putting data but we are also updating the records from the historical copy to create this type of incremental load we need to have a database Source a modification Time Field a primary key field to recognize which records are new and finally a qvd to serve as data storage for this example we're going to insert and update casino game transactions into a qlik sense app our table is saved in the mySQL database and accessed in the app using an odbc connection the database connection is already pre-created in the data load editor as my SQL space click space advance to save time on our demonstration the data folder path for the qvd is also created with the name 1-4 space output as seen from our file manager the qvd folder is currently empty since we haven't created the file using the data load script yet the first step is to create an initial full load of data to the qvd using the script if we look at the table on my SQL our game transactions table initially has 808 records with Max ID 2013.001 010 and was last updated on October 7th 2022 at 12 am we're going to create a full load script to save all of these 808 records to the qvd first create a new section called full load using the my SQL click Advanced odbc connection insert the script for the table click ADV underscore game transaction you can opt not to include the load statement by deselecting the include load statement checkbox in the lower right once the table has been selected click the insert script button to add the SQL select statement to our full load section we do not need to show all of these columns in the script so we can simply replace the columns and add an asterisk to create SQL space select space asterisks space from space click underscore ADV dot click ADV underscore game transactions semicolon we will enclose this script to a table called full load by inserting the script full load colon before the SQL select line next we can now store the game transactions record into a qvd insert a new line after the SQL select statement then type store Space full load space into space open square brackets lib colon forward slash forward slash 1-4 space output forward slash gametransaction Dot qvd close square brackets Open brackets qvd close brackets semicolon since we've saved the contents of the table full load into the qvd we can drop a table afterwards on a new line after the store script type in drop table full load semicolon with a full load script done hit the load data button once the data load is complete the game transaction dot qvd file should now be visible in the 1-4 folder using the easy click queue viewer app you can check the contents of this output as we can see in this window there are 808 records available upon applying the full load script next we will demonstrate how to insert and update the records in the app and qvd after updating the table in other words once the 809th record and other new lines are added to the database we will pick up the new records directly from the database table and insert them into the app as for the old records that were updated we will also update their information we're going to use the file 1-4 Dash additional gamingtransactions.sql to update the gaming transactions table it contains 202 new records to insert and two records to update the updated date time column of this record are set to October 10 2022 8 pm now copy and paste the SQL script and go to the MySQL workbench then run the script now that we have the new data ready we can continue to the data load editor of our qlik Sense app on the full load section of our data load editor we need to comment the entire block so as not to run this part upon clicking data we already have the qvd file from the first run earlier so this part is no longer needed to comment the entire block select all of the script and run the comment button in the upper right this should gray out the entire script for this section you can always rerun this script by uncommenting it and loading the data to refresh the qvd which can be done regularly with the full load aside create a new section and name it incremental load for the first two lines in this section type last update colon load space Max Open brackets double quotes updated space date time close double quotes close brackets space as space last date time space from space open square brackets lib colon forward slash forward slash 1-4 space output forward slash gametransaction Dot qvd closed square brackets open square brackets qvd closed square brackets semicolon this statement takes the maximum updated date time value from our last full load run of game transactions and saves its value into the table called last update we will need this value later to figure out if the specific records are new or not add another line on the script then type Let space V last update date time equals space date Open brackets Peak Open brackets open single quote last date time close single quote close brackets comma open single quote WHYY Dash m m Dash DD space HH colon mm colon SS close single quotes close brackets semicolon as we can see from the queue viewer the qvd file has saved the date time into a number to save space so in order to use the date time value correctly in the script we've converted it back to its proper date time format using the date function on the other hand the peak function was used to return the value that has already been loaded with the value in its proper format it is then saved into a new variable called V last update date time which we will use later in conjunction with a where Clause to filter the records taken from the database since we now have the last update date time we can drop the last update table that was created in a new line type in drop space table space last update semicolon next we need to connect to the game transaction table and take only the records which were updated recently to do so we need to insert the connection string as well as the SQL select statement copy the first three lines from the full load statement then remove the comment once removed replace the full load colon with game trans colon to create a new table the script should look like this lib space connect Space 2 space open single quotes my SQL space click space Advanced close single quotes semicolon game trans colon SQL space select space asterisk space from space open single quote click underscore ADV close single quotes Full Stop open single quotes click ADV underscore game transactions close single quotes on a new line add the where statement that will filter the records to take from the database type in where space open single quotes updated space date time close single quotes space greater than sign space open single quotes dollar sign Open brackets V last update date time close brackets close single quotes semicolon this checks for the updated date time records that were recently modified or updated by comparing it to the value of the variable V last update date time let's proceed with the concatenating of the new records with the old records from the qvd to concatenate type in a new line concatenate Open brackets game trans close brackets then type the load script and take the value from the qvd enter load space asterisks space from space Open brackets lib colon forward slash forward slash 1-4 space output forward slash gametransaction Dot qvd close square brackets Open brackets qvd close brackets space where space not space exists Open brackets open single quotes game Space ID close single quotes close brackets all right semicolon the where not exists statement is crucial to the script so the qlik sense engine can compare the game ID field from both the new and old records with the data complete you can now save this data into the qvd by typing this in a new line store space game trans space into open square brackets lib colon forward slash forward slash 1-4 space outputs forward slash gametransaction Dot qvd close square brackets Open brackets qvd close brackets semicolon if you want to use this data on the app you can stop your code here and run the script but since we are not going to apply the front end for this app we're going to drop the table and end the script by typing drop space table space game trans semicolon then exit space script semicolon with the incremental load script complete hit load data to run it as we can see from the output message the qvd is updated and now contains 1010 total records let's open the qvd using Q viewer to check the data we've started with 808 records during the initial full load added 202 new lines and updated two lines which match the 1010 records that were now available in the file the two updated game IDs 2013.0002 and 2013.0003 should also show their updated game time which is five thousand and nine thousand seconds respectively we've seen from the past lessons that scripts on data load editor can become lengthy and elaborate depending on the complexity of the app you are developing to make the process more efficient qlik sense offers ways to reduce the lines of codes using script files and subroutines script files are composed of blocks of code that are stored in external files such as dot qvs or Dot txt and they can be included in your application with a single reference QBs is the abbreviation for the click view script file to open qvs files you can use any editor such as notepad notepad plus plus or Adobe Dreamweaver at the same time you can also create a qvs using the editor program by saving it with the extension.qvs by using script files you can standardize the back end data preparation across multiple applications this lets you use the same script file for different apps that need the same data prep process which saves time and makes your script management a lot more efficient let's look at how we can package code in script files as well as how to call them to load and prepare data in a new app in this section of the load data editor we have created an inline script that loads the branch ID Branch name and sales records of a convenience store in addition we've also created a resident load of the maximum number of sales into the table called top branch the resident load loads data from a previously loaded table this type of load is useful when you want to perform calculations on data loaded with a select statement where you do not have the option to use click functions such as date or numeric value handling in this case we are using resident load to apply the max function to the inline loaded sales value since this script is not yet loaded the app does not recognize the data yet we will use this script package a script file which will then be called in this app first copy all of the script in this section next open your preferred editor here we're going to use notepad plus plus in a new file paste the script from the qlik sense app once done save the file we're going to save this under the 1-5 folder uncheck the append extension then type the file name convenience space store space sales Dot qvs before saving we are also going to copy the saved path to use it in qlik sense later on hit save to create the qvs file you can also open and edit this qvs later by opening it using an editor next go back to the qlik sense app and create a new connection select folder then paste the copied save path into the path text box we always need to create a new data file connection when referencing files saved in the computer or server to isolate direct access to the file system if you can remember we also use the same method when saving qvd files as well as loading multiple files from a folder let's name this connection as qvs storage then hit create to call the qvs file we will be using the include variable the include slash must underscore include variable specifies a file that contains text that should be included in the script and evaluated as script code you can store parts of your script code in a separate file and reuse it in several apps this is a user-defined variable on our data load editor type dollar sign Open brackets include equals lib colon forward slash forward slash qvs Space Storage forward slash convenience space store space sales dot qvs semicolon once done click load data in the output message the app should fetch 10 records from the sales table and one record from the top Branch table but what would happen if we referenced a qvs file that was not available in the path change the file name specified on the include statement convenience space store space sales1.qvs then hit load data as we can observe no errors popped up in the output message and it was still completed successfully this would be detrimental for apps that require the script file to be loaded successfully as the load process did not alert the user that no files were found with the specified name to generate an error we need to use the must include variable in our script type must underscore before the include variable then hit load data Upon finishing the script notice how must underscore include differs from include the output message shows an error that the must underscore include statement has failed and data has not been loaded this way the user can identify which QBs is missing and needs to be checked if remove the one from the file name and load the data the must include statement will still read and evaluate the script in the file and use the tables mentioned in the qvs file by using the qvs we were able to replace the block of code with just one line if you have script sections that can be packaged and reused with other qlik sense application it makes sense to store it in a script file for others to use it is also worth pointing out that the block of code can still be placed directly in the data load editor and will be ready to use easily by others but by using the include and must include variable you can increase the value of the created script by using them across different apps this process makes it good for Source control subroutines are conceptually similar to script files but they have a subtle difference in usage subroutines contain reusable calculations or Expressions which you can call in the script multiple times instead of calling a block of script from an external script file subroutine calls a section of script from within the data load editor section so instead of rewriting a calculation on a script several times you can simply write the code once then call the name of the subroutine to execute the lines inside it for this second example we're going to bring in the inline load and Resident load script that was used on the include statement we also changed the include statement into a comment to avoid duplicate tables we're going to create a subroutine that counts the number of rows in the table after it has been loaded so once the sales table is loaded we're going to call the subroutine to display the number of rows in sales then load the top Branch table and finally call the subroutine again to calculate the number of rows for the top Branch table before calling any subroutine we need to create one first it is generally best practice to create subroutines at the top most line of your script to ensure that the subroutine is created first and to make it easier to find if you already have hundreds of lines of scripts to create the subroutine we are going to use the sub and the sub statement type in Subspace count table Open brackets V table close brackets count table would be the name of our subroutine while V table is the variable that we will use as a placeholder for the table names later on now we can add the calculations and expression in this subroutine on a new line type Let space V row count space equals space new rows Open brackets open single quote dollar sign Open brackets V table close brackets close single quotes close brackets semicolon this counts the number of rows within the specified table then saves its value to the new variable called V row count add another line of script then type Trace space dollar sign Open brackets V table close brackets space has space dollar sign Open brackets V row count close brackets space rows semicolon the trace statement writes a string to the script execution progress window and to the script log file when used it is very useful for debugging purposes by using dollar sign Dash expansions of variables that are calculated prior to the trace statement you can customize the message so for this subroutine we're using the trace statement to show the table name as well as its total number of rows to end the subroutine add another line then add end sub semicolon with the subroot created we can now utilize this calculation in the script place the line pointer after the sales inline load then type call space count tables Open brackets open single quotation sales close single quotations close brackets semicolon the call control statement calls a subroutine which must be defined by a previous sub-statement as you might have noticed we've also included the string sales enclosed and single quotes inside parentheses to pass this value to the V table variable that we've created in the subroutine earlier this way the subroutine count table will know which table to use for its calculation after calling the subroutine for the first time the top Branch table resident load is next according to the flow of the script we will call the subroutine again after this resident load to count the number of rows for top branch after the resident load script type this on a new line call space count table Open brackets open single quotation top branch close single quotation close brackets semicolon instead of sales we've saved the string top Branch to change the table that will be counted hit load data to show the output of our subroutine as we can see in the output message after fetching the table sales there is a message line that states sales has 10 rows same is also seen after fetching the top Branch table the text top branch has one rows is shown as output which signifies that our subroutine has been executed correctly foreign functions that reduce length and code redundancy qlik sense also offers a lot more functions that can be utilized both in script and chart expressions let's look at two conditional functions match and pick conditional functions are mostly used to evaluate conditional expressions and return results based on the condition but the two mentioned functions do not refer to a condition to create results but instead look at a defined set of values match and pick are a bit similar when it comes to their approach to checking the data but differs in the type of output we will demonstrate how the match function works first match compares the first parameter with all the following ones and Returns the numeric location of the Expressions that match the comparison is case sensitive we will use the match function to reduce the number of accessories loaded in this app we've already loaded the accessories inventory Excel file to this app so we can head straight to the data load Editor to edit the load statement once we're on the data load editor window unlock the auto-generated section once unlocked insert a new line before the final semicolon symbol then type where space match Open brackets open square brackets variation underscore color close square brackets comma single quotes blue single quotes comma bass single quotes pink single quotes comma bass single quotes black single quotes space close brackets greater than sine zero the syntax of this function is composed of a string field to check foremost Then followed by the parameters where the field will be compared each parameter is concatenated by a comma the match function looks at each value in the variation underscore color field if it matches any of the parameters blue pink or black it will return the numeric location based on the parameter order that we've indicated so if qlik sense found a blue accessory it will return the number one since it is the first color that we mentioned in the code we've used the match function in conjunction with the where statement to limit the variations of accessories to feed into our app we've added the condition that it should be greater than zero to remove the items that are neither blue pink or black let's check if we've got the correct output by showing data in a table click load data then head to the sheet Tab and click edit sheet in the edit sheet window add a table by navigating to charts and then dragging the object table into the sheet once placed add a column select Dimension then choose accessory underscore name next add another column and use a chart expression type equals aggr AV Open brackets concat Open brackets variation underscore color comma single quotes comma single quotes close brackets comma accessory underscore name close brackets in this expression we are concatenating each variation color under individual accessory names using the aggregation function apply the expression and check the output on the table the variations available should only be blue pink or black since we've filtered out the rest do take note that the match function is case sensitive so each parameter used must match the letter case indicated in the Excel file if you need to create a case insensitive match we will discuss other match variations that might fit your needs pick function Returns the nth expression in the list compared to match it outputs the expression rather than the position of the parameter since the pick function outputs an expression we can use it to make our apps more Dynamic let's look at how we can use pick to create a dynamic measure for this table first let's create a variable that will hold the measure selected by the user navigate to the data load editor auto-generated section and add a new line after the load statement type Let space V measure selected space equals space single quotes orders single quotes semicolon then click load data once loaded navigate to the edit sheet window next insert a variable input which is located under custom objects place the variable input on the right side of the table next configure the properties of the variable input under appearance General hide the title then navigate to the next subsection and set the name to V measure selected or the new variable that we've created from the script earlier then show as drop down once done set the values in the next subsection we've already set the default as orders but we still need to add the full list for the drop down under fixed or dynamic values select Dynamic then open the expression editor for the dynamic values type equals single quotations orders pipe stocks pipe price single quotation take note of the letter case and spelling that we've used for this list since we are going to match this later on now that we have our variable input configured it is time to add the dynamic measure to the table select the table then navigate to data add column measure then open its expression editor type equals pick Open brackets space match Open brackets single quotation dollar sign Open brackets V measured selected close brackets single quotation comma single quotation orders single quotation comma single quotation stocks single quotation comma single quotation price single quotation close brackets space comma space sum Open brackets orders close brackets space comma space sum Open brackets stocks close brackets comma space Max Open brackets price close brackets close brackets notice that we've used both match and pick for this expression this is because an integer is needed for the pick function to return the expression so first we used match to compare the user's selected measure to the list of orders stocks and price which will then output a number of either 1 2 or 3 based on the chosen measure the pick function will then take this number to select the measure applied to the table for example the default is set to orders which will output 1 based on the match function then this output of 1 will point to the first expression for the pick function which would be the sum of orders so it would be extremely important to align the order of your expressions and parameters correctly when you're using both the match and pick functions hit apply to add this expression to the table next let's add a label as a header to this new column in the label text box type equals V measure selected to make it Dynamic and display the selected measure as a header once done hit done editing to test our output if we change the drop down to stocks it would change the numbers on the column and display the total amount of stock the same would happen if we change to the last option which is price as mentioned earlier there are other variations of the match function that you could use for case insensitive data one of these variations is mix match the mix match function compares the first parameter with all the following ones and Returns the numeric location of the Expressions that match the comparison is case in sensitive let's use mix match to edit the where statement in the same app that we've been working on on the data load editor remove the hole where statement then type where space mix match Open brackets open square brackets accessory underscore name close square brackets comma single quotes 3D space glasses single quotes comma single quotes I space mask single quotes close brackets greater than sign zero notice that the syntax is the same as that of match but their only difference is in terms of functionality mix match is case insensitive so it does not check for the letter case of the characters run the script to load the data and show which accessories are listed on the sheet as we can see on the sheet two accessories are listed 3D glasses and an eye mask this letter case does not match our parameter but since they have the exact spelling the accessories still passed the filter same with match mix match will also output the numeric location of the accessory based on the orders of the parameters we've indicated but will return 0 if no matches are found but what if we need to filter using a set of characters since we don't have the exact name of them in this case we can utilize the wild match function while match compares the first parameter with all the following ones and Returns the number of the expression that matches it permits the use of wildcard characters asterisks and question Sign In the comparison strings asterisks matches any sequence of characters question mark matches any single character the comparison is case insensitive using wild match let's filter the accessories which end in prop and accessories that have exactly three characters before the word mask to do so go back to the data load editor and remove the whole where statement replace it with where space wild match Open brackets open square brackets accessory underscore name close square brackets comma single quotation asterisk prop single quotation comma single quotation question mark question mark question mark space mask single quotation close brackets greater than sign zero wild match is case insensitive so letter case is not checked in this line we are looking for accessory names that end with prop and accessory names that have exactly three letters or numbers before the word mask the condition greater than zero is still kept in the statement because any rows that have a match will have the numeric output of the order location based on our parameters which can be either one or two hit load data to check the results as we can see from the table six accessories matched our first parameter that all ends in prop while two accessories matched our second parameter with exactly three characters before the word mask which resulted in an eye mask and gas mask if you're not a subscriber click down below to subscribe so you get notified about similar videos we upload to get the files the instructor used in this tutorial and follow along click over there and click over there to watch more videos on YouTube from Simon says it

Channel: Simon Sez IT

Views: 24,923

Keywords: qlik sense, qlik, qliksense, qlik advanced, qlik sense advanced, qliksense advanced, qlik sense on windows, business intelligence, qlik sense training, data visualization, qlik sense tutorial, data analytics, learn qlik sense, qlik sense trainings, qliksense course, qliksense tutorial, learn qliksense, data analysis, data modeling, qlik bi, qlik sense BI, data science, business intelligence software, data analyst, business analytics, qlik sense visualization tutorial

Id: ny8d2XlTWsQ

Length: 209min 41sec (12581 seconds)

Published: Tue Jun 06 2023