1 2 3 4 Previous Next

Qlik Design Blog

58 Posts authored by: Jennell McIntire
Jennell McIntire

Qlik Sense Histogram

Posted by Jennell McIntire Jan 12, 2018

What is a histogram?  A histogram is a chart that allows you to visualize the distribution of data over a continuous interval or certain period.  It is made up of bars like a bar chart but instead of displaying actual counts against a dimension, a histogram displays the frequency at each interval or bin allowing you to see where the values are concentrated and where there are gaps or odd values.  A histogram is also different from a bar chart in that it does not need measures.  Only a single numeric dimension is needed to create a histogram.  Once the dimension is added to the Qlik Sense histogram chart, the frequency is automatically calculated.

 

Let’s look at an example, in the histogram below, the dimension BMI (body mass index) is used to view the frequency.  After BMI was added to the chart, Qlik Sense automatically created the BMI bins based on the frequency distribution.  This chart shows that BMI is concentrated in the range 21 <= x < 22.75 (21 to 22.75, but not including 22.75).

histogram.png

The Qlik Sense histogram chart has some properties that can be used to customize the histogram.  There is the ability to set the number of bars to a maximum number or set the width of the bars if you would like the bars to be even intervals.  Qlik Sense Help has an example of a histogram using even intervals to show temperature.

num of bars.png               bar width.png

 

In the histogram above, the number of bars is set to 10.  When the bars are set to Auto, Sturges’ formula is used to determine the bins and this may change based on selections.

 

A histogram chart is ideal for a large amount of data when you need a quick visualization of frequency.  It is easy to create since only one dimension is required.  The key to remember is that the dimension field must be numeric.  Try it out with your numeric dimensions in your next Qlik Sense app.  If you are using QlikView, you may be interested in Henric Cronström’s blog Recipe for a Histogram on how to create a histogram in QlikView.  Also, check out Arturo Munoz’s blogs on the Distribution Plot chart and the Box Plot chart.  You may also find these helpful in your next Qlik Sense app.

 

Thanks,

Jennell

Today I thought I would share how I used the system functions OSUser() and DocumentName() in a Qlik Sense app.  Here is some background on the app - the app resides on a server and users must log in to access it.  Users can have multiple copies of the app and each file name is unique.  The app allows users to store the results of their analysis in QVDs and I wanted the QVDs to include the QVF name/id as well as the logged in user name to ensure that there is one set of QVDs per app.  To do this, I used the OSUser() and DocumentName() functions.


Let’s learn a little more about these functions.  According to Qlik Sense Help, OSUser() returns the name of the user currently connected and DocumentName() returns the name of the current Qlik Sense app.  In the script below, I created variables that store the results of these functions.

script.png

OSUser() returns a string formatted like this:

UserDirectory=xxx;UserId=xxx

 

This is what I stored in the vFullUser variable.  Then in the vUser variable, I used the Right, Len and Index functions to capture just the UserId portion of the string.  DocumentName() returns the app ID for the QVF.  I use the vUser and vAppName variables later in the script when I am storing the data into QVDs.  Below is an example of what that script looks like.  I append the user ID and the app ID to the name of the QVD.

store.png

Qlik Sense offers many system functions that can be used in both the script and chart expressions to provide system, device and Qlik Sense app properties.  Here is a list of the available system functions:

 

AuthorDocumentTitleIsPartialReload
ClientPlatformEngineVersionOSUser
ComputerNameGetCollationLocaleProductVersion
DocumentNameGetObjectFieldReloadTime
DocumentPathGetRegistryStringStateName

 

You can learn more about the system functions available in Qlik Sense Help.  The OSUser() and DocumentName() functions are easy to use and capture the information I need to make the QVDs specific to a user and an app.  Keep these functions in mind the next time you need information about a Qlik Sense app or the system/device it is running on.

 

Thanks,

Jennell

Jennell McIntire

Do..Loop

Posted by Jennell McIntire Nov 17, 2017

The do..loop control statement is used in the script to iterate through one or more statements until a condition is met.  It is useful when you need to dynamically check a condition while executing statements in the script.  There are two ways the do..loop can be used.  The first is to check the condition before executing statement(s) in the loop and the second is to check the condition after the statement(s) in the loop have been executed.  Let’s look at some examples.

 

The script below checks to see if the condition has been met before the statements are executed.  When x is no longer less than 10, the do..loop will stop.

Do while.png

The script below is a little different.  In this script, the condition is checked after the statements are executed so the statements will always run at least one time.

Do loop while.png

Both scripts produce the same results as seen below.  Products 1 through 9 were created.

Do while table.png

The do..loop also can be used with the “Until” keyword instead of the “While” keyword producing different results.  In the script below, the statements will be executed until x is less than 10.  Since x is equal to 1 which is less than 10, the statements never run so no products are generated.

Do until.png

Now if the condition is checked after the statements are run, as shown in the script below, one product will be created since the statements run once before the condition is checked.

Do loop until.pngDo until table.png

Now what if you need to exit the do..loop.  You can do this using Exit do.  The loop will be exited and execution will continue with the statement(s) following the loop.  Here is an example of what this looks like.  In the script below, the loop will exit after the Load statement runs 3 times, stopping when x is equal to 3.

exit do when.png

In the script below, this loop will exit after the Load statement executes one time.  Since x is not equal to 3, the loop will be exited when execution gets to the exit do statement.

exit do unless.png

The do..loop is helpful in the script when you need to execute statements while /until a condition is met.   It provides flexibility since the condition can be dynamic and be evaluated before or after the statement(s) in the loop.  This is a little different from the For..Next loop where a counter is used to determine the number of times the statement(s) in the loop are executed.  Learn more about loops in Henric’s blog Loops in the Script.

 

Thanks,

Jennell

Jennell McIntire

Dimension Colors

Posted by Jennell McIntire Oct 20, 2017

A few months ago, I wrote a blog about setting colors in master dimensions and measures.  Today I am going to share a little more about how master dimensions can be set to a single color or multiple colors.  Let’s first talk about setting a dimension to a single color.  This can be helpful when you want to use a color other than the Qlik Sense default color in your charts.  For instance, a bar chart may look like this with the default blue color.

default blue.png

But if your app is using a different color theme, you may want all the bars to be purple for instance.  You can do this by setting the Dimension color on the Edit dimension tab of the master dimension to the color you would like the dimension to be.  This color will be the same for all dimension values which is good practice when there are many dimension values.

edit dimension small.pngpurple small.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Now let's discuss setting a master dimension to multiple colors.  Sometimes you may need the color for each dimension value to be different.  This is when you can use the Value colors tab of the master dimension to set the colors of each dimension value.  The stacked bar chart below is a good example of when you may want the dimension values to have different colors.  This chart is looking at the calls by activity type and call type.  The call type dimension values are set to 3 different shades of blue so that they can be distinguished in the stacked bar chart.  If the color values were not set, then the standard blue, red and yellow colors would be used instead.

 

stacked small.png

The color values can be set on the Value colors tab of the master dimension (see image below).  A color scheme can be used to set the colors or each value can be set manually.  The call type field only has 3 values so it is a good candidate for value colors.  You want to avoid using values colors or coloring by dimension if there are several different dimension values.  Too many different colors (or not so different colors) can become confusing and indistinguishable to the user.

value colors.png

 

On the Value colors tab, there is also the option to select a color for Others, if you are adding any limitations to the dimension, or for Null values.  When using the value colors to color your dimensions, be sure to select color by dimension and enable library color in the properties of the chart.  Check out this video in Qlik Sense Help for more information.

 

These color features available in dimension master items provide one place to set and edit the dimension colors used in charts.  You can set it once and use it in multiple charts.  This promotes consistency in our apps thus making them easier to digest and understand.

 

Thanks,

Jennell

Jennell McIntire

FileSize Function

Posted by Jennell McIntire Sep 22, 2017

In the past, I have blogged about the FileName function and how I used it to generate data for my Qlik Sense app.  Today, I thought I would blog about another file function, the FileSize function.  The FileSize function is a script function that can be used to return the size of a file or table.  The FileSize function returns an integer for the size, in bytes, of the file specified or a table.  The file can be a QVD, a text file, an Excel file or a table.  The syntax is quite simple:

 

Syntax:

FileSize([filename])

 

I have used the FileSize function to determine if a QVD exists before I load it.  In the example script below, I set the size of the CustomerMaster QVD to the vSize variable.  Then I check the variable to see if a size was returned.  If a size was returned meaning a QVD is there, I load the QVD.  If the QVD does not exist then the FileSize function will return null.

script.png

The FileSize function can also be used to return the table size of the table file being read.  To do this, the FileSize function is used in the Load statement as seen in the example script below.  When the filename parameter is excluded, then the FileSize function will return the size of the table currently being read.  The TableSize field will store the size of the CustomerMaster table after it has been loaded.

script2.png

The FileSize function is another easy-to-use script file function that can be used in Qlik Sense or QlikView to provide the size of a file or a table.  Use the FileSize function as a checkpoint in your script to ensure the file is available before loading, as I did in the example above, or use it ensure size requirements for files being loaded are being met in your app.  Good luck scripting!

 

Thanks,

Jennell

Qlik GeoAnalytics is known for its powerful mapping capabilities.  I had the opportunity to dig down into the layers of Qlik GeoAnalytics when working on a project about Indian Migration.  In this project, the team wanted to illustrate which states in India migrants moved to and left from.  The plan was to show this on a map.  Here were the requirements:

 

  • Use color to show the selected state (the state users move to or from)
  • Use color to show the number of migrants who moved
  • Use arrows to show the direction of the move
  • Place dots on each state that migrants move to or from

 

Here is a look at one of the resulting maps.  In order to build it, we used 3 GeoAnalytic layers: the Area layer, the Bubble layer and the Line layer.

Map.png

In the screenshot above, the selected state is Andhra Pradesh and it is highlighted in orange.  All the states that migrants came from are colored in a shade of green using the ColorMix1 function.  Darker shades of green indicate a larger number of migrants than the lighter shades of green.

 

Before we can begin to add the layers, we first had to add the GeoAnalytics Map to the app.  Once this is done, the layers can be added.  The first step was to use the Area layer to show which states users left from.  The color expression (which you can see in the attached app) first checked for the selected state and colored it orange, then it checked for states where no one migrated from and colored them gray.  The last part was to use the ColorMix1 function to color the states in shades of green based on the number of people who migrated from that state.  The second step was to add dots or bubbles to the state.  To do this the Bubble layer was used.  The third step was to add the Line layer to illustrate the direction of the move.  In the screenshot above, the arrows are all leading to the selected state, Andhra Pradesh.  The width and size of the arrows are based on the number of migrants.  The final few steps included using the Draw Order Adjustment property to indicate the order that we wanted the layers drawn – first the Area layer, then the Bubble layer and last the Line layer.  We also made use of the Info Bubble so that we could provide informative data when users hover over an area, line or bubble.

 

Feel free to check out the attached app using Qlik Sense to see how all the pieces were put together.  If you do not have Qlik Sense, you can download and try Qlik Sense Desktop for free from here.  In this project, we only used 3 of the layers available with Qlik GeoAnalytics.  There are other layers available as well as advanced geospatial information and geographic relationships.  Learn more about Qlik GeoAnalytics and check out our Getting Started Videos.

 

Thanks,

Jennell

One of the new enhancements in the June 2017 release of Qlik Sense is the ability to define colors in dimensions and measures in the master items library.  When creating a dimension or measure in master items, users now can specify a color that will be used for the dimension or measure.  This makes it easier for users to be consistent in their color choices throughout their app.  Every time the master item is used, the specified color will also be used.

 

I found this new feature helpful when building out visualizations for an app.  The design required that measures that showed the actual number be a rusty color while standard measures appeared as gray.  I could do this by selecting the color I wanted to use for each measure when I created the master item.  Let’s take a closer look at how I added the coloring in the objects below.

 

charts.png

 

The 3 KPIs on the left are measures.  In each one, I specified the color when I created the measure.  In the image below, you can see the new color option when you create/edit a dimension or measure.

edit measure.png

 

When I use the measure in a KPI, the color is automatically set since I specified it in the master item.  In the Cost Variance KPI at the top, I wanted to add conditional coloring instead of the color I specified in the measure so I selected the option to not use the library in this object as seen below.  This flexibility allowed me to take advantage of the conditional coloring when needed.  Note: The ability to turn off “Use library” is not available in all objects.

 

Color.png

 

In the combo chart, the bars and lines were colored based on the measure color giving me the ability to color the bars and line in the chart exactly as the design required.  While this may seem small, it is a huge enhancement for users and expands the design capabilities of Qlik Sense.  The color palette also provides several different color options so apps can be branded as needed.  To learn what else is new in Qlik Sense June 2017, check out Michael Tarallo’s blog.

 

Thanks,

Jennell

Jennell McIntire

Rank Function

Posted by Jennell McIntire Jun 30, 2017

Over the years, I have seen the Rank function used in various expressions.  Recently, I was working on a Broadway Shows project where we wanted to show the rank in a bar chart before the label adding a numeric indicator of how a show ranked.  You can see what I am referring to in the bar charts used in the Broadway app.  The bar chart below is an example of one of the charts.  We added the rank before the show name on the y-axis label.

Broadway.png

In this blog, I will discuss how the Rank arguments can be used to change how the rank is presented.  To do so, I will use a simplified data set to illustrate how the rank can be displayed.  Let’s start by defining the Rank function.  The Rank function is a chart function and is defined in Qlik Sense Help as:

 

Rank() evaluates the rows of the chart in the expression, and for each row, displays the relative position of the value of the dimension evaluated in the expression. When evaluating the expression, the function compares the result with the result of the other rows containing the current column segment and returns the ranking of the current row within the segment.

 

For charts other than tables, the current column segment is defined as it appears in the chart's straight table equivalent.

 

Syntax:

Rank([TOTAL] expr[, mode[, fmt]])

 

Originally, I used the Rank function without any arguments.  This will rank the products by their sales and display it before the product label.

Rank no arguments.png

Rank no arguments chart.png

By default, with no arguments for mode and fmt, the Rank function will show the lowest and highest value displayed like a range as seen in the chart above.  But what if you want to show a single number for the rank where Product_B and Product_J would show 1 as the rank for both projects since they both tied for first place.  This can be accomplished by using the mode and fmt arguments.  Mode can take values 0 – 4 and controls which rank is shown for each row for example show the lowest rank, the average rank or the highest rank.  The fmt argument takes values 0 – 2 and controls how and where the rank is displayed.  You can find the detailed description for each setting in Qlik Sense Help.

 

For the Broadway app, I set the mode to 4 and the fmt to 1.  If I do this with this data set, I will get a chart like this:

Rank arguments.png

Rank arguments chart.png

The mode argument is 4. This will show the lowest rank on the first row, then increment by one for each row.  The fmt argument is 1 and this will show the low value on all rows.  When there is a tie, only the lowest rank will be displayed on the respective rows.

 

There are not always ties when using the Rank function but when there are, the Rank function provides options for how to handle the display of the rank.  This was something I never had a need for until now but I found it to be very valuable so I thought I would share what I have learned.  The next time you use the Rank function, try out the various arguments to find the display that works best for your app.

 

Thanks,

Jennell

The switch control statement is one of many controls statement that can used to determine the flow of the script.  In Qlik Sense help, it is defined as:

 

The switch control statement is a script selection construct forcing the script execution to follow different paths, depending on the value of an expression.

 

Syntax:

Switch expression {case valuelist [ statements ]} [default statements] end switch


The switch control statement can be used in the script to identify a specific execution path.  With the switch control statement, several paths can be defined and a default path can be defined as well when no match is found in the case clause.  The various script paths cannot cross – they should be individual paths that do not overlap.  In the script below, the expression x will be compared to each case.  When there is a match, the script for that case will be executed.

 

In the example below, Case 1 will be executed – the variable y will be set to Sunday and one record will be generated in the table.

script.png

Below is a look at the record generated.

Table.png

It is also possible to have more than one value for a case, for instance, you can use the script below to run the same script if x matches 1, 2 or 3.

case 1 2 3.png

When faced with various script execution paths, try using the switch control statement.  While I rarely have a need to use it, I like how neat and straight-forward the syntax is.  I have attached an example Qlik Sense app with the full script for you to test out for yourself.

 

Thanks,

Jennell

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

Filter Blog

By date:
By tag: