1 2 3 4 Previous Next

Qlik Design Blog

55 Posts authored by: Jennell McIntire
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

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

Filter Blog

By date:
By tag: