Starting with version 12 of QlikView we released a new connector that allowed developers to extend the target data sources available. The Qlik REST Connector provides a means for the Qlik Analytics Platform to efficiently load data into a Qlik Sense or QlikView application from a REST service that returns data in XML, CSV or JSON format.What is REST?REST stands for Representational State Transfer. It relies on a stateless, client-server, cacheable communications protocol. RESTful applications use HTTP requests to post, read, and delete data. REST is a lightweight alternative to RPC (Remote Procedure Calls) and Web Services (SOAP, WSDL, et al.).More info on REST (Representational state transfer - Wikipedia, the free encyclopedia)The Qlik REST ConnectorThe Qlik REST Connector is a generic connector, it is not tailored to a specific REST data source. The requirements for creating connections must be acquired from the specific data sources. Examples for connecting to popular social network platforms are provided in the help documentation.How to get the connector?Get the connectors here: Qlik Connector Packages How it works. Getting Star Wars dataIf you read the Qlik documentation you will find examples on how to get data from Facebook, Twitter, LinkedIn and even Google Analytics.For this post example we are going to get data from this website http://swapi.co/ It contains data from the universe of Star Wars. It uses JSON format to give us access to data about the seven films so we can plot some SW demographics.Because Qlik REST is a generic connector first step to connect to a new data source will always be to carefully read the documentation from the source where the data will be acquired from.I’m interested in getting data from the Characters, in the API documentation web page we can read how to query the requested data.http://swapi.co/documentation#peopleCreate new connection. Give your connection a name and then fill in the blanks.Request URL. http://swapi.co/api/people/Data Options. If you want to specify the output data type, JSON, CSV or XML during the load process you can uncheck it otherwise keep it auto detect.Key generation strategy will let you define how you want it to generate the table’s keys, I rarely change it from Sequence ID.Authentication. This API uses no authentication so we can skip that.Pagination. Qlik REST connector will provide you with standard pagination methods (explained in help page) and Custom pagination that will let you do build your own process.If you are looking for more pagination examples please check help page For this particular example I will pick Next URL as it comes specified in the API documentation.Query parameters & Query headers. For this API we don’t have to specify any further parameter.We should be good to go (you might want to normalize fields names and so forth) and perform a reload.I'm attaching the app so you all can check how it's done.Enjoy QlikingAMZhttp://twitter.com/arturoqv ...View More

The Visualization API‌ is a cool new API introduced in Qlik Sense 2.2 that allows you to create visualizations right in javascript, removing the need to create visualizations in the Qlik Sense client before they can be used in a mashup.Here's a simple example, using the "Consumer Goods Sales" app.app.visualization.create('linechart',['Month', '=Sum([Sales Margin Amount])']).then(function(vis){ vis.show("QV01");});This will create a line chart with the one dimension, the "Month" field, and one measure, the expression "=Sum([Sales Margin Amount])". If you try this though, you'll notice that the y-axis label is equal to the expression, and you may want to name the label instead. In this case, we need to use the third, optional, parameter to the Visualization API create() method, which is an options object.In the options object we can specify a qHyperCubeDef‌ instead of using the second, optional, column parameter to the create() method. We also have to do it this way if we wish to use a custom Dimension or Measure created in the Qlik Sense client, as the column parameter will only accept field names and expressions. Here's an exampleapp.visualization.create('linechart',[],{ qHyperCubeDef: { qDimensions: [ { qDef: { qFieldDefs: [ "Month" ] } } ], qMeasures: [ { qDef: { qDef: "Sum([Sales Margin Amount])", qLabel: "Sales Margin" } } ], qInitialDataFetch: [ { qHeight: 12, qWidth: 2 } ] }}).then(function(vis){ vis.show("QV01");});Notice, we left the column parameter as a blank array. If you try to enter text into the column parameter array in addition to the including the qHyperCubeDef option, you may end up with an error.You can set quite a few options like above, or you can use the Visualization API setOptions() method‌, which lets you change options on an already existing object. For instance, if we wanted the chart above to display sales over time instead of sales margin over time, we could update the hypercube with the setOptions() method, like below.visRef.setOptions({ qHyperCubeDef: { qDimensions: [ { qDef: { qFieldDefs: [ "Month" ] } } ], qMeasures: [ { qDef: { qDef: "Sum([Sales Margin Amount])", qLabel: "Sales Marginffff" } } ], qInitialDataFetch: [ { qHeight: 12, qWidth: 2 } ] }})Notice we called the setOptions() method on visRef, which is a reference to the vis object returned in the create() method callback.This is really just scratching the surface of the possibilities of the Visualization API. Be sure to check it out! ...View More

Databases are usually not very forgiving. Strict rules apply, defining what’s allowed and what’s not. For example, you are not allowed to enter data unless it has the right data type and is formatted the right way. Further, you are often not allowed to enter a value for a foreign key unless this value already exists in the master table. And you are not allowed to enter the same value twice if the field is a primary key. The reason is of course to ensure data integrity. Without such rules, the database would soon be cluttered with bad quality data and contain a large number of errors. The fact is that a good system is one that has a large number of rules, but at the same time is easy to use: Equipped with a user interface designed in a way so that the user doesn’t notice the rules – or at least isn’t disturbed by them. But with the Qlik engine it is a very different situation. QlikView and Qlik Sense should not make sure that the data is free from errors. Instead, they should do exactly the opposite: Display the source data along with all its errors. This requirement is totally different from the demands you have on a database, and as a result the Qlik engine is built in a different way: Data Types There are no data types in the Qlikengine. The reason is simple: You may have data from different tables or even from different data sources in one single field. Then there is a potential risk that you have different data types in the different sources. When loaded, all fields are converted into duals (number and text, or just text), and so one field can contain data that originally had different types. Formatting A single field can have a mixed data format. Also here, the reason is simple: Different sources may have different formats. As a result, it doesn’t matter if a date is formatted as 3/31/16, 2016-03-31 or 42460. They will all three represent March 31, 2016. Each distinct field value has its own format, and a single field may thus be displayed with different formats. Referential integrity The Qlikengine does not enforce referential integrity. For example: You may have a customer ID in your fact table that does not exist in the customer table (which would be an error in the data integrity of the database). But the Qlikengine will accept this and show NULL as customer name. Relationship type Often you know if you have a many-to-one or a many-to-many relationship between two entities. But this information is not loaded from the database. Instead the Qlikengine assumes worst case and is always prepared for a many-to-many relationship. Links between tables don’t carry information about relationship type. And all calculations involve aggregations, since there is a possibility for multiple values of the referenced field. The bottom line is that the Qlikengine is a very forgiving engine. It handles errors in all of the above cases gracefully. No matter how many such errors you have in the data, the Qlikengine will always make a best-effort attempt in evaluating and showing the loaded data. HIC Further reading related to this topic: Data Types in QlikView Automatic Number Interpretation It’s all Aggregations ...View More

The Demo Team recently built a mashup for the 2016 Oscars where we analyzed the nominees and award winners over the past 88 years. The design for the mashup often showed nominees and winners in the same chart as seen below where the silver bar indicated nominations and the gold bar represented wins.One requirement for the app was to color the number of nominations bar silver and the number of wins bar gold. In the data model, we did not have a field that we could use as the dimension to show the coloring for nominations and winner so at the suggestion of Alexander Karlsson we used the ValueList chart function. As noted in Qlik Sense Help, “ValueList() returns a set of listed values, which, when used in a calculated dimension, will form a synthetic dimension.” In the Actresses bar chart above, we created the dimension using this syntax:ValueList('Oscars', 'Nominations')Thus creating a synthetic dimension with two possible values: Oscars and Nominations. Then in the measure, we used an If statement to assign a measure to each list item in our ValueList dimension.If(ValueList('Oscars', 'Nominations')='Oscars', Count({<Actress_Win_flg={'1'} >} Distinct Film), (Count(Distinct Film)-Count({<Actress_Win_flg={'1'} >} Distinct Film)))If the dimension is ‘Oscars’ then we count the films where the actress won. Otherwise, we count the nominations where the actress did not win. This same logic was used to handle the coloring of the bars. We colored the bars by using this expression:If(ValueList('Oscars', 'Nominations')='Oscars', '#e1aa3d', '#ccc')If the dimension is Oscars showing the wins, then color the bar gold otherwise color the bar silver.The ValueList chart function provided what we needed to treat nominations and winners as if they were a dimension providing us the flexibility to vary the measures and coloring we wanted to use on the bars in the bar chart. This solution saved us time from having to modify our data model to accommodate our design requirements. The only difference between the ValueList synthetic dimension and a normal dimension is synthetic dimensions are not affected by selections. Alexander Karlsson wrote a blog about ValueList as well as ValueLoop – check it out.Thanks,Jennell ...View More

The Aggr() functions is one of the most advanced functions in the QIX engine, and it is not always easy to use. It does not get easier when you put set analysis expressions in it.In one of my previous posts (Pitfalls of the Aggr function) I recommended having the set analysis expression in both the inner and the outer aggregation function when using set analysis in the Aggr() function. This is a good rule of thumb, because in most cases doing so will generate the result that you want.But this is not always correct.In more complex calculations you often need to use the condition in one place only – sometimes in the inner aggregation, sometimes in the outer. It depends on how the condition is formulated. Then it is important to understand the difference between the two positions.The evaluation of the Aggr() function is a two-step process: In the first step, an intermediary virtual table is created using the inner aggregation and the dimension of the Aggr(). In the second step, this virtual table is aggregated using the outer aggregation.For example, say that you want to find the largest order value per year. Then you would need to first calculate the sales value per order, and in a second step find the largest of these values. HenceThe first step aggregates the source data (with multiple records per Order ID) into a virtual table with one record per Order ID, and the second step finds the largest values in the virtual table.However, there is not yet any set analysis in the expression. So, let us use the following requirement instead:Show the largest order value per yearInclude only products from the product group “Shoes” in the order valueCalculate these numbers only for 2014 and 2015The two conditions correspond to the following set analysis expression:But where should this expression be written? In the outer or in the inner aggregation?To answer this question, we must ask ourselves in which step the conditions should be used. Then it becomes obvious that the condition in product group must be used in step one – in the inner aggregation. If it is used in the outer aggregation only, the order values will be incorrect – they will be calculated from all products.The condition in year, however, can be put in either place. Hence, the following expression will work fine:From the above example one might draw the conclusion that you always should put the condition in the inner aggregation. But this is not the case. Sometimes you have a condition that cannot be put in the inner aggregation. The following requirement can serve as example:Per year, show the bestselling product within the product group “Shoes”Show how this product ranks compared to all products, also non-shoesThe solution is the following tableThe condition in product group should be evaluated in step two, so the expressions used for Product and Rank are:Here it is not possible to have the condition on product group in the inner aggregation, since this would interfere with the calculation of the rank. You must have it in the outer aggregation.Bottom lines are:You need to figure out if your condition should be evaluated in step one (in the inner aggregation) or in step two (in the outer aggregation). This will determine where to put the set analysis expression. You may need to use The Feynman Problem Solving Algorithm.If you can’t figure out where to put your set analysis expression – try putting it in both the outer and the inner aggregation function, and keep your fingers crossed. Afterwards you should however verify that the numbers are what you want.HICFurther reading related to this topic:Pitfalls of the Aggr functionA Primer on Set Analysis ...View More

Working on one of our projects, the requirement was to export the object data. It seems though, 2.1 does not let you do that. I used both: app.getObject(value, value).then(function(model){ model.exportData() and qlik.table(model).exportData({download: true}) Both of them returned error Object {code: 403, parameter: "REST HTTP error", message: "Forbidden"} Until this bug is fixed, I have created a workaround. A new directive that will handle the export by accepting Dimensions and Measures, passing them to the api service. This is for every Hypercube we want to create which is also for the objects like stacked bar chart that 2.1 does not have the ability to export yet. I believe this is fixed in 2.2 though!We will use the template from Creating a website with Angular and the Capabilities API.In controllers/dashboard.js add the Dimensions and Measures for export $scope.export = [ { headers:['Case Owner Group', 'Avg Case Duration'], data:[['Case Owner Group'], ["Avg([Case Duration Time])"]] } ]; In the views/dashboard.html add a button that will use the new directive <button class="btn btn-default pull-right" export-to-csv data="export[0].data" headers="export[0].headers" title="'myFileName'">Export</button> git: https://github.com/yianni-ververis/capabilities-api-angular-templateBranch: Qlik Branch ...View More

Dear Qlik friends & family,I’m very pleased to announce that one of our very favorite functions has been finally upgraded with a nice and longtime requested functionality. Aggr() virtual table is now sortable!Note: For those of you not familiar with what Aggr() is and what can do for you, please review the following materials:Aggr() Technical Brief HIC bringing some clarityDespite the fact that we have been able to do some crazy stuff with Aggr(), every time we wanted to explore the possibilities of inter-record or range functions we faced a wall, Aggr() array of values couldn’t be sorted by any means. You couldn’t control how the data was ‘stored’ in the aggr() virtual table (load order by default) making it hardly usable for a variety of cases such as cumulative calculations, inter-record and range calculations, etc.Aggr syntax used to be like: Aggr({SetExpression}[DISTINCT] [NODISTINCT ] expr, dim{, dimension})What's new?From now on, we count in with a brand new Aggr() chart function that allows us to sort the resulting array (both in Qlik Sense 2.2 and QlikView 12*). The magic happens in the new StructuredParameter. Syntax is now as following: Aggr({SetExpression}[DISTINCT] [NODISTINCT ] expr, StructuredParameter{, StructuredParameter})A StructuredParameter is a combination of a dimension, and optionally the all new sorting method. StructuredParameter syntax is as follow: 1 StructuredParameter: (FieldName, (NUMERIC, ASCENDING)) 2 StructuredParameters: (FieldName,(Field2Name,(FREQUENCY,DESCENDING)))An Aggr() array can contain virtually anything so you have to specify what data type will be sorted within the array of data (numeric, text, frequency or Load_Order) and the sort criteria, basically ASC or DESC. I strongly recommend you to check the Aggr() help page and get familiar with the new syntax.Real life examplePlease read this blog post to discover a real usage scenario. (...) I needed to capture the percentage and number of the gains and losses in a KPI object(...) In order for the Aggr() function to work as I expected, I needed to make sure the MonthYear field was sorted properly in ascending order so I handled this in my script. Note that if the MonthYear field was not in ascending order, the Above() function may not always return the previous month thus returning the wrong results.Now, to solve Jennell's dilemma we could simply use Aggr() avoiding any extra steps such as sorting the data in the script and making our calculation fully dynamic. max(aggr(sum(Members)-above(Sum(Members)), (MonthYear,(NUMERIC, ASCENDING))))I would love to hear usage cases scenarios from you in the comments area. Where you guys plan to use this new capability?.Enjoy Qliking,AMZ*Yes, it also works on QV12. Just ignore the syntax errors you will see in QlikView function editor.UPDATED: For those of you looking for an expression as sorting parameter, good news, please check out HIC postRecipe for a Pareto Analysis – Revisited ...View More

What?!?! Another release? Come on! I thought this year's Superbowl ads were creative, but Puppy, Monkey, Baby was definitely something to see, but...this is even better. Seriously, welcome to the Qlik Community and thank you for taking a brief moment to learn what's new in Qlik Sense 2.2. We’ve done it again! Qlik Sense 2.2Powerful enhancements for both business users and developersIn the past year and a half, we have continued to deliver product innovation at an impressive cadence. Delivering true decision support system and business intelligence software with our platform approach...yep, it's more than just a tool. By keeping our promise to deliver three new releases every year, Qlik has proven it’s ability to deliver as an organization thereby ensuring that our customers have continuous access to an ever increasing set of capabilities. It is important to recognize however that these new capabilities extend well beyond what most people see in the product. At Qlik, we are just as committed to delivering value to the developer community as we are to business users. Delivering capability for both the end user and the developer is critical to support our platform approach. This is because enabling one shared common platform that supports all business intelligence and analytics use cases (click to watch) is the key to seeing the whole story that lives in your data. Not only are we innovating faster than ever but our extensive open APIs ensure that others can innovate right along with us thereby multiplying the power of the platform approach.Qlik Sense 2.2 enables business users to more freely prepare and explore data, and provides developers new, powerful tools for building custom applications and extending Qlik Sense. Here are some of the highlights:Advanced data preparation automatic date handling, calculated fields, and a newly redesigned data managerAlternate dimensions and measuresuser can swap measures and dimensions int he same chart to answer many questions Powerful new developer APIs and capabilities new app integration APIvisualization API for programmatic creation of analyticsnew integrated developer environment (IDE) plugin for Visual StudioExpanded printing and exporting capabilities for stories and charts to PowerPoint, .PDF, and ExcelNumerous user experience and enterprise management improvementsYou can learn more and see these features in action here:Then learn how to use some of these great new features on the Qlik Help Channel Playlist: What's new in Qlik Sense 2.2 - YouTubeEnjoy guys - please feel free to leave me your comments and questions, we love to hear from you.Regards,Mike TaralloSenior Product Marketing ManagerQlikFollow Me: @mtarallo ...View More

Here are some useful topics related to alternate states and the Capability APIsCreating Alternate StatesCreating an alternate state with the Capability APIs is easy. Just do the following app.addAlternateState('StateName'); Alternate states created through the Capability APIs can be saved in the app or created every session. If you try to add an alternate state that already exists though, you may get some unexpected behavior. That's why I would recommend using something like the following when adding an alternate state: app.getAppLayout(function(layout) { if(layout.qStateNames.indexOf('StateName') === -1) { app.addAlternateState('StateName'); } }); This will check to make sure the alternate state doesn't already exist before adding it to the app.Alternate States and Session ObjectsFor session objects, such as lists and hypercubes, it's super easy to assign them to an alternate state. All you need to do is include the 'qStateName' parameter in your object definition. Below is an example of a list object that fetches 10 rows from the field 'SomeField', and is assigned to the state 'StateName'. app.createList({ 'qDef': { 'qFieldDefs': ['SomeField'], }, 'qInitialDataFetch': [ { 'qHeight': 10 } ], 'qStateName': 'StateName' }, function(reply){}); Alternate States and Qlik Sense ObjectsYou can't assign a Qlik Sense object directly to an alternate state, but with a bit of set analysis, you can make an object respond to selections on an alternate state. Let's take, for example, a measure that is simply the sum of some field name, and add the set analysis needed to make it respond to selections on an alternate state. Sum( {$< FieldName = P( {StateName} FieldName ) >} FieldName) Making Selections on Alternate StatesMaking selections on alternate states is as simple as passing an extra parameter to the Field API methods. Example below app.field('FieldName', 'StateName').select([0], false, false); The above example would select the first row in the "FieldName" field, on state "StateName". The important part of the above is the second parameter to the app.field() method. Utilizing that second paramter, you can apply just about any method in the Field API to an alternate state. ...View More

In my previous post i explained how to use my template and build a website with Angular js and the Qlik Sense's Capabilities API.Creating a website with Angular and the Capabilities APIWhen we build controllers with Angular and creating bindings among objects, on navigation, Angular is doing a very good job on managing the controllers, directives, services etc, but it does not handle properly the bindings from Qlik Sense. Thus, how ever many controllers and pages you may have, the objects that you have called with app.getObject(), are still there. That is why when you make a selection in one object and you navigate to another page, you get the "Error: [$compile:ctreq] Controller 'qv-collapsed-listbox-delegated-open', required by directive 'ngClass', can't be found!" error.Even though this does not affect the user experience, its still an error. Furthermore, if you have a large website with many object, as we usually do in our Demo and Best Practices Team, then this becomes a problem because there is memory allocation on each of the objects that were created with app.getObject().Trying to solve it was a trivial process, since there is no documentation on how to destroy the objects. What I have done is, after the app.getObject() put a then(model), put the models into an array of objects and manage them on every page change. So I destroy all of them before I call $location with model.close() and then assign the new ones after the route has completed loading the new template and controller.Let me explain the code change. I assume that you have already read on how to use the template Creating a website with Angular and the Capabilities APIDefine the object array holder in our app.js var me = { obj: { qlik: null, app: null, angularApp: null, model: [], } }; In the html, put the usual code instead of the directive previously used. In dashboard.html replace L30 with this <div class="qvobject" data-qvid="a5e0f12c-38f5-4da9-8f3f-0e4566b28398" id="a5e0f12c-38f5-4da9-8f3f-0e4566b28398"></div> In our controller dashboard.js, replace L23 with the object array that we will call for binding, me.objects = ['a5e0f12c-38f5-4da9-8f3f-0e4566b28398']; In the same file, under the events, add the new function me.getObjects = function () { api.getObjects(me.objects); } Now lets add the two functions in the API service. We need one function to create the objects and another one to destroy them me.getObjects = function (obj) { var deferred = $q.defer(), promises = []; angular.forEach(obj, function(value, key) { app.obj.app.getObject(value, value).then(function(model){ app.obj.model.push(model); deferred.resolve(value); }); promises.push(deferred.promise); }); return $q.all(promises); }; me.destroyObjects = function () { var deferred = $q.defer(); var promises = []; if (app.obj.model.length >= 1) { angular.forEach(app.obj.model, function(value, key) { value.close(); deferred.resolve(); promises.push(deferred.promise); }); app.obj.model = []; return $q.all(promises); } else { deferred.resolve(); return deferred.promise; } }; Finally, on every page change, call destroy objects and then move to the new page api.destroyObjects().then(function(){ $location.url('/' + page); }); Make sure to check the latest code onGit: https://github.com/yianni-ververis/capabilities-api-angular-templateQlik Branch: http://branch.qlik.com/#/project/56b4a40140a985c431a64b08 ...View More