1 2 3 4 Previous Next

Qlik Design Blog

49 Posts authored by: Jennell McIntire

A couple of years ago I wrote a blog on customizing straight tables in QlikView explaining how you can add an ad-hoc report to your QlikView app.  So, I thought I would share how you can now create a custom report in Qlik Sense using the Climber Custom Report extension.  The Climber Custom Report is an extension that can be added to your Qlik Sense app to give users the ability to create their own ad-hoc reports.  In this blog, I will show you how easy it is to add a custom report to your app using Qlik Sense Desktop.

 

  1. The first step is to download the Climber Custom Report extension from Qlik Branch, unzip it and put it in your Extensions folder (C:\Users\xxx\Documents\Qlik\Sense\Extensions).
  2. In your Qlik Sense app, create a table with all the possible dimensions and measures a user may want to see in a report and then add the table to master items.  The table may look something like this: table.png
  3. Add the Climber Custom Report extension to a sheet in your app and then you are ready to create a report.
  4. In the Visualizations drop down, select the report you just created.  All tables that are in your visualization master items will be listed in the drop down.  Once the table is selected, the dimension and measure lists will be populated with the dimensions and measures that are used in the table as seen below.dimensions and measures.png
  5. Select the dimensions and measures you would like to add to your report by clicking on them.  Your report will look something like the image below after you make some selections.  The dimensions are blue and the measures are orange.selections.png
  6. From the custom report bar above the chart, you can remove a dimension or measure by clicking the x and you can change the order of the columns by dragging and dropping the dimensions and measures into the order you would like them to appear in the report.

 

And that is it - it is that simple to add a custom report to your Qlik Sense app.  With Qlik Sense self-service, a user can create a report by dragging and dropping dimensions and measures into a table but what I like about the Climber Custom Report extension is it makes everything available to the user with a clean, professional and organized look.  Download it now and test it out for yourself.  See the extension in action in the Situational Awareness demo.  Note - the Climber Custom Report extension works in Qlik Sense 3.0 and higher and, like all extensions, are not supported by Qlik.

 

Thanks,

Jennell

The ability to make selections and see what data is associated is one of the powerful capabilities of Qlik Sense and QlikView.  Selections allow users to explore the data in an app and to answer their specific questions at any given time.  In this blog, I will discuss the following selection options you may find in a selection pop-up window (shown below): Clear selection, Select all, Select possible, Select alternative and Select excluded.

popup.png

Clear selection

Let’s start with the Clear selection option.  As you may expect, this will clear all selections that have been made in an app excluding locked selections.  Locked selections are selections that cannot be cleared or changed.  They are used when the user wants to protect a selection.

 

Select all

Select all will select all values in a field making them green.  If there are excluded values in the field when you select all, then they will become selected excluded – these items will remain gray but they will get a check mark next to them indicating that they are also selected.  In the image below, Dairy was selected in the Product Group field and Cheese was selected in the Product Sub Group field.  All the other values in the Product Group field are excluded and therefore gray.  Once all values are selected in the Product Group field, the excluded items stay gray but now have a check mark to indicate they are selected excluded.

select all.png

If the selection that excludes some of the values (which is Cheese in this example) is removed then they will all become selected and turn green.

 

Select possible

To explain the select possible selection, let’s first define possible values.  Possible values are values that are not selected and not excluded by a selection.  They appear with a white background.  For example, all values in a filter pane will be possible if no selections have been made.  In the image below, Dairy is selected in the Product Group field and the Product Sub Group has 5 possible values (the first 5 values in the list).  The possible values are product sub group items that are associated with the Dairy selection.

what are possible.png

If select possible is applied to the Product Sub Group, you will get the following results:

select possible.png

Select alternative

What are alternative values?  Alternative values (light gray) are values that would have been possible (white) if a selection was not made in the field.  We have already seen an example of that in the image below.  In this example, Dairy was selected first and then Cheese was selected.  Before Cheese was selected, the first five values in the Product Sub Group field were white (possible values).  After Cheese was selected, Cheese became selected (green) and the other four values became alternative (light gray).

selected dairy and cheese.png

Select excluded

Select excluded will select all the non-selected values in a field.  If Dairy is selected in the Product Group field, then select excluded will select all values that were excluded (gray) and will make them green and the Dairy selection will become an alternative value (light gray).  If Dairy (Product Group) and Cheese (Product Sub Group) were selected and select excluded was selected in the Product Sub Group field (see image below), then the selected value Cheese becomes an alternative value (light gray), the possible values become green and selected and the excluded values become selected excluded (gray with a check mark).

select excluded.png

The selection options reviewed in this blog can be used not only in filter panes and the selections tool but they can also be used in charts.  This gives the user the ability to drill down in the data and see what data is associated and excluded by selections.  Selections are very powerful so it is important to know all your options and how you can make selections to analyze your data.  The example images used in this blog are based on the data in the Consumer Goods Sales demo.  Feel free to use the selection tool in the app to test out selections or log in to qlik.com so you can add your own filter panes to the demo app.

 

Thanks,

Jennell

Today I am going to blog about five Counter Aggregation Functions that can be used in Qlik Sense and QlikView in both charts expressions and the script.

  1. Count()
  2. MissingCount()
  3. NullCount()
  4. NumericCount()
  5. TextCount()

Before taking a closer look at how we can use each of these functions, let’s first look at the data set I will use for the examples.  Below is the Excel data I will load.  It is a simple list of fruits, their color and quantity.

Excel.png

1. Count()

The Count function is probably one of the most common functions that can be used.  In a chart, Count() aggregates the number of values in each chart dimension.  In the script, Count() returns the number of values aggregated in the expression as defined by a group by clause.

 

Expression for a chart: Count(Distinct Fruit)

 

In the script below, Count() will return color and the number of fruits that have that color.

Count.png              Count Table.png

2. MissingCount()

In a chart, the MissingCount() function will aggregate the number of missing values in each chart dimension.  In the script, it will return the number of missing values aggregated in the expression, as defined by the group by clause.

 

Expression for a chart: MissingCount(Quantity)

 

In the script below, MissingCount() will return 1 if the Quantity field is missing a value.

MissingCount.png              MissingCount Table.png

3. NullCount()

NullCount() will return the number of null values in each chart dimension in a chart.  In the script, NullCount() returns the number of null values aggregated in the expression, as defined by a group by clause.

 

Expression for a chart:  NullCount(Color)

 

In the script below, NullCount() returns 1 if the Color field is null.

NullCount.png              NullCount Table.png

4. NumericCount()

In a chart, NumericCount() aggregates the number of numeric values by each chart dimension and in the script, NumericCount() returns the number of numeric values found in the expression, as defined by a group by clause.

 

Expression for a chart: NumericCount(Quantity)

 

In the script below, the total Quantity fields that have numeric data is returned.

NumericCount.png              NumericCount Table.png

5. TextCount()

In a chart, TextCount() aggregates the number of non-numeric values by each chart dimension and in the script, TexCount() returns the number of non-numeric values found in the expression, as defined by a group by clause.

 

Expression for a chart:  TextCount(Color)

 

In the script below, TextCount() will return the total number of text values in the Fruit field.

TextCount.png              TextCount Table.png

Here is a table with all these functions used in a chart:

Table.png

While I have not used all of the counter aggregation functions in my work with QlikView and Qlik Sense, I think they can be valuable when auditing and checking the health of your data.  They can highlight gaps in the data that should not be there and point out data type issues in the data.  I would be interested in hearing how you use these functions in your apps.

 

Thanks,

Jennell

Jennell McIntire

Binary Statement

Posted by Jennell McIntire Feb 10, 2017

Have you ever used a Binary statement in your script or done a “binary load” as folks familiar with Qlik scripting may say?  The Binary statement can be used in both QlikView and Qlik Sense scripts to load data from another QlikView document or Qlik Sense app.  When using QlikView, the Binary statement can only be used to load another QlikView document.  When using Qlik Sense, the Binary statement can be used to load another Qlik Sense app or a QlikView 11.2 or earlier document.  Note that you cannot use a Binary statement in a QlikView script to load a Qlik Sense app.  The Binary statement will load not only the data but the section access data as well.  It does not load variables or any layout information such as charts, sheets or stories from the app or document.

 

The Binary statement is helpful when you want to use the data model from an existing app or document in a new app or document.  Instead of copying the app/document and then deleting the sheets, stories, etc., you can create a new app/document and load just the data using the Binary statement.  Then you have a fresh slate to build out the sheets and visualizations.  Chuck Bannon wrote a blog on ways you can extract data from a document which you may find useful.  In his blog, he uses the Binary statement to get the data from another document and then he stores the data into QVDs.  I personally prefer the second option he discusses that uses a for loop to create the QVDs.  Storing the data into QVDs is useful if you need to manipulate the data when loading it.

 

There is one thing you must remember to do when using the Binary statement in order for it to work – you must make the Binary statement the first statement in the script.  This means adding it before the Set statements.  The syntax varies for QlikView and Qlik Sense so let’s take a look at both.

 

In QlikView, either statement below can be used.  In the first example, the MyApp document will need to be in the working directory otherwise the absolute file path will need to be included as seen in the second example.

QV1.png

QV2.png

In Qlik Sense, you will need to create a folder connection to the app you would like the use.  In the example below Demo Apps is the folder connection I created to access the MyApp QVF.

QS1.png

Simple, right?  The Binary statement when loaded as the first statement in the script will load the data from another QlikView document or Qlik Sense app providing you with the same data model as the original app and a clean slate to build out your visualizations.  Good luck scripting!

 

Jennell

Jennell McIntire

Field Functions

Posted by Jennell McIntire Jan 13, 2017

There are several field functions that can be used in Qlik Sense and QlikView charts to return information about a field and/or selections in an app.  They are:

 

  1. GetSelectedCount
  2. GetAlternativeCount
  3. GetPossibleCount
  4. GetExcludedCount
  5. GetCurrentSelections
  6. GetFieldSelections

 

In this blog, let’s take a look at how each of these functions can be used in your chart expressions.

 

GetSelectedCount

getselectedcount(field_name [, include_excluded])

This function returns the number of selected (green) values in a field.

 

The GetSelectedCount of the Product Group field below will return 3 for the 3 selected (green) items.

GetSelectedCount 1.png

If the optional include_excluded parameter is set to True(), the returned count will include selected values which are currently being excluded by selections in other fields.  If it is False() or omitted, then these values will not be included in the count.  In the image below, the selection of Jam and Jelly in the Product Sub Group field has excluded Alcoholic Beverages and Baked Goods so they are now gray instead of green.  The GetSelectedCount function for the Product Group will now return 1.  If the include_excluded parameter is set to True(), then it will return 3, counting the excluded selections.

 

GetSelectedCount([Product Group]) = 1

GetSelectedCount([Product Group], True()) = 3

 

GetSelectedCount 2.png


GetAlternativeCount

getalternativecount(field_name)

This function returns the number of alternative (light gray) values in a field.

 

The GetAlternativeCount of the Region Name field below will return 3 for the light gray items in the field.

GetAlternativeCount.png

 

GetPossibleCount

getpossiblecount(field_name)

This function returns the number of possible values in the field.  If the field includes selections, then the selected items (green) are counted.  Otherwise associated (white) values are counted.

 

In the image below, the GetPossibleCount function for the Product Sub Group field will return 3 for the 3 associated (white) items in the list.

GetPossibleCount.png

If I were to select Muffins in the Product Sub Group field (see image below), then the GetPossibleCount function will return 1 for the selected item.

GetPossibleCount 1.png

 

GetExcludedCount

getexcludedcount(field_name)

This function returns the number of excluded (dark gray) values in the field.

 

In the image below, GetExcludedCount of the Product Line field would return 1 for Drink which is dark gray.

GetExcludedCount.png

 

GetCurrentSelections

getcurrentselections([record_sep [,tag_sep [,value_sep [,max_values]]]])

Unlike the other functions we have looked at so far, this function returns a string containing the current selections in an app.  The optional tag_sep parameter allows you to select the separator between the field name tag and the field values.  The default is a colon (:).  The optional value_sep parameter allows you to select the separator between the field values.  The default is a comma (,).  The optional max_values parameter allows you to set the maximum number of field values that can be individually listed.  The default is 6 and anything more than that is shown as 'x of y values'.

 

In the image below the GetCurrentSelections function will return the following:

 

Product Group: Beverages

Product Line: Drink

Product Sub Group: Juice, Soda

GetCurrentSelections.png

 

GetFieldSelections

getfieldselections(field_name [, value_sep [, max_values]])

This function also returns a string with the current selections for a field.  The optional value_sep parameter allows you to select the separator between the field values.  The default is a comma (,).  The optional max_values parameter allows you to set the maximum number of field values that can be individually listed.  The default is 6 and anything more than that is shown as 'x of y values'.

 

In the Product Sub Group filter pane above, Juice, Soda will be returned by the GetFieldSelections for the Product Sub Group field.

 

Field functions provide valuable information about selections in a field that can be used in an app to display information or help determine if another action needs to take place.  For instance, if more than one field value is selected, do something.  Try using a field function next time you are building an app to see how they work.

 

Thanks,

Jennell

The Drop script keyword is often used in scripts to drop a field or a table from the data model and from memory.  In this blog, I will discuss some of ways I use Drop Field and Drop Table in my script.  Drop Field can be used in the script to drop one or more fields from the data model.  The syntax is as follows:

 

To drop field A from all tables in the data model:

Drop Field A;

 

To drop field A from only table X:

Drop Field A From X;

 

To drop more than one field (A and B) from the data model:

Drop Fields A, B;

 

To drop more than one field (A and B) from multiple tables (X, Y):

Drop Fields A, B From X, Y;

 

I often use Drop Fields when I am creating a link table in my data model.  You can read more about link tables in Arturo’s Concatenate vs Link Table blog.  When I have more than one table linked by more than one field in my data model, I create a link table to eliminate the synthetic table and link the tables by just one field.  To do this, I first create a composite key in each of my tables.  Then I create a link table that stores the composite key and the fields that make up the composite key.  To avoid creating another synthetic table, I drop the fields that make up my composite key from the original tables.  Using some of Arturo’s example in his technical brief, here is an example of the script:

full script.png

In the script above, I used Drop Fields to drop the Year, EmployeeID and ProductID from the Sales and Budget tables.  Since these fields are now in the link table, I no longer needed them in the Sales and Budget tables.

 

Drop Table works in a similar way.  Drop table can be used in the script to drop one or more tables from the data model.  The syntax is as follows:

 

To drop table X:

Drop Table X;

 

To drop more than one table (X and Y):

Drop Tables X, Y;

 

Often in my script I create a new table based on an existing table.  When I do this, I frequently use Drop Table to delete the original table to prevent synthetic tables.  In the example script below, the Drop Table statement deletes the SalesTemp table and all its fields from the data model.  Therefore, the SalesTemp table can no longer be used in the script after the Drop Table statement.

script drop.png

Drop Field and Drop Table are useful scripting statements that help you create a data model that is free of synthetic tables and unnecessary fields and tables.  So the next time you find a synthetic table in your data model, check to make sure you did not forget to drop a field or a table.

 

Thanks,

Jennell

Jennell McIntire

NoConcatenate

Posted by Jennell McIntire Nov 18, 2016

Have you ever loaded two or more tables in your script to find that when the script was complete your data model had only one table?  I ran into this issue when I first starting scripting and I think this is probably a common scenario of new Qlik Sense and QlikView developers.  Qlik Sense and QlikView will automatically concatenate two tables if they have the same field names.  By using the NoConcatenate prefix, you can prevent this from happening.  Let’s look at an example.  When I reload the script below, I end up with one table named Table1 with records from both data sources – Sheet1 and Sheet2.

Script1.png

First Table1 is loaded.  When Table2 is loaded with the same fields, Qlik Sense automatically concatenated the data from Sheet2 to Table1.  If I had used the NoConcatenate prefix as seen below, I would end up with 3 tables in my data model: Table1, Table2 and $Syn 1 Table (a synthetic table).

Script2.png

Data Model Viewer after script above reloads:

TableViewer1.png

The synthetic table was created because my data model had two tables with the same field names so Qlik Sense created a synthetic key to handle this.  You can read more about synthetic keys here.  You should avoid synthetic tables when you can.  There are various ways you can create two tables without creating a synthetic table.  One is to use the Qualify statement – this will add the table name as a prefix to the field name.  Another way, which is more popular, is to rename the fields in the second table when you load them.  These two options will produce separate tables and no synthetic tables.

 

Now you may be wondering why you need to use NoConcatenate at all.  I often use it when I want to perform some additional manipulation to the data in an existing table.  For example, in the script snippet below, I am loading life expectancy data for various countries, genders and years.  Since I am using the CrossTable statement in the first Load statement, I cannot add a where clause to load only the 2014 data.  So, in the second Load statement, I perform a NoConcatenate Load to load all the data from DataTemp2 where year is 2014.  Then I delete DataTemp2 since I have the data I need in the DataTemp table.  The most important part of this script is the Drop Table at the end.  This deletes the first table I loaded (DataTemp2), preventing a synthetic table from being loaded.

Script3.png

This is often the way I use NoConcatenate - to create a new table from an existing table with some modifications and then I drop the original table.  NoConcatenate is useful and once you get the hang of it, you will find that NoConcatenate is a helpful prefix you will use often in your script.

 

Thanks,

Jennell

Jennell McIntire

FileName Function

Posted by Jennell McIntire Oct 21, 2016

Today I thought I would share how I used a combination of string functions and the FileName function to create data for my app.  I loaded several Excel files with one Load script and generated the data I needed for my app using the filename of the files.  Below is a subset of the files I was working with.  I had a separate Excel file for each cause of death and each gender.

files.png

Here is the script I used to load the Excel files:

script.png

I used a Crosstable Load to load the Excel files.  You can learn more about the Crosstable Load in Henric Cronström’s blog.  The Excel files include the country, the year and the number of people who died from the specified cause of death.  In my app, I also wanted to include gender and cause of death which was not included in the Excel file.  To do this, I decided to parse the gender and cause of death from the name of the Excel file.  Starting with gender, you can see in the files listed above that each filename ended with male or female.  In the script below, the Filename function returned the name of the Excel file including the extension but excluding the path (e.g. Death due to accidents - female.xls).  I use the SubStringCount function to determine if the filename included ‘- male’ or ‘- female.’  If it did, the function returned the number of occurrences which in this case would always be 1.  If an occurrence of ‘- male’ was found, then ‘Male’ was added to the Gender field.  If an occurrence of ‘- female’ was found, then ‘Female’ was added to the Gender field.

gender.png

The next bit of data I wanted to extract from the filename was the cause of death (see script below).  Each cause of death started at the 14th position/character so I used the Mid function to grab the text starting with the 14 character and I used the Index function to find the starting position of the hyphen so I could determine how many characters the Mid function needed to capture.  To figure out how many characters the Mid function should grab, I subtracted 15 from the position returned by the Index function (where the hyphen is located).  So if we look at the file named Death due to accidents - female.xls, the Mid function would start at the “a” in accidents and would grab 9 characters (the result of 25 – 14).  To finish it up, I used the Capitalize function to capitalize the first letter in each word of the cause of death.

cod.png

Since the files were all named and formatted the same way, I decided to minimize my script and use one Load statement to load all the files.  I could do this by using an asterisk (*) in my From clause like this:

from.png

This script loaded all xls files in the folder that start with “Death due to “.  Of course, I could have opted to load each file individually but why create more script to maintain.

 

Below is a sampling of the data I ended up with after loading the Excel files.  I can see the number of males and females (per 100,000 inhabitants) that died from an accident for each country in 2013.

table.png

In this blog, I reviewed a few helpful tips and functions that can be used in your script.  To recap, I used the FileName function to get the name of the file that I loaded and several string functions including SubStringCount, Mid, Index and Capitalize to generate the data for two new fields: Gender and Cause of Death.  I also discussed using the asterisk (*) in a single Load statement when loading multiple files that are named and formatted the same way.  This approach saved me time because it provided an easy way to create the data for the Gender and Cause of Death fields and there was less script to prepare.  Hopefully, you can make use of these functions in your app.

 

You can learn more about some of the string functions mentioned in this blog here.

 

Thanks,

Jennell

Jennell McIntire

Pivot Table Loading

Posted by Jennell McIntire Sep 23, 2016

With the release of Qlik Sense 3.1 this week came the Pivot Table Loading feature.  This feature provides the ability to “un-pivot” data that is stored in a pivot table format.  I am excited about this new feature because I often have data that I need to “un-pivot” in Qlik Sense.  I must admit that in the past I occasionally used QlikView to generate the script to load this type of data using the CrossTable wizard but now with the Pivot Table Loading feature, I can do it all in Qlik Sense.

 

Let’s take a look at an example.  Assume I have a data set that looks like the image below where the first column is country and I have data values for various years.

Excel.png

When I load this into Qlik Sense, the data is loaded just like the Excel file with a field named Country, 2013, 2014 and 2015 (see image below).  What I would prefer is to have a country field, a year field and then a field that stores the data values.  This is where the Pivot Table Loading comes into play.

preview1.png

In Qlik Sense, I can drag my Excel file into Qlik Sense to load it.  Once the file is loaded I can open the Data Manager and click on the edit icon for the table.

data manager.png

Below in the Data Manager window, there is now the Unpivot option.

data manager2.png

When Unpivot is clicked, you are prompted to select the fields that you want to transpose into rows.  Since I want to transpose the 2013, 2014 and 2015 fields into rows, I will select those three columns and select the Apply unpivoting button.  At the bottom of the screen, I am provided a preview of the data and I can see that my years are all in one field now.

data manager3.png

Once the unpivoting is applied, I can rename the attribute and data fields and load the data.

data manager4.png


Now if I preview the table I loaded, it looks like this:

preview2.png

The 2013, 2014 and 2015 fields have been transposed into rows and I now have a Year field that I can use in a filter pane or in my visualizations.  My script was auto-generated and you can see the use of the CrossTable prefix to load the Excel file.

script.png

The Pivot Table Loading feature is a valuable new feature in Qlik Sense 3.1 that will make it easier to load pivot table formatted data.  Check out Michael Tarallo’s video titled Qlik Sense 3.1 - Using Unpivot (video) to see the Pivot Table Loading feature in action.

 

Thanks,

Jennell

In Qlik Sense, alternate dimensions and measures are available providing users the option to change the dimensions and/or measures being used in a chart.  Alternate dimensions and measures are defined in advance and provide users with different views of the data all in one chart.  Alternate dimensions and measures can be added to a visualization in the Properties panel.  In the image below, there is a bar chart with Product Sub Group as the dimension and Sales $ as the measure.  There are also two Alternative dimensions: Product Group and Product.  Alternate dimensions and measures can be added by clicking the Add alternative button and selecting or entering the new dimension or measure.

bar chart.png

By default, the bar chart above shows the Sales $ by the main dimension for the chart, Product Sub Group.  Since there are alternative dimensions set up, the user can also view the Sales $ by Product Group or Product by simply clicking the arrow next to the dimension name on the y-axis and selecting either Product Group or Product.

dimension switch.png

In Qlik Sense 3.0, the ability to change the dimension from the axis was added.  Users can also change the dimension or measure by opening the Exploration Menu and changing the dimension or measure.

dimension switch panel.png

Adding alternate measures is just as easy.  In the image below, two alternate measures were added to the Margin Amount Over Time line chart: Sales $ and Budget $.

line chart.png

A few things to be aware of when using alternate dimensions and measures:

 

  • In bar, line and combo charts, the title needs to be visible on the axis of the alternate dimension/measure so the user can see the arrow to click on to change the dimension/measure.  If you prefer not to show the title, then the user can still change the dimension/measure by going into the Exploration Menu.
  • Alternate dimensions and measures can be added to all chart types but they can only be changed in the visualization (via the axis) in bar, line and combo charts.  In other chart, they can be changed in the Exploration Menu.

 

If you are familiar with QlikView, you may find alternate dimensions and measures to be similar to the cyclic group that is available in QlikView.  They are both great features because it allows the user to have one visualization with numerous views.  This is ideal in keeping your sheets uncluttered.  Instead of having the same visualization repeated using different dimensions or measures, you can now have one chart with alternate dimensions and/or measures.

 

Thanks,

Jennell

Jennell McIntire

Visual Search

Posted by Jennell McIntire Jul 29, 2016

Qlik Sense 3.0 has expanded the capabilities of Smart Search by including visual results when you perform a search in your Qlik Sense app.  Not only can you search for field and dimension values in your data model, you can now search chart types, chart names (titles, subtitles and footnotes), dimension and measure definitions and labels.

 

Let’s use the Consumer Goods Sales demo to see some of the ways the Visual Search works.  To begin a search you simply click the Search icon in the upper right corner of the sheet.  Say I want to find all maps in my app.  I can search for map and the results will show me the map objects in my app.  I can click on the map to view it on the sheet.

 

map.png

 

My map search did not return any results (in the Apply a selection section) that I could make selections on so let’s do another search to see where I have used the Product dimension in my app.  In the image below you can see that this search returns not only visualizations but data fields that I can make selections on.  I can navigate through the visualizations returned by my search by using the arrow on the right.  At the bottom in the Apply a selection section, I can make a selection in the search results to filter the data in my app.

 

product.png

Under the charts in the Explore section there is an info icon.  Clicking this icon will show where my search string was found in the chart.  For instance in the first chart, the Product dimension was used in the chart.  In the third chart, product was found in the title, dimension name and data value.

 

3 charts.png

Above you can see how we can search for a dimension.  The same can be done for a measure but note that the search does not return measure values.  I really like the ability to search measures because now I can quickly check to see if I am using a specific field in one of my measures without manually checking each measure.  In this example, I want to see where I am using the Sales Amount field in my app.  If I search for “Sales Amount” with double quotes, I can find all the charts that use this field in the measure.

 

Sales Amount.png

 

In this example, I used double quotes because I knew the field was named Sales Amount.  If I did not use the quotes, the search would have searched for the words Sales and Amount separately.  Arturo Munoz created a Qlik Sense Search Cheat Sheet that has some helpful tips on performing searches.

 

The new Visual Search in Qlik Sense 3.0 expands the search capabilities that can be performed in an app by searching objects and master items as well as field values making it easier to find what you are looking for and to filter your data.  Michael Tarallo created a brief video on this new feature – check it out!

 

Thanks,

Jennell

In this blog, I am going to discuss some things you should do before you publish and/or share your Qlik Sense app with others.  These tips are steps that can be taken to improve the user experience whether they are simply viewing the app or making edits to the app.

 

  • The first set of tips is around Descriptions – provide descriptions wherever you can to help users understand what they are viewing.
    • Add an app description to your app.  This provides an overview of what the app is all about.
    • Add sheet descriptions to your app.  Offer a sentence or two about the sheet explaining the analysis that can be done on that sheet.
    • Add comments to your script, if necessary, to make it easier for you or someone else who may update or edit it in the future.
  • Jazz it up a bit.  Add an app thumbnail and sheet thumbnails to your app to improve the look of the app from the App Overview page.  Which App Overview is more appealing to you?  This one:

     ExecDashOverview.png

          Or this one?

     ExecDashOverview-pplain.png

  • Organize your app so that it flows nicely.  Make sure your sheets and the objects on the sheets are in a logical order.  Keep sheet objects to a few and do not clutter the sheet – less is more.
  • Be Consistent throughout the app.  Here are some examples of what I mean:
    • Have a consistent layout - If you have filters on your sheets, place them in the same area on all sheets so the user knows where to expect them.
    • Use color consistently – The Consumer Goods Sales demo does this nicely on the KPI Dashboard sheet by coloring all the margin charts in blue, the sales charts in gray and the budget charts in green.  This sheet uses coloring to show what objects go together making it more intuitive for the user and guiding the user on how you would like them to read the charts on this sheet.

          ConsumerGoods.png

    • Align objects on the sheet.  With the grid, Qlik Sense does a good job at making it easy for users to keep objects aligned, organized and neat.
  • Create Master Items to make your development easier as well as the development of any future editors of the app.  Master Items provide one location for dimensions and measures that may need to be reused in the app.  Add a quick description to measures that are a little more complicated.
  • Add a Story to your app if there are some findings you would like to share with others or if you would like to document how you want users to use the app.
  • Make sure the Data is right.  Before sharing your app, just confirm that your data is accurate.
  • Test on Smaller Devices if there is the chance that users may access your app from a tablet or mobile phone.  Remember that with responsive design, objects will be organized from top to bottom, left to right.  So if the sheet from Consumer Goods Sales (shown above) was viewed on a mobile device, the charts would be viewed in this order: margin KPI, margin pie chart, margin line chart, sales KPI, sales pie chart and so on.  Michael Anthony wrote a blog on this topic that you may find useful.

 

Presented here are a few tips that can be taken to polish your Qlik Sense app before you share it with others.  These tips take a few minutes to implement and can improve the user experience thus their understanding of the app.  You should also check out Michael Anthony’s blog on UX Best Practices for Data Analysis.  It provides a detailed overview of some topics I discussed such as color, mobile design, layout and so much more.

 

Thanks,

Jennell

Jennell McIntire

String Operators

Posted by Jennell McIntire Jun 3, 2016

There are two string operators that can be used in Qlik Sense and QlikView.  They are & (ampersand) and like.  While I use the ampersand all the time, I have never used like before but I will start after learning how easy it is to use.  The & operator is used to concatenate two strings.  I often use this when I want to combine text and the results of a calculation in a chart title or Text and Image object.  For example in the bar chart below from the Executive Dashboard demo, this expression is used for the title:

 

'Total Revenue by Product Group = ' & num(Sum([Sales Quantity]*[Sales Price]), '$#,##0')

chart.png

The expression uses the ampersand to concatenate the string 'Total Revenue by Product Group = ' and the results of the total revenue calculation: num(Sum([Sales Quantity]*[Sales Price]), '$#,##0') into one string.  It will place the strings right after one another so do not forget to add spacing in between your strings if necessary.

 

The like operator has another purpose.  It compares two strings using wildcard characters and returns the Boolean value of True if the string before the operator matches the string after the operator.  The two wildcard characters that can be used in the string after the operator are * and ?.  The * represents any number of characters while the ? represents only one character.  Take a look at how this works in the examples below.

 

  • ‘Qlik’ like ‘Q?ik’ will return True (-1) - the ? is a wildcard character for the ‘l’
  • 'Qlik' like 'Q*' will return True (-1) - the * is the wildcard character for ‘lik’
  • 'Qlik' like 'Q?k' will return False (0) – the ? is for a single character therefore it cannot represent the ‘l’ and the ‘i’
  • 'Qlik' like 'Q??k' will return True (-1) – the first ? is the wildcard character for the ‘l’ and the second ? is the wildcard character for the ‘I’

 

The like operator can be used when you need to compare two strings that may vary slightly.  Assume you have a full list of products that look like this:

filter2.png

 

The like operator can be used to display products that start with ‘Product’ and end with the number 1.

filter.png

The expression below could also have been used returning all products that end in 1.

 

If(ProductName like '*1', ProductName)

 

Ampersand and like are string operators that can be used in charts and in the script to concatenate or compare strings.  They are both binary operators meaning they take two operands.  When using the & operator, each string on either side of the & is an operand.  The same applies to the like operator with the operands being on each side of the like operator.  Happy Qliking!

 

Thanks,

Jennell

The Lookup function is a script function that allows you to look up and return the first occurrence of a value in a field that has already been loaded in the script.  The lookup can occur in the current table or a previously loaded table.  Here is the syntax:

 

lookup(field_name, match_field_name, match_field_value [, table_name])

 

The first parameter field_name is the field from which the returned value will come from. The field_name must be entered as a string so enclose the field name in single quotes.

 

The second parameter match_field_name is the field where you will be looking up the value.  This parameter also needs to be entered as a string.  This parameter and the first parameter, field_name, must be fields in the same table in order for the Lookup function to work.

 

The third parameter match_field_value is the value that you are looking for in the match_field_name field.

 

The fourth and last parameter is the table_name.  This is an optional parameter.  If the lookup is occurring in the current table that is being loaded, then this parameter can be omitted.  If the lookup is in another previously loaded table, then this parameter should be the table name enclosed in single quotes.

 

So, let’s take a look at a small example of the Lookup function.  In the script below, the first two inline load scripts load a ProductData and a CustomerData table.  The loading of the Temp table is where we can see the Lookup function in action.  In this example, I am looking up the customer name with a specified customer ID.  The Lookup function will return the value of the Customer field (first occurrence) from the ProductData table where the loaded CustomerID matches the value in the CustomerID field in the ProductData table.

script.png

Once this script is run, the Temp table looks like this:

table.png

The field CustomerName has the customer name that corresponded to the first occurrence of the CustomerID being loaded in the Temp table.  This value was captured by looking it up in the ProductData table.

 

If the Lookup function does not find a match, null will be returned.  The Lookup function is one line of code that is fairly easy to add to your script to look up a value in a field but it has some limitations.  First, the order of the search is the load order.  You are not able to sort the data so the first occurrence will be based on the load order of the value.  Second, the Lookup function is not as fast as the ApplyMap function.  While the Lookup function is flexible and easy to use once you know the parameters, ApplyMap should be your first choice when you need to look up a value based on the content of a field.  You can read more about the ApplyMap function in the blogs listed below:

 

Mapping … and not the geographical kind

Don't join - use Applymap instead

 

Thanks,

Jennell

Today I decided to blog about the Autonumber function that can be used to create a “compact memory representation of a complex key.”  Having recently learned about this function, I realized there have been times in the past when this would have been helpful to use.  For instance, when I need to build a link table in my data model, I often create keys that I use to link the tables.  Sometime these key fields are lengthy and are a combination of 3 or 4 fields.  In this blog, I will show you how you can use the Autonumber function to create a “compact memory representation of a complex key.”

 

Assume I load a data set that looks like this:

Data to load.png

And I want to load another data set that looks like this:

Data to load2.png

These two data sets have the same first four fields so if I were to load them as is, I would get a synthetic table in my data model.  To avoid that I will set up a key field in each of the tables that includes the FoodCategory, StoreNo, Year and Month fields.  This key field will be the field that links these two tables.  I will do this using a preceding load when I load both of these tables.  The script would look like this:

script.png

In the first table, I am using a preceding load to load all fields and then I am using the Autonumber function to create a key field that represents the four fields: FoodCategory, StoreNo, Year and Month.  I am doing the same thing in the second table I am loading but the difference here is that I am not loading the key fields.  By not loading the key fields, I am preventing a synthetic table from being loaded.  The end result looks like this:

table.png

Notice the FSYMkey field.  In this example, it is a unique integer that represents a larger expression.  In the past, I would have created the key field like this (see the FSYMkey2 field in the table below):

table2.png

FSYMkey2 is a more complex field that would have taken up more memory.  This example is small but if you had thousands of unique key fields like this, the consumed memory would add up.  By using the Autonumber function, I was able to use an integer to represent a long string thus minimizing the memory usage in my app.  This is one of many tricks that can be used to reduce the memory usage in your app.  Henric Cronstrom has some other ideas in his Symbol Tables and Bit-Stuffed Pointers blog.  Check it out.

 

 

Thanks,

Jennell

Filter Blog

By date:
By tag: