1 2 3 Previous Next

Qlik Design Blog

67 Posts authored by: Jennell McIntire
Jennell McIntire

Floor It!

Posted by Jennell McIntire Oct 5, 2018

Have you ever tried to compare two dates that looked the same to find out that in fact they were different?  I was recently approached by a colleague who had this problem.  In their script, they were adding a flag when the date in a field was also the last day of the month.  Both dates were formatted to show the month, day and year (M/D/YYYY) but the flag was never true even when the dates appeared to be the same.  When working with dates, you may find that although two dates are formatted the same, the underlying values may be different.  To troubleshoot this, we used the Num function to get the numeric value of the two dates we were comparing in the script to see if the two dates were the same numerically.  Take a look at the table below for an example of how we resolved the issue.

 

StepsDate 1Date 2Notes
Start with these dates2018-10-05 05:16:5010/5/2018
Format Date 1 to display the date like Date 2 is formatted

Date('2018-10-05 05:16:50', 'M/D/YYYY')

>>

10/5/2018

10/5/2018
Is Date 1 = Date 2?10/5/201810/5/2018No, not equal
Use Num() to see numeric value of dates

Num(‘2018-10-05 05:16:50’)

>>

43378.220023148

Num(10/5/2018)

>>

43378

The numerical values of Date 1 and Date 2 are not the same
Use Floor to round Date 1 down to just the date

Num(Floor(‘2018-10-05 05:16:50’))

>>

43378

Num(10/5/2018)

>>

43378

Using Floor, is Date 1 = Date 2?

Num(Floor(‘2018-10-05 05:16:50’))

>>

43378

Num(10/5/2018)

>>

43378

Yes, they are equal

 

So let’s explain what is going on here.  We started with 2 dates – one that had a timestamp and one that did not.  After formatting the dates the same, it was determined that the dates were not equal.  This is because the underlying numeric value of Date 1 still included the time even though the time was not visible after it was formatted as M/D/YYYY.  The numeric value of 2018-10-05 05:16:50 is 43378.220023148 while the numeric value of 10/5/2018 is 43378.  When looking at the numeric values, the value before the decimal point represents the date and the value after the decimal point represents the time.  To handle this, the Floor function was used.  According to Qlik Sense Help,

 

Floor() rounds down a number to the nearest multiple of the step shifted by the offset number.

Compare with the ceil function, which rounds input numbers up.

Syntax: 

Floor(x[, step[, offset]])

 

Once the floor function was used, Date 1 was rounded down to just the date, giving it a numeric value of 43378 - the same as Date 2.

 

It is helpful to remember that dates have numeric values.  We often see dates formatted to meet our needs which is great but when we need to compare dates, we need to look beyond the displayed date and look at the numeric date.  The Date function controls how the date is displayed but it does not change the underlying value of the date.  In this example, the Floor function rounded the timestamp down to just the date.  The Ceil function works similarly except it rounds up.  You can also check out Henric Cronstrom’s blog titled Why don’t my dates work? for other date related issues you may stumble upon.  I hope you find this blog helpful and that it helps you quickly troubleshoot date comparison issues should they arise.

 

Thanks,

Jennell

Jennell McIntire

Table Functions

Posted by Jennell McIntire Sep 7, 2018

Today, I am going to blog about Table functions.  I came across these functions when I was exploring the various ways I can determine if a table exists in the data model.  The table functions I will review today include:

 

  • FieldName
  • FieldNumber
  • NoOfFields
  • NoOfRows
  • NoOfTables
  • TableName
  • TableNumber

 

These functions provide information about the current table being loaded or specified in the function.  All of these functions should be used in the script but the NoOfRows function can also be used in a chart expression.  Let’s take a closer look at these functions but first let’s load a table that we can work with.

Table script.png

Now load the script to illustrate the functions:

Function script.png

Here are the results of the Temp table loaded above:

Results.png

While the function names give you a good idea on what they are used for, let’s take a more detailed look at each one of them.

 

FieldName

The FieldName function takes two parameters, field number and table name, and returns the name of the respective field.  Based on the script above, the FieldName function will return Name since it is field 1 in the Table1 table.  Note, field numbers start with 1, not 0.

 

FieldNumber

The FieldNumber function takes two parameters, field name and table name, and returns the number of the specified field in the table.  In this case, Gender has a field number of 2 in Table1 so 2 is returned by the FieldNumber function.

 

NoOfFields

The NoOfFields function takes one parameter, table name, and returns the number of fields in the table that was specified.  The table name parameter must be for a table that is already loaded in the data model.  It cannot be for the table that is currently being loaded.  For example, in the script above, the table Temp could not be used in the NoOfFields function since that is the table being loaded.

 

NoOfRows

The NoOfRows function also takes one parameter, table name, and returns the number of rows in the specified table.  Like the NoOfFields function, this function must be used for a table that was previously loaded.

 

NoOfTables

The NoOfTables function does not take any parameters and returns the number of tables that have previously been loaded in the data model.  Based on the script above, the NoOfTables function will return 1 for the Table1 table.  Note that it does not include the Temp table that is being loaded when this function executes.

 

TableName

The TableName function takes one parameter, table number, and returns the name of the table with the respective table number.

 

TableNumber

The TableNumber function takes one parameter, table name, and returns the number of the table with the respective table name.  Note that the table number starts with 0 and not 1 so TableNumber(Table1) will return 0 since Table1 was the first table loaded.

 

Hopefully, you have learned something new and found this useful.  Table functions provide information about the tables and fields that have been loaded in the data model.  If you would like more information, check out the Qlik Help site.

 

Thanks,

Jennell

Jennell McIntire

Error Variables

Posted by Jennell McIntire Aug 10, 2018

Today I am going to blog about Error Variables and how they can be used in an app.  Error variables are available in Qlik Sense and QlikView to:

 

  1. Determine what actions should be taken if an error occurs during script execution
  2. Provide information about error(s) that occur during script execution

 

The four error variables I will review in this blog are:

 

  1. ErrorMode
  2. ScriptError
  3. ScriptErrorCount
  4. ScriptErrorList

 

Of the 4 error variables, ErrorMode is the only variable that is set by the user.  This variable determines what should happen if an error occurs during script execution.  This variable is set in the script like this:

errormode.png

ErrorMode can take one of three values: 0, 1 or 2.  By default, ErrorMode is set to 1.  This means that if there is an error during script execution, the script will stop and prompt the user for an action.  When ErrorMode is set to 0, the error will be ignored and the script execution will continue.  When ErrorMode is set to 2, the script will fail and stop.  The ErrorMode variable should be set at the beginning of the script or before a section of the script where you know there may be errors that you would like to handle.  Once the ErrorMode is set, it will remain the same unless the ErrorMode variable is reset/changed later in the script.  For instance, you may opt to set the ErrorMode variable to 0 if there is an area in your script that may throw errors that you would like to ignore (not stop script execution).  In this case, you may need to set ErrorMode back to 1 if you want to be notified of other errors later in the script.

 

Let’s see how the error messages are presented if I run the simple script below to load a group of Excel files that start with “Book.”  Note, that all the files loaded do not include the “Monthly Sales” field.

Load script.png

If I precede this script with Set ErrorMode = 0, this is what I see when the script is complete.

0.png

Notice that the script finished executing even though there was an error.  Now let’s see what happens if we keep the default (ErrorMode = 1) or set ErrorMode = 2.

2.png

Here the script execution stopped at the error and did not complete providing the option to Close, correct the script and reload again.

 

Unlike the ErrorMode variable, the ScriptError, ScriptErrorCount and ScriptErrorList variables are output from Qlik Sense or QlikView that provide information about the error that was encountered when the script was executing.  The ScriptError variable will return an error code.  A list of the error codes and their descriptions can be found here in Qlik Sense Help.  ScriptErrorCount returns the number of statements that caused an error during script execution.  Lastly, the ScriptErrorList variable returns a list of the errors encountered during script execution.  If there is more than one error, they are separated by a line feed.  These error variables can be accessed in the script or via the UI.  In the script, you can simply refer to the ScriptError variable to find its’ value.  For example, in the script below, I can check for the error code 8 (“File not found”) to determine if the file being loaded was missing.

script.png

Error variables can also be access in the UI via the Text & image object.  In the Text & image object measure, simply enter an equal sign and the name of the variable like this:

measure.png

To return something like this:

errorlist.png

Error variables are useful and easy to use.  I find ErrorMode = 0 the one I use the most when I know there is a chance that the script may throw an error that is ok to ignore.  It is also useful if you need to control the path of the script’s execution based on an error.  You can learn more about error variables in Qlik Sense Help.

 

Thanks,

Jennell

Jennell McIntire

Age Function

Posted by Jennell McIntire Jul 13, 2018

In this blog I thought I would share the Age function – a function that is not new to Qlik Sense or QlikView but new to me.  The Age function, which can be used in the script or in a chart expression, returns the age or the number of “completed years” based on a given date (timestamp in the syntax below) and a birthdate (date_of_birth in the syntax below).  The syntax for the Age function is as follows:

 

age(timestamp, date_of_birth)

 

Based on the timestamp value, the Age function will return the number of full years that have passed since the date_of_birth.  Let’s look at a few examples.  In the table below, there are 5 players along with their birthdate.  In the last column, I am calculating their age using the Age function.  I am using the Today() function for the first parameter – this will return the current date (7/13/2018) from the system clock.  The second parameter is the birthdate (a field in the data model) for the player.

table.png

Notice that the date format for the current date and Birthdate are the same.  The format of dates in an app are based on the Set DateFormat statement at the beginning of the load script unless you opt to format them differently.  In this example, the DateFormat is set as follows:

Set.png

If I try to calculate the age based on the expression below, it will not work because the first parameter is not formatted properly based on the DateFormat (M/D/YYYY) I am using in the app.  In this case, the Age function will return null.

expression.png

Age can also be calculated based on a date other than the current date.  For instance, I can calculate age based on an event or the last day of the year.  The Age function is a very simple function to use and can be used in various scenarios other than age to calculate the number of years that have passed since a given date.

 

Thanks,

Jennell

One of the new features in the April 2018 release of Qlik Sense was the addition of the NumericalAbbreviation variable.  This variable allows users to specify the numeric abbreviations that are used when a numeric value has an Auto number format.  This was great because by default, G is used to indicate billions and my preference is to use B.  Now, I can do that by editing the NumericalAbbreviation variable in my script.  If you are familiar with scripting, you know that the first few lines on the Main tab in the script are SET variable statements that indicate what abbreviations and values you would like to use to represent certain data in your app.  For example, in the script below, the MonthNames variable is set to how I would like to abbreviate months in my app.

SET month.png

With the NumericalAbbreviation variable, I can specify how I will like the numeric abbreviation to appear.  By default, it is set like this:

SET default.png

If you created an app using a release prior to the April 2018 release, you will not see this variable on the Main tab in your script but you can add it to your script along with the other variables statements.  The easiest way to do this is to copy and paste it from Qlik Help.

 

In the app screenshot below, you can see the G used in the Total Gross KPI and in the Top 10 Theaters bar chart (bottom left).

G-sheet.png

Since my preference is to use the B instead of G to show billions, I changed the NumericalAbbreviation variable to be:

SET - Copy.png

Now the app looks like this:

B-sheet.png

A small, easy tweak to the script, allowed me to change the numeric abbreviation in the app.  I should note that the numeric abbreviation is used only when Numbering Format is set to Auto.  So, if the Numbering Format is set to something other than Auto such as Money or Number, the NumericAbbreviation variable is not used and the format that you specify in the properties of the object is used.

 

In this blog, I showed one example of how the NumericalAbbreviation variable can be used but it can also be used to add spacing before the abbreviation or other custom abbreviations, if you choose.  This seems like a small feature but it has a large impact providing users with more flexibility and control over how the data is displayed.  To learn about other new features released in April, check out Michael Tarallo’s Qlik Sense – What’s New April 2018 video.

 

Thanks,

Jennell

Jennell McIntire

Nested Loops

Posted by Jennell McIntire May 4, 2018

There are various loops/control statements that can be used in a script to control script execution.  Some common ones include:

  • If…then
  • Do…loop
  • For…next
  • For each…next

It is also possible to use one loop/control statement within another loop.  In the script I am going to review in this blog, I used all of these control statements except the do…loop to generate the rank of a company, by region, over 8 years.  Let me begin by setting the scene and explaining what I am trying to do.  I had 8 years’ worth of company data that included the company's overall rank (in a Data table).  Let’s say the overall rank is the company’s rank based on sales across all regions for a given year.  My goal is to get a company’s rank for their specific region, each year, based on the overall rank.  For example, in the example data table below Company C may have an overall rank of 3 in 2017 but in its region, North America, it is the top company so it has a regional rank of 1.

 

Example Data

YearCompanyOverall RankRegionRegional Rank
2017Company A1Europe1
2017Company B2Europe2
2017Company C3North America1
2017Company D4Central America1
2017Company E5North America2
2017Company F6Europe3
2017Company G7Europe4

 

What I would like to do via the script is determine the regional rank for each company, each year, so I can use it in a KPI object in an app.  Here is the script that I am using to accomplish this:

script.png

Let’s step through the script step by step.

  1. First, I use a For each…next loop to loop through each region (line 1). 
  2. Then I use a For…next loop to loop through the years (line 3).  Since I knew what years of data I had, I hard-coded the years but you can also use variables for the years which is a good idea if the years may vary. 
  3. My next step is to use an If…then statement (line7) to determine if this is my first time through the loop.  If it is the first time, year will be 2010 and the region will be Africa so this is what I check for in the If...then statement.  If this is the first time through the loop, I create a Temp table that has the key field, CompanyRegionKey, and the field, Regional Rank, which I calculate using the RowNo() function.  The RowNo() function returns the position of the current row in the resulting table starting at 1.  Since I am sorting the table by Rank, the rows will be added to the table in the regional rank order.  You can learn more about the RowNo() function in my RecNo or RowNo? blog.  If this is not the first time through the loop, then I create the Temp table and I use the noconcatenate prefix so that the data is not concatenated to the already existing Temp2 table (which has the same fields). 
  4. My next step is to create (line 26) or concatenate to (line 29) the Temp2 table which will store the key field and the new Regional Rank field. 
  5. After this step, I Drop the Temp table (line 32).  I remove the Temp table from the data model and recreate it every time I loop through the script so that RowNo() always starts at 1.  If I did not delete the Temp table and concatenated the data to it, the RowNo() function will pick up where it left off instead of starting over at 1.
  6. For each region, I loop through all the years and create and save the Regional Rank.  Once all the years are complete for a region, the script will go to the next region in the For each…next loop. 
  7. Once all regions and years are complete, I Left Join the Regional Rank field to my Data table and then Drop the Temp2 table from the data model.  I now have a Regional Rank value for each company in my Data table.

Save yourself time and lines of script by using loops when you need to repeat one or a series of statements in your script.  Loops can be nested, as I did in the script above, or used one at a time.  Instead of repeating the same statements for each year and each region, use nested loops so the statements in the script only need to be written once.  This also makes maintenance easier.  In the future, if a change needs to be made to the script, it only needs to be done once.  Check out other blogs about loops with Henric Cronstrom’s blog on Loops in the Script and my blog on the Do…Loop.


Thanks,

Jennell

Jennell McIntire

Replace() Function

Posted by Jennell McIntire Apr 6, 2018

The Replace() function can be used in the script or a chart to replace all occurrences of a sub-string with another sub-string within an input string.  Basically, you can replace a character or string of characters in a field or input string.  The syntax from Qlik Help looks like this:

 

Replace(text, from_str, to_str)


The first parameter, text, is a string.  This can be a field or some text.  The second parameter, from_str, is the string you would like to replace in the input string.  This can be a single character or a string of characters.  The third parameter, to_str, is the string that should replace the second parameter.  If the from_str is not found in the text then nothing is replaced.  The Replace() function works from left to right.

 

I have used the Replace() function when I have a field that has some characters that I do not need.  For example, I was loading names in a recent project and some of the names had a leading underscore character (_).  Since this was the case with only some of the names and not all, I used the Replace() function to replace the underscore with an empty string using syntax like this:

replace.png

You can also nest the Replace() function.  I have used this if there is more than one character I would like to replace in a string.  The nesting works from the inside out so keep this in mind as it may affect your results.  In the table below are a few examples of how Replace() can be used.

table.png

  • Column 1 - shows the original text string for Product.  This is the first parameter for all the Replace() examples.
  • Column 2 – replaces the underscore with a space.
  • Column 3 – is an example of a nested Replace() function.  First it replaces the underscore with an empty string.  Then it looks at the result of that string and replaces the question mark with an empty string.  The last step is to look at the results of that string and replace underscore question mark (_?) with a space (which will not be found in the Column 3 example).
  • Column 4 – is another example of a nested Replace() function but notice how the order affects the results for the input string “Product_?E”.  Since the first Replace() function to be evaluated replaces the “_?” with a space, the next two Replace() functions do not find the second parameter to replace.  Therefore, the result is “Product E” with a space before “E.”  In Column 3, the result is “ProductE,” without a space, since the underscore and the question mark were replaced individually with an empty string before the Replace(‘_?’, ‘ ‘) part of the expression was executed.

 

While my example shows how the Replace() function can be used in a chart expression, I always use it in the script to handle any replacements as I load the data.  What is nice about the Replace() function is it does not modify the text if the sub-string cannot be found.  With that in mind, you should make sure you want to replace all occurrences of the sub-string because there is not an option to pick and choose which sub-string occurrences to replace - all of them will be replaced.

 

Thanks,

Jennell

As a member of the Demo Team, I often use random, dummy data to build demos.  If the data is not exactly how I would like it to be, I can tweak it to best illustrate Qlik products and features.  One of the tweaks that I often make is to update the dates in the data model – I take an old data set from a few years ago and refresh it so it looks more current.  The AddYears and AddMonths functions make this easy to do.

 

The AddYears and AddMonths functions can be used in the script and in a chart.  Here is how the AddYears function is defined in Qlik Sense Help.

 

AddYears

This function returns the date occurring n years after startdate or, if n is negative, the date occurring n years before startdate.

 

Syntax:

AddYears(startdate, n)

 

The AddYears function adds or decreases a date by the number of years indicated in the second parameter.  If the parameter is positive, the date is increased, and if the parameter is negative, the date is decreased.  In the table below, the Date column has the original date.  The AddYears(“Date”, 5) column increases the date by 5 years.  The AddYears(“Date”, -5) column decreases the date by 5 years.  You can see how easy this to use to refresh the dates in your data model.

AddYears.png

The AddMonths function works the same way except the AddMonths function has a third parameter to set the mode.  The syntax looks like this:

 

AddMonths

This function returns the date occurring n months after startdate or, if n is negative, the date occurring n months before startdate.

 

Syntax: 

AddMonths(startdate, n , [ , mode])

 

The mode, as defined in Qlik Sense Help, “specifies if the month is added relative to the beginning of the month or relative to the end of the month. If the input date is the 28th or above and mode is set to 1, the function will return a date which is the same distance from the end of the month as the input date. Default mode is 0.”

 

Let’s look at some examples.  In the table below, the AddMonths(“Date”, 3) column add 3 months to the date in the Date column and AddMonths(“Date”, -3) shows a date 3 months prior to the date in the Dates column.  By default, mode is 0 so by excluding it I am assuming the default value.  So you can see that AddMonths(“Date”, 3, 0) returns the same results as AddMonths(“Date”, 3).  If I set the mode to 1, AddMonths(“Date”, 3, 1), it will take into account the distance of the date from the end of the month when the day is the 28th or above.  You can see that for the Dates 1/1/2013 and 1/15/2013, AddMonths(“Date”, 3, 1) returns 4/1/2013 and 4/15/2013, respectively, as you would expect.  But for 1/30/2013, the results are a little different.  Since the 30th is past the 28th day of the month, the function checked to see how many days from the end of the month the day falls.  In this case, the 30th is 1 day from the last day of the month (which is the 31st for January).  So the results of AddMonths(“Date”, 3, 1) will return a date 3 months later and 1 day from the last day of the month, hence 4/29/2013 since there are 30 days in April.

AddMonths.png

The AddYears and AddMonths functions can also be used to create a new date in your data model.  For instance, if you need to create a new date field that is one month after a transaction date, you can do that in the script with an expression like this:

 

AddMonths(“Transaction Date”, 1) as “New Date Field”

 

There are many ways these functions can be used in your app.  The one thing to keep in mind is if you are going to increase or decrease the dates in your data model, you should do so consistently.  Make sure you are updating all dates in the data model by the same time frame.  This way, date relationships in the data model are maintained throughout the app.  You may also need to update other date related fields like day, week, month, year, etc. if you are using these fields in your app.

 

Thanks,

Jennell

Do you ever use inline tables in your Qlik Sense app?  I do, especially when I need to add a small amount of data to an app that does not already exist in an Excel file or some other data source.  With the November 2017 release of Qlik Sense, manual entry was added in the Data manager.  With manual entry, users have the option to either manually enter their data into the table editor or paste their data into the table editor to add it to the Data manager.  I like this new small feature because it makes it very easy to copy Excel data or a table from a web page and add it as a table in the Data manager.  Let’s look at how it works.

 

From the Navigation menu, click on Data manager to open it.

menu.png

Once the Data manager is open, click on the plus sign to add data.  On the left, there is an option for Manual entry as seen below.  Select Manual entry to open the Manual entry editor window.  Once the Manual entry editor window is open, users can enter a name for the table they would like to add as well as the field names and table entries.  To enter data, double-click on the cell and start typing.  You will find that as you enter data into the cells, additional rows and columns are added automatically.  Once you are done entering the data for the table, click the Add data button at the bottom right to add the table to the Data manager.  Users can also paste in rows from an Excel table or a webpage which is how I like to use it.  Simply, copy the rows you would like to add, select the entire data entry table by clicking a column or row until they turn orange (as seen below) and then paste the table/rows (Ctrl-P).

manual entry paste.png

After the data is added to the Data manager, it can be loaded and added to the data model.  Refer to Qlik Sense Help for shortcuts that can be used when using the table editor.  Now, if you are accustomed to creating inline tables via the script editor, you can still do that in the script editor using the syntax below:

 

inline.png

 

 

 

This script will add a Products table with 2 fields, Product and Sales.  Five rows of data will be added to the Products table.

 

 

 

 

 

Manual entry allows users to manually enter data into the Data manager in a few easy steps.  Users do not need to remember the inline load script and they can load just the rows of data they need.  Manual entry is ideal when you have a small amount of data to add to an app.  When you have a lot of data, loading directly from the source or an Excel file may be easier.

 

Thanks,

Jennell