Wednesday, 19 November 2014

No nodistinct

This article is taken from my new book - Mastering QlikView.  Available from 26th November.



The Aggr function has, as an optional clause, the possibility of stating that the aggregation will be either distinct or nodistinct.

The default option is distinct and, as such, is rarely ever stated.  In this default operation, the aggregation will only product distinct results for every combination of dimensions - just as you would expect from a normal chart or straight table.

The nodistinct option only makes sense within a chart, one that has more dimensions than are in the Aggr statement.  In that case, the granularity of the chart is lower than the granularity of the Aggr and therefore QlikView will only calculate that Aggr for the first occurrence of lower granularity dimensions and will return null for the other rows.  If we specify nodistinct then the same result will be calculated across all of the lower granularity dimensions.

That can be difficult to understand without seeing an example, so let us look at a common use case for this option.  We will start with a data set:

ProductSales:
Load * Inline [
Product, Territory, Year, Sales
Product A, Territory A, 2013, 100
Product B, Territory A, 2013, 110
Product A, Territory B, 2013, 120
Product B, Territory B, 2013, 130
Product A, Territory A, 2014, 140
Product B, Territory A, 2014, 150
Product A, Territory B, 2014, 160
Product B, Territory B, 2014, 170
];

We will build a report from this data using a pivot table:


Now, we want to bring the value in the Total column into a new column under each year, perhaps to calculate a percentage for each year.  We might think that, because the total is the sum for each Product and Territory then we might use an Aggr like this:

   Sum(Aggr(Sum(Sales), Product, Territory))

However, as stated above, because the chart includes an additional dimension (Year) than the Aggr, then the expression will only be calculated for the first occurrence of each of the lower granularity dimensions (in this case, for Year = 2013):


The commonly suggested fix for this is to use the Aggr without the Sum and with a nodistinct like this:

   Aggr(NoDistinct Sum(Sales), Product, Territory)

At first, this will appear to solve the problem:


The problem occurs when we decide to have a total row on this chart:


Because there is no aggregation function surrounding the Aggr, it does not total correctly at the Product or Territory dimensions.  And we can't add an aggregation function - like Sum - because it will break one of the other totals.

There is, however, something different that we can do - something that doesn't involve Aggr at all!  We can use our old friend Total:

   Sum(Total<Product, Territory> Sales)

This will calculate correctly at all the levels:






Stephen Redmond is author of Mastering QlikView, the QlikView for Developer's Cookbook and QlikView Server and Publisher
He is CTO of CapricornVentis a QlikView Elite Partner. We are always looking for the right people to join our team.
Follow me on Twitter: @stephencredmond

Tuesday, 4 November 2014

Easy incremental load

I was demoing a quick incremental load script earlier today so I thought I would share it here.

To generate the test data, I used an echo command at the Command Prompt to generate data using the system time:

echo %DATE% %TIME% %RANDOM% >> Data.txt

Every time I run this command it writes a new line to the text file with the current system date and time and a random value.  Perfect for demoing an easy incremental load.

Here is the code:

// vLastLoadTime holds the last date of loading
// If it is not set, then set it to 1,000 days ago
If Len('$(vLastLoadTime)')=0 Then 
Let vLastLoadTime=Num(Now()-1000);
End If 

// vLoadTime holds the current run time
Let vLoadTime=Num(Now());

// Load the data from the data source
// between the two dates
Data:
LOAD @1:23 as DateTime, 
     @24:n as Value
FROM
[..\Data Files\TXTs\Data.txt]
(fix, codepage is 1252)
Where @1:23>$(vLastLoadTime)
And @1:23<=$(vLoadTime);

// If there were any rows,
// concatenate the QVD rows and re-Store
If NoOfRows('Data') > 0 Then

// Get the length of the QVD file
Let vFileLen=FileSize('Data.qvd');

// If the file exists, load it
if Len('$(vFileLen)')>0 Then

Concatenate (Data)
Load
DateTime,
Value
From [Data.qvd] (qvd);

End if

// Store the full table back to QVD
Store Data into [Data.qvd];

// We can drop the table
Drop Table Data;

End if

Let vLastLoadTime=vLoadTime;

// Load the data from the QVD
Data:
Load
DateTime,
Value
From [Data.qvd] (qvd);

Of course, this is just one type of incremental load that we can do using QVDs.  There is an excellent article in the QlikView help file that describes other scenarios.  It is worth reading.


Stephen Redmond is author of Mastering QlikViewQlikView Server and Publisher and the QlikView for Developer's Cookbook
He is CTO of CapricornVentis a QlikView Elite Partner. We are always looking for the right people to join our team.
Follow me on Twitter: @stephencredmond

Saturday, 18 October 2014

Promote a Qlik Sense sheet from Community to Approved

In Qlik Sense, users (with appropriate permissions) can create their own sheets.  These sheets will not be seen by any other users unless the owner chooses to publish them.  Once published, the sheet goes into the Community sheets area.

It may be the case that a sheet created by a user is so good that we would like to promote it to being an Approved sheet.  Right now, there doesn't appear to be a supported method of doing so.

We can, however, use an unsupported method and that is simply to update the entry in the database for the Sheet object.  You will need to refer to my previous post on connecting to the repository and use a Query tool (such as the one that comes with pgAdmin III).

The table that we want to update is AppObjects.  You will need to write a query (or load the data into Qlik Sense!) to find out the correct ID.  The Object will have Published=true and Approved=false so we just need to update the latter:

Update "AppObjects"
Set "Approved"=true
Where "ID"='F3986BDB-1BD7-41D8-8B27-C5040103B827'

Simple as that.  Once is is approved, as well as appearing in the Approved list, if you duplicate the application, the sheet will appear in the available sheets to edit.


Stephen Redmond is author of QlikView Server and Publisher and the QlikView for Developer's Cookbook
He is CTO of CapricornVentis a QlikView Elite Partner. We are always looking for the right people to join our team.
Follow me on Twitter: @stephencredmond

Friday, 17 October 2014

Configure a Qlik Sense security rule to reload one task

One of the cool things about Qlik Sense Server is the ability to be far more granular as to what you give particular users access to do in different areas.  In QlikView Server, we can make people Document Administrators and give them access to a folder to be able to perform reload tasks on the documents there.  In Qlik Sense Server, we can give a user, or users, access to just one reload task for one application.

In Qlik Sense QMC, I will go to the Security Rules section and add a new rule.  I will probably only want to add Read and maybe Update but not allow Delete for this rule.  The rule will apply to QMC only.


The Resources that I will list here can be either specific names of objects or they will be wildcards.  In this example, my list of Resources are:

QmcSection_Task - the access to the Task section of QMC
ReloadTask_1899c8cb-3073-4362-9f98-a36dee86dcc8 - Access to a specific task
App_3a1be6fa-99b4-422c-94a8-de031c94a7f3 - Access to a specific application

I could give access to ReloadTask_* and App_* to give the user or group access to all tasks and all apps.

In the access, I can assign to a specific set of users or I can specify a name of a role.  This role does not have to exist anywhere else - this is you creating it!

So, how do I find out the Guid of a particular Task or App?  Well, I could query the database directly for the information (see my previous blog entry - Connect to your Qlik Sense repository).  Alternatively, I can use the web browser's developer tools to help:

If I right-click on a Task or App in their respective lists, I can select "Inspect Element" from the menu (IE11 and Chrome - other browsers may vary).  The Span that contains the row will have an element that contains the Guid:


Just copy and paste!


Stephen Redmond is author of QlikView Server and Publisher and the QlikView for Developer's Cookbook
He is CTO of CapricornVentis a QlikView Elite Partner. We are always looking for the right people to join our team.
Follow me on Twitter: @stephencredmond

Connect to your Qlik Sense Repository

There are many reasons why you might want to connect to your Qlik Sense repository.  For example, you may want to create a Qlik Sense application that lists all the applications and objects, along with current publication status.

It is quite easy to connect as it is hosted, by default, in a PostgreSQL database.

First thing that you will need is the download of the PostgreSQL ODBC drivers from: http://www.postgresql.org/ftp/odbc/versions/msi/ - there are both 32 bit and 64 bit versions available.

Once you have installed the driver, you can configure the ODBC connection:



The things that you will need to know are:

Database - QSR
Server - wherever the repository database is installed.
Port - 4432 (not the default of 5432)
User Name - postgres
Password - the password that you provided on installation.

That should be good to go.

For a good tool to browse the database, pgAdmin III is a winner: http://www.pgadmin.org/


Stephen Redmond is author of QlikView Server and Publisher and the QlikView for Developer's Cookbook
He is CTO of CapricornVentis a QlikView Elite Partner. We are always looking for the right people to join our team.
Follow me on Twitter: @stephencredmond

Monday, 25 August 2014

Update to Link Table rules

Back in 2011, I wrote a blog post entitled "Rules for creating a Key/Link Table in QlikView".  There were 3 basic rules:

1. Create a primary key in all the tables,
2. Rename the foreign keys to break the links,
3. Use a mixture of Concatenate and Join to generate the Key table using the Resident data.

These rules will still create you a Key/Link table that will work, but it might not always be the most efficient.  As I have had to deal with larger and larger data sets in QlikView, I have learned the hard way that things that work quite well on a table with a few hundred thousand records do not work quite as well when your tables with a few hundred million.

It is really the first "rule" here that can be the problem.  In a dimension table, the key that associates to the Key/Link table does need to be the primary key, however, the key that associates fact tables to the Key/Link table does not have to be a primary key!  This key only needs to be unique for the set of keys being linked to in the Key/Link table, not for the whole record.

For example, if my fact table contains CustomerID and DateID, most of the time that combination will not make a primary key in the fact table, but it is all the key that we need to associate to a Key/Link table containing CustomerID and DateID.  By not using a primary key in the fact table, we are vastly reducing the cardinality of the key being used and hence the amount of memory required to store it.

Another change that I would make is that I no longer use AutoNumberHashxxx to generate a key value.  Now, I always use AutoNumber with an AutoID.  This is because AutoNumber with an AutoID will generate a set of sequential integers which do not, in general, get stored in symbol tables, further reducing the memory requirement.  Using AutoNumberHashxxx to generate multiple different keys will result in non-sequential integer key values which will have to be stored in Symbol tables.

Yet another change that I would require is that the Key/Link table is generated using the Distinct keyword.  Nothing kills performance quite like having duplicate key values in a Key/Link table.

Finally, I no longer require to rename the foreign keys and then rename them back when creating the Key/Link table - I just leave them as is and then use the Drop Field xxx From yyy statement to remove the old foreign keys.

So, the new rules become:

1.  Create a key in each table at the correct granularity to associate to the Key/Link table.  Use AutoNumber with an AutoID.

2.  Use a combination of Concatenate, Join and Distinct to load a Key/Link table of distinct values.

3.  Use Drop Field xxx From yyy to remove the old foreign keys from the fact tables.

For example:

I have a simple structure with Customer, Calendar, Orders.  I have a separate fact table with Customer, Calendar and Delivery information.  I get a synthetic key that I want to resolve by using a Key/Link table:




Step 1 - Customer and Calendar (DateID) already have a primary key.  In Order I will create an ID from CustomerID and DateID.  I will do the same in Delivery:

   ...
   AutoNumber(CustomerID & '-' & DateID, 'CD') As LinkID,
   ...


Step 2 - Now I load my key table.  I will begin with data from the Order table:

   Key:
   Load Distinct
LinkID,
CustomerID,
DateID
   Resident
Order;

Now Join in the Product and OrderDetail keys from the OrderDetail table.

   Concatenate(Key)
   Load Distinct
LinkID,
CustomerID,
DateID
   Resident
Delivery;


Step 3 - Drop the old foreign keys:

   Drop Fields CustomerID, DateID From Order;
   Drop Fields CustomerID, DateID From Delivery;

Now all should be good:


Of course, we don't just create link tables to avoid synthetic keys, we usually create them to overcome association difficulties such as loops.

There is a special prize of a free copy of my forthcoming book, Mastering QlikView, for anyone who can give me a good, technical (i.e. not "because Qlik say so"), reason as to why the original, synthetic key containing, data structure is less desirable than the link table structure.


Stephen Redmond is author of QlikView Server and Publisher and the QlikView for Developer's Cookbook
He is CTO of CapricornVentis a QlikView Elite Partner. We are always looking for the right people to join our team.
Follow me on Twitter: @stephencredmond

Wednesday, 30 July 2014

Extensions in Qlik Sense

Qlik Sense is all extensions.  Even the out of box objects are extensions.  It also comes with some samples of additional Extensions.

After installing Qlik Sense Desktop, have a look in %userprofile%\Documents\Qlik\Examples\Extensions.  You will find several sub-folders, each containing one extension.

If you have experience with QlikView 10/11 extension objects (or have read my blog entries on how to create extensions) then you will see immediately that there is a different way of creating extensions in Qlik Sense.

There are two core files that you must have:

- a .QEXT file which contains the JSON description of the extension that will be used within the desktop client.
- a .JS file that contains the javascript to implement the extension.  This JS is build around the requiredjs framework.

Additional files can be added as required.

If you copy one of the extension folders into %userprofile%\Documents\Qlik\Sense\Extensions and restart the Qlik Sense Desktop (F5 may be enough) then the extension should appear.

You can also simply create your own sub-folder and create the .QEXT and .JS files and they will be picked up.  However, it is easier to use the Workbench to create your extension from a template.  To run the Workbench, first make sure that Qlik Sense Desktop is running (it provides the web service for this) and then connect to http://localhost:4848/workbencheditor.

There is no documentation for the extensions in the default help for Qlik Sense, but you may be able to access the beta documentation at: http://betahelp.qlik.com/0.95/en-US/online/index.html.

Some of you may have seen a post of mine where I had proposed a new chart type called a Pie-Gauge.  I had already created a QlikView v11 extension to generate these gauges using RaphaĆ«ljs to draw the pies so I wanted to have a go at doing this in Qlik Sense.  After a bit of trial and error, and looking at other extensions (including Ralf Becher's implementation of the d3 dependency wheel), I was able to get what I was looking for:


I am happy for anyone to grab the code for this from my github repository.  Just grab the whole RedmondPieGauge folder and drop it into your %userprofile%\Documents\Qlik\Sense\Extensions folder and restart the Qlik Sense Desktop.

You need to specify one dimension and then two expressions - one for Actuals and one for Target.

Each of the objects - including the text labels - are clickable to make selections exactly as if they were a Qlik Sense object.  This also implements a slider in the properties to allow you set a doughnut size.  Anyone who knows me will know that this was definitely an academic exercise - I hate doughnut charts!

Enjoy.


Stephen Redmond is author of QlikView Server and Publisher and the QlikView for Developer's Cookbook
He is CTO of CapricornVentis a QlikView Elite Partner. We are always looking for the right people to join our team.
Follow me on Twitter: @stephencredmond