Objective:

For this document we will review how to POST data into a webhook using the Qlik Rest Connector. We will review two examples of calling a webhook in Slack and Microsoft Teams.

What is a webhook

A webhook is a configurable callback URL which allows for applications to call other applications. The most common use cases involving webhooks involve integrating alerting into chat applications like Slack or Microsoft Teams.

Qlik REST Connection Configuration

In this section we will configure a Data Connection using the Qlik REST Connector for a POST which will later be used by both the Slack and Microsoft Teams examples.

• Create a new Data Connection

• Select the REST Connector option

• Configure the Data Connection:
  • For the URL, enter an arbitrary REST endpoint which accepts POST requests. We are using an example from https://jsonplaceholder.typicode.com/ but you can adjust to fit your environment.
  • Select the Method of POST
  • Uncheck Check response type during 'Test Connection'

• Give the Data Connection a meaningful name & Save

Slack

Slack Pre-Requisites:

This section will be heavily adapted from Slack's API documentation. For a more exhaustive and ultimately up-to-date review, refer to Slack’s documentation for guidance.

Note: These steps may require elevated permissions on your Slack instance. If unavailable consult your Slack administrator(s) for guidance.

• Add the Incoming WebHooks app to your Slack instance

• Select the channel which you want to post to

• Scroll to the integration settings and
  • Copy the Webhook URL
  • (Optional) customize the name
  • (Optional) customize the icon / upload your own

• Save the settings

Qlik Load Script Configuration for Slack:
For this example, we will use more advanced features that are minimally required to post to Slack. We will format our message to include both a dynamic URL to the Qlik app which was reloaded as well as an icon to a meaningful image to symbolize our Qlik app. You can refer to the Slack documentation on its capabilities for more customization of the message.

We will use a load script similar to this one:

// Define your Slack Webhook URL
LET vSlackWebHook = 'https://hooks.slack.com/services/...';
// Build out the URL
LET vServerHost = 'https://qliksense.domain.ad/'; // use https://server.company.com/prefix/ if using an alternative virtual proxy
LET vPath = 'sense/app/'; // Path for direct opening of an app
LET vDocID = DocumentName(); // Determine the app's GUID
// Here is an example body
LET vBody = '{"blocks":[{"type":"section","text":{"type":"mrkdwn","text":"Qlik app reloaded \n <';
LET vBody = vBody & vServerHost  & vPath & vDocID & '|Click here to access>"},"accessory":{"type":"image","image_url":"https://i.imgur.com/vakMyWS.png","alt_text":"App Icon"}}]}';
// Replace the " characters with the chr representations
LET vBody = Replace(vBody,'"',chr(34) & chr(34));
// Replace the / characters with the chr representations
LET vBody = Replace(vBody,'/',chr(47));
// Replace the \ characters with the chr representations
LET vBody = Replace(vBody,'\',chr(92));
// Replace the * characters with the chr representations
LET vBody = Replace(vBody,'*',chr(42));

// This is just a generic REST connection to an endpoint which has the POST method configured
LIB CONNECT TO 'REST-POST (domain_admin)';

RestConnectorMasterTable:
SQL SELECT 
	"col_1"
FROM CSV (header off, delimiter ",", quote "'") "CSV_source"
	WITH CONNECTION (
    	URL "$(vSlackWebHook)",
    	BODY "$(vBody)"
);

[CSV_source]:
LOAD	[col_1]
RESIDENT RestConnectorMasterTable;


DROP TABLE RestConnectorMasterTable;
DROP TABLE [CSV_source];

You will want to adjust the following:

• vSlackWebHook: Insert your Webhook URL copied from the Incoming Webhook configuration
• vServerHost: Adjust to the URL which users will use to access Qlik. If you are using an alternative virtual proxy, then be sure to include this value as well.

After we build the body, you can see that we are replacing certain special characters with their chr representations since the Qlik REST Connector would otherwise treat them as literals. If you have other special characters in the body that you are constructing, then consult with a ASCII / CHR code mapping. This guide used this one (https://testguild.com/qtp-ascii-chr-code-chart/).

Once you have adjusted the body to suit your needs and adjusted the required parameters, reload the app.
If successful then you should receive an alert like so:

Microsoft Teams

Microsoft Teams Pre-Requisites:
This section will be heavily adapted from Microsoft Team’s Documentation. For a more exhaustive and ultimately up-to-date review, refer to Microsoft’s documentation for guidance.

Note: These steps may require elevated permissions for the Team. If unavailable consult the Team’s owner(s) for guidance.

• Go to the Connectors option for the channel

• Search for incoming webhook and add this connector

• Give the webhook a name and optionally customize the icon which it will use then create

• Scroll down after creation and copy the Webhook URL

Qlik Load Script Configuration for Microsoft Teams:
For this example, we will use more advanced features that are minimally required to post to Microsoft Teams. We will format our message to include both a dynamic URL to the Qlik app which was reloaded as well as a clickable icon for users to easily access the Qlik app. You can refer to the Microsoft Teams documentation on its capabilities for more customization of the message.

We will use a load script similar to this one:

// Define your Slack Webhook URL
LET vTeamsWebhook = 'https://outlook.office.com/webhook/....';
// Here is an example body
// Build out the URL
LET vServerHost = 'https://qliksense.domain.ad/'; // use https://server.company.com/prefix/ if using an alternative virtual proxy
LET vPath = 'sense/app/'; // Path for direct opening of an app
LET vDocID = DocumentName(); // Determine the app's GUID
LET vBody = '{"@context":"https://schema.org/extensions","@type":"MessageCard","themeColor":"0072C6","title":"Qlik Sense App Reloaded","text":"Click **Open** to access the app","potentialAction":[{"@type":"OpenUri","name":"Open","targets":[{"os":"default","uri":"';
LET vBody = vBody & vServerHost  & vPath & vDocID & '/overview"}]}]}'; // Dynamically build out the variables defined previously
// Replace the " characters with the chr representations
LET vBody = Replace(vBody,'"',chr(34) & chr(34));
// Replace the / characters with the chr representations
LET vBody = Replace(vBody,'/',chr(47));
// Replace the @ characters with the chr representations
LET vBody = Replace(vBody,'@',chr(64));
// Replace the * characters with the chr representations
LET vBody = Replace(vBody,'*',chr(42));

// This is just a generic REST connection to an endpoint which has the POST method configured
LIB CONNECT TO 'REST-POST (domain_admin)';

RestConnectorMasterTable:
SQL SELECT 
	"col_1"
FROM CSV (header off, delimiter ",", quote """") "CSV_source"

	WITH CONNECTION (
    	URL "$(vTeamsWebhook)",
    	BODY "$(vBody)"
);

[CSV_source]:
LOAD	[col_1]
RESIDENT RestConnectorMasterTable;


DROP TABLE RestConnectorMasterTable;
DROP TABLE CSV_source;

You will want to adjust the following:

• vTeamsWebhook: Insert your Webhook URL copied from the Incoming Webhook configuration
• vServerHost: Adjust to the URL which users will use to access Qlik. If you are using an alternative virtual proxy, then be sure to include this value as well.

After we build the body, you can see that we are replacing certain special characters with their chr representations since the Qlik REST Connector would otherwise treat them as literals. If you have other special characters in the body that you are constructing, then consult with a ASCII / CHR code mapping. This guide used this one (https://testguild.com/qtp-ascii-chr-code-chart/).

Once you have adjusted the body to suit your needs and adjusted the required parameters, reload the app.
If successful then you should receive an alert like so:

Hi All,

Created simple application State->District  wise layer map using KML.  Application used container object which has ability when you click on state automatically further drill down district will be display for India country. 

Hope this will be useful for new comers who wanted to learn google map using layer area option.

Thanks

Vikas

Link Table :

Please find simple link table concept implemented with multiple fact tables. Hope definitely help new comers will help to clear concept of link tables.

Data Modelling is technique in qlik for linking tables we can use link tables concept please refer ppt .

Qlik Tips: Rules for creating a Key/Link Table in QlikView and this post

Re: Advantages of Link Table;

ref: https://www.analyticsvidhya.com/blog/2014/12/remove-synthetic-key-concatenation-link-table-qlikview/

Please find attached excel & data model containing how to implement a link table.

Hope this help to new comers.

Thanks

Vikas

Over the last couple days I've been using the Qlik Sense REST connector to get survey data from SurveyMonkey. As far as I can tell, our organization does not have access to the dedicated SurveyMonkey connector, so REST needed to work. I found the process tricky, so I'm documenting the steps I took to get this working. Some of this material is specific for SurveyMonkey, but I hope that there may be some clues that are relevant for other connections.

Authentication

Whatever service you are using, you will need some kind of authentication token for your final connection. For SurveyMonkey this involves "creating an app". I'm not really making a SurveyMonkey app (whatever that is), but this process provides access to a token. In my case, because I have a paid subscription to SurveyMonkey, I can create a private app and get an authentication token immediately. If you have a free plan, you'll need to go through a more cumbersome process to get the token. Because reading is hard, I inadvertently went through the entire process for a public app - only to realize that it gave me back the SAME authentication token that I already had access to ! So, the good news is that I've seen this process work.

API Docs | SurveyMonkey API Developer Portal (Authentication)

Talking to SurveyMonkey: The Tools (cURL)

To make this work you'll need to make GET and POST requests to the SurveyMonkey API. There are a few tools for this, but if you have Windows, I recommend using the command line cURL tool. Download here. https://curl.haxx.se/download.html

You can unpack the downloaded zip file in your Program Files (or wherever) and then look for the executable file. Run a command prompt from that directory (you can also put it in your path if you'll use it multiple times). You're now ready to talk to SurveyMonkey.

Getting a Token

First you'll need to create your app. Go to https://developer.surveymonkey.com/apps/ and "Add a New App". If you can make it Private, do it because you'll get your authentication token right away, if not Public works. When you've created the app, go to settings and you'll find a "Client ID", a "Secret", a "OAuth Redirect URL" and, if private, an "Access Token". You'll need this information. You should also set the Scopes, in my case I needed View Response Details (and some others), so I set this to "Optional".

Now, following the API documentation from SurveyMonkey, you'll do the following:

1. Follow the link to get "short-lived" code.

Here you'll enter a link in the browser. Replace the colored text with the appropriate values found on the App settings:

https://api.surveymonkey.com/oauth/authorize?response_type=code&redirect_uri=YOUR_REDIRECT_URI&client_id=YOUR_CLIENT_ID

When the browser redirects you it will update the URL. In the URL you should see ...?code=AUTH_CODE

The short-lived code (which is a long alphanumeric string) will only be good for a few minutes, so copy it and get cracking... (you can always do this again to get another code, so no big deal).

2. Get Access Token

Now it's time to use cURL. Open up the command prompt and make sure you have access to the curl executable. Enter the following with all the information you've gathered (you may need to enter it line by line in the console). I suggest creating the full command in a text document, that way if you need to go through this process again (with a new AUTH_CODE), you'll be ready to go.

curl -i -X POST https://api.surveymonkey.com/oauth/token -d \
  "client_secret=YOUR_CLIENT_SECRET \
  &code=AUTH_CODE \
  &redirect_uri=YOUR_REDIRECT_URI \
  &client_id=YOUR_CLIENT_ID \
  &grant_type=authorization_code"

Assuming you did this in time (your AUTH_CODE didn't expire), in the returned message from this post you'll get a response with a "YOUR_ACCESS_TOKEN" and an "access_url". I didn't actually use the access_url, but you might as well keep track of it.

Creating a Qlik Rest Connection

Now that you have your YOUR_ACCESS_TOKEN you can use it to establish the REST connection. Assuming that you want to get all of the responses for one or more surveys you'll need to find the ids for your surveys, set up a connection, and get the data.

1. Get Survey IDs.

The SurveyMonkey API allows you to access the ids of the survey with a GET call. You could do this in Qlik, which is nice because you could save all your survey ids in a table. A more straightforward way is to make the request in cURL and then manually create a record of these ids. This is what I did with curl:

curl -i -X GET -H "Authorization:bearer YOUR_ACCESS_TOKEN" -H "Content-Type": "application/json" https://api.surveymonkey.com/v3/surveys

You'll get back a crazy json text string. Copy it and then enter it at this website to make it look pretty: https://jsonlint.com/

Now you should see a "data" array that has a list of survey names and ids. Copy down these IDs. Personally, I made a table with the name of the surveys I needed and their associated IDs. This can come in handy later for the app.

2. Make the REST connection

In the "data load editor" of your Qlik App, on the right hand side click, "Create new connection" and choose "Qlik REST Connector". 

Let's first assume that you want to create a connection to a single survey to get its data. In this case use one of the IDs you collected in the the previous step. The URL you use should be the same as step one, but with the survey id and "response/bulk" added, for example, "https://api.surveymonkey.com/v3/surveys/SURVEY_ID/responses/bulk"

For the other parameters, here's what I used (do I understand all of this? no):

Method: GET

Auto detect response type: check

Key generation strategy: Sequence ID

Authentication Schema: Anonymous

Skip server certificate validation: check

Use certificate: No

The Query headers tell the SurveyMonkey API service that you have valid access. So, you'll need to provide the access token (you don't need to do "Content-Type").

Name: Authorization    

Value: bearer YOUR_ACCESS_TOKEN

The Query parameters provide additional information to the API about what information you want. In our case SurveyMonkey defaults to providing 50 records per "page" of data. You can increase this by changing the "per_page" value to 100 (the max I believe).

Name: per_page

Value: 100

I also checked, "Add missing query parameters to final request", because (I believe), when we use pagination this setting makes sure that the per_page setting goes to every subsequent page call. May be unnecessary.

Finally, for Pagination type, I used "Next URL". This tells Qlik how to make a request for more data because SurveyMonkey limits how much it will send at one time. (Note, there is a limit on API calls, so if you have crazy big data, this could be a problem).

"Next url' path: links/next

The SurveyMonkey documentation tells you to get this next page address at "links.next", but that's not how Qlik does it, you need the forward-slash. (This took forever to figure out!)

3. Create the Script

Now that you have the connection, you can click "Select Data" icon to determine what data you want. The response type should be set as JSON. Because JSON is a nested structure you'll need to un-collapse the root and other nodes there within to find the data you are looking for. The first time you may want to try gathering everything, and then look at the data model to see what you really need.

Click "Insert script" to put the code into the scripting area. You can run it now and see what you get.

4. Multiple Surveys

What if you don't want to connect to the same survey every time, but rather want to adapt to the ids that you collected in the first step of this section? In step 2, you hard coded the ID into the URL, but there is a workaround.

Edit your REST connector. Instead of using a URL with a hard-coded ID, just use "https://api.surveymonkey.com/v3/surveys"

In fact, I don't think it matters what we put here because we will override this value.

Under the SQL code you received upon inserting script, you'll add a "WITH CONNECTION ()" command. It will look something like this:

LIB CONNECT TO 'SurveyMonkey REST connector'

RestConnectorMasterTable:

SQL SELECT

"per_page",

"total",

"page",

"__KEY_root"

FROM JSON (wrap on) "root" PK "__KEY_root"

WITH CONNECTION(

Url "https://api.surveymonkey.com/v3/surveys/SURVEY_ID/responses/bulk"

         );

The Url in WITH CONNECTION tells Qlik to look at this URL instead of the one defined in the REST connector. What's nice about putting it here is that the URL text string can be a variable. This way you can dynamically load a SURVEY_ID from a table. So for example, I created a variable called vURL that dollar-expands a variable storing the ID of the survey I'm currently interested in.

let vURL = 'https://api.surveymonkey.com/v3/surveys/$(vID)/responses/bulk';

So in WITH CONNECTION I used:

Url "$(vURL)"

If you have these survey IDs stored in a table you can loop through the table (for.. next), Peek at the id in the current row, set it to vID and then run the REST connector with each of the surveys.

Summary

At the end of the day, this process was cumbersome but I learned a lot about connecting to APIs. Hopefully, the SurveyMonkey dedicated connector will be available for others, but this approach seems to work. Perhaps much of this is applicable to other connectors.

Please feel free to give me feedback and comments. There are a few steps of this process that I don't fully understand, so if you have a different approach or can provide more detail, I'd appreciate it.

Using Excel variables in Qlik Sense

Approach :  Use excel sheet for loading all set analysis in Qlik Sense environment and use those variables as a expression in Qlik Sense Charts.

Environment

• Qlik Sense

Resolution

Simple approach to load all set analysis expressions using excel sheet and maintain at one place and use the same for expressions in Qlik Sense Charts.

Find attached excel sheet containing YTD, Current YTD, Growth % formulas in excel sheet same has been loaded in QS & used in Chart as a variable.

This document shows how Set Analysis can be used to create a prior period comparison that is responsive to your current selections.

The expressions can be applied to any data set where you have your main date field broken out into Day, Month and Year fields.

This application is a Qlik Sense version of the QlikView app that has been uploaded to Community here:

QlikView App: Set Analysis - Prior Period Comparison

There is further discussion on the app and technique in the comments there (including some important notes about when your data set doesn't have values for all dates).

There are a number of other tutorials and downloadable examples on our website here:

https://www.quickintelligence.co.uk/qlikview-examples/

If you have any questions please post them in the comments below.

Steve Dark

http://www.quickintelligence.co.uk/

Hello Friends,

I have created series of videos for Qlik section access. You can watch them using below YouTube link  

https://www.youtube.com/watch?v=XhFJtzdQ_Eg&list=PLYupZqrze4LKkofl2OL6Em_MQd9aXpYqk

I have covered section access from very basic level to complex level.  

1) Qlik : 001_Section Access : On a single field

2) Qlik : 002_Section Access : Testing and creating dynamic records for * in section access

3) Qlik : 003_Section Access : Data reduction at column level using OMIT function

4) Qlik : 004_Section Access : Row and Column level Section Access in a single Qlik application.

5) Qlik : 005_Section Access : Row level data reduction on multi fields (2 fields) using Link Table

6) Qlik : 006_Section Access : Row level data reduction on multi fields (3 fields) using Link Table

7) Qlik : 007_Section Access : Row level data reduction on multi fields using Link Table and Cross Join method.

Note : There would be 2 more videos on this series which would be uploaded soon.

Happy Qliking !!!

Manish Kachhia

<Part of http://integration.qlik.com/ and created together with raymond Neves>

Table of Contents

• Security integration: Provide Single Sign On and share access rights
• Introduction
  •     Authentication
  •     Authorization using the security rules
• Detailed security rules
• Appendix A: Streams
• Appendix B: Security flow when opening an app
• Appendix C: Sense ticketing API explained
• Appendix 😧 Security approach summary
• Appendix E: More information and tools about security?
• Appendix F: Security rule examples

Introduction

In OEM software integration cases,  users and their authorizations are stored usually in a SQL table and the OEM website already does the authentication step. So the key thing we have to explain you in this article is how do we transfer the user and his rights to Qlik Sense?  The short answer is: We usually do this by using a ticket (token based) mechanism. This ticket can be seen as a passport for the user. It contains his userId, and the groups/roles which define his authorizations.

Make sure you check the introduction and extended security videos first.

Authentication

Before you can use the Qlik Sense system, in most cases you want to know who the user is, we call this authentication.  The next step is that based on this userId you want to assign the user access rights, we call this authorization. Both developers and users communicate with Sense via the Proxy (see this as a webserver which performs the authentication step: who are you?). For each type of authentication we can create a virtual proxy. So normally we would create a virtual proxy for each authentication mechansim your company needs:

• Internal users using Active Directory
• External users using ticketing
• External users using SAML

Authorization using the security rules

After the authentication (who are you?) you need to think how you want to integrate the authorization (what can you do and see?). The keys you want to protect in Sense are called resources. Example resources are

• In the client side, called the hub context:
  • Stream
  • App
  • Object.type  inside an app
    • Sheets
  • Stories
  • Dimensions
  • Measures
  • Library (master items)
  • Script
  • Data model viewer
• In the admin side, called the management console (QMC) context
  • Reload tasks
  • Security configuration
  • Apps and objects
  • Streams

• Users

There is no mandatory structure you have to follow in Sense. We have designed a very flexible approach in which each "thing" in Sense is a resource. And if you want to "use" it you need to have a "key" that allows you to access that resource. In Sense we protect the resources above with security rules, and it has the following logic:

In most OEM cases we give each customer its own stream,  and therefore we create a rule like this to split the customers in the system

• Allow a user to view (Action),
• all dashboards inside a stream (Resource)
• Provided that the group (e.g. a customer name) of the user matches the name of the stream

Lets illustrate this with the example of a city (a Sense server), it consists of

• streets (streams) which are used to
• group houses (apps), and a
• house consists of rooms (app.objects).

  Most people can only enter 1 house and all rooms. But in order to arrive at your room, you will have to drive though your street, open the house and enter your room. And that is exactly the same way with an OEM security setup in Sense.

SaaS security setup

Most times you have a SaaS solution with a lot of customers in 1 system. You want to divide this. So, we start with giving each customer its own stream, and once you have access to the stream you are allowed to view all apps. Of course we can limit the access to apps and even sheets, the script and database connections.  This is possible because everything is a resource is Sense. And before a user can access a resource he needs at least one security rule that evaluates to true for the action he would like to do (read, update, publish).

Minimum requirements of the security rule

Back to our example of the street and the houses:

You always need access to a stream before you can open the app, or view the resources inside an app:

1. Stream: The highest level is the stream (e.g. a street), once you are in the street you can see
2. App: The apps (the houses), once you enter a house you can see
3. App.object: The app.objects (the rooms) like sheets, stories, script, database connections etc.

Import to remember: If you want to see a chart, you always need to create one or more security rules that together provide access to all the above resources. (stream, app, app.object)

Each resources knows always who its parent is. So you can make a security rule that says

• If the user tries to access a Stream:
  • The name of the resource (in this case a stream) = (must equal)  the name of the group of the user
• Then: you are allowed to see me

Now the user is authorized to see the stream. The next step is to grant him access to the app and app.object with this rule:

• If the user tries to access an app or the contents of an app (e.g. sheet)
  • If you have access to my parent (has privilege)
• Then: you are allowed to see me

Detailed security rules

Sense security is based on Attribute Based Access Control ABAC. Each time a user requests access to a resource, Qlik Sense evaluates the request against the security rules in the Qlik Sense system. If at least one rule evaluates to True then Qlik Sense will provide the user with access according to the conditions and actions described in the security rule. If no rules evaluate to True then the user will be denied access. The fact that Qlik Sense security rules are property-based makes Qlik Sense very scalable as you can build rules based on properties that apply to groups of users. In most BI systems you need to create a role for each organizational value (Spain, France etc.), this is not needed with Sense. We just use a variable to variable comparison: e.g. If your Active directory group (or group provided by ticket/SAML) matches some property of the stream/dashboard you are allowed access.  For more information about Security rules see this video or the Qlik Sense help

If you don't have a key you won't get in, same like your house. If you have a key you can enter your house, and all rooms inside it. (Access to stream, Yes? Ok, than you might see all the dashboards)

So for Sense, if you have access to a stream maybe you want to show the user all dashboards inside the stream. In this way you keep the security concept very simple and effective. As a starting point I would recommend to give each department (HR, Finance) or customer (Customer A, Customer B) its own stream.

So we can give users access to a dashboard, and define whether he can use/edit/create Sheets, Stories, bookmarks etc. For example,  if the user does not have access, that is a security rule that evaluates to true, he won't see the edit or bookmark button in Sense.

Authorization summary

Example: OEM  security example use case for a fictive OEM partner

Functional design of the security principles

• Each customer can only see its own data. Each customer can have 1 or more users. These users can be a member of 1 or more groups/roles which determine what they can see or do in the system. E.g. a manager can see the "whole world" and a regional manager only his "own region".
  • For example we have
    • 2 customers: Sunny and Cloudy Petrol. Each customer can have the roles
      • Manager and

• Employee.
• The security concept should be as simple as possible. In the first phase we will authorize on streams and therefore we will give each customer its own stream. (A stream is the key grouping structure of dashboards)

Technical design of Qlik Sense security:

We have to configure the following layers

1. Give each customer its own stream.
2. Create a security rule to authorize on this stream.
3. Use section access to enforce row level security of the data.

1: A stream per customer

2: Security Rule

3:Section access

In the script we mark a special section as security. In the section we can limit per dashboard what a user/role can see (data filter/reduction). We do this based on the attribute: 'group' you provided in the ticket. In this way you don't need to sync user-group relationships to Sense.

Integrated security overview

The picture below outlines the complete flow from user single sign on with ticketing, to row level security via section access.

Key take aways:

So this is what you DON'T need to do anymore

• You don't need to keep your source system in sync with Qlik Sense. Because the only link between your system and Sense is the group (or other attributes) you assign dynamically in the ticket request code you write. You only need to create the template apps. The rest will be generated by your system by making use of the API's.

More security documentation can be found here

Appendix A: Streams

See a demo of streams here

The content in the hub is organized in streams. A stream is a collection of apps that a group of users has specific access to. The users of the stream can have different access rights. Some users might only be able to read the content in the stream, while others might have the rights to publish their content to the stream.

Appendix B: Security flow when opening an app

Introduction

Users interact with Qlik Sense using authenticated communication through one or more proxies. Information is transferred using cookies. The cookies are necessary regardless of authentication type used. When using a web browser, the web browser sends a ticket to the proxy. The proxy then returns a cookie to the browser and this cookie is included in the communication with the Qlik engine. When using a custom client, the client must set the cookie and provide it to the proxy.

Authentication

Authentication is the procedure of verifying the identity and credentials of users wishing to access Qlik Sense. It can be done in various ways:

• Windows authentication using cookies
• SAML
• HTTP header authentication using cookies and headers. Header authentication should only be used when custom authentication systems are used.
• You can also extend with custom authentication modules like the ticketing method we always use in software integration scenarios.

Security example: The figure below shows the flow in the Qlik Sense security system when a user logs in and opens a app.

1. Authentication: The authentication module in the Qlik Sense Proxy Service (QPS) handles the authentication. The credentials provided by the user are verified against information from the identity provider (for example, a directory service such as Microsoft Active Directory).
2. Session creation: When the user credentials have been successfully verified by the authentication module, a session is created for the user by the session module in the QPS.
3. Access control system: When the user tries to open an app, the Qlik Sense Engine Service (QES) requests the Qlik Sense Repository Service (QRS) to check if the user is authorized to perform the action. The QRS then checks the repository database, where, among other things, all users and access rights are stored.
4. The users are imported into the repository database from one or more User Directories (UDs) (for example,Microsoft Active Directory) using Qlik Sense User Directory Connectors (UDCs). The import is triggered by the Qlik Sense Scheduler Service (QSS) and the intervals in-between imports can be scheduled.
5. Dynamic data reduction: When the user has been successfully authorized by the QRS, the app is opened. Before the data is displayed to the user, the QES performs a dynamic data reduction, where the data that the user is allowed to see is prepared.

See also:

Planning Qlik Sense Deployments: Authorization

Planning Qlik Sense Deployments: Access control

Planning Qlik Sense Deployments: Data reduction

Managing a Qlik Sense site: Virtual proxies

Qlik Sense Proxy Service APIs

Introduction to QlikAuth

You can create the ticketing implementation yourself via the QPS ticket API, or use the QlikAuth module. QlikAuth is a community tool/wrapper for .NET, JAVA, Javascript around the Qlik Sense Ticket API

Appendix 😧 Security approach summary

Appendix E: More information and tools about security?

Appendix F: Security rule examples

You can make the security in Sense as complex or simple as you want, I defined a couple of scenario from simple to complex.

Scenario 1a: Authorization business rules Stream authorization

For our fictive company we defined the following rules

• We have defined groups in the source system. This can be AD groups (user.group) or the groups you supply in the ticket (user.environment.group).
• Each customer can only see its own stream
  • Within a stream he can see all apps
• Within an app he can only see the sheets and stories but not the backend

Scenario 1b: QMC Admin

• Same as 1a
• But now each customer can also have an admin that should have access to the QMC

Scenario 2: Authorization business rules: App authorization

• Each customer can only see its own stream
  • Within a stream he can see specific apps
• Within an app he can only see the sheets and stories but not the backend

Scenario 3: Authorization business rules: Sheet authorization

• Each customer can only see its own stream
  • Within a stream he can see specific apps
• Within an app he can only see the sheets and stories but not the backend

Scenario 4: Apply row/column level filtering

• Each user can only see the data in the app where he is authorized for
• For example, an employee can only see the orders (and everything associated) he created.

Security rule code for each scenario

Implementation of scenario 1a using the QMC - Security rules

Before we start, remember that If you want to see something, you need one or more security rules that give you access to

• The stream
• The app
• The app.object

So in the examples below we create 3 rules (to make it manageble we splitted the rules per resource type, you can also create 1 big rule)

• A rule for the stream
• A rule for the app
• A rule for the app.object

Modify standard rule set

Also we have to look at the standard security rule set in Qlik Sense. Here we have to disable some rules to prevent our your have too much authorizations. You can always find the rules that are active for your stream/app by going to the audit. Double click a cell, and you can see why John, has the rights to create app.objects (sheets, or basiscally all the "things" inside an app)

The rules below have to be disabled. To offer too much access to our users for now.

Template Rule

(resource.resourcetype = "App" and resource.stream.HasPrivilege("read")) or ((resource.resourcetype = "App.Object" and resource.published ="true" and resource.app.stream.HasPrivilege("read"))

Implementation of scenario 1b: QMC admin per customer

If you then create a user in your source system, and give him the admin group. He is able to see the QMC, only for his stuff.

And he can see this:

Only his stream and only his apps which are published in his own stream

Read access but not a specific stream and its apps

View everything in the QMC except apps and streams

Scenario 30: Audit display all

Security rule summary

See Qlik help for an overview of the options in the security rules. In my multi tenant SaaS demo platform I used the rules below.

I recently spent a lot of time digging through materials trying to prepare for a webinar I did today on Section Access in Qlik Sense.

What I found was that while there are a gajillion posts on Section Access in QlikView I found very little detail about Section Access in Qlik Sense. So I whined and belly ached and moaned and pointed fingers and ... just kidding I created some. The attached 13 page document contains what I think is a really good primer on how to implement Section Access in Qlik Sense.

It covers:

How to implement Row Level Security

What an * really does

Explains the difference between USER and ADMIN as an access type

How to handle Section Access via a file or a database instead of just INLINE

How to implement Column Level Security so you can hide PII and PHI

How to implement a combination of Row and Column Level security for when you need Bob to have access to most of the details for Department 42 and Department 44 but he should only see the PII for the employees in Department 42.

Yeah it's that comprehensive. But feel free to comment and add anything that you feel others might need to know. Or describe other use cases and share the code you used.

Disclaimer: I spent about 40 hours researching which included a lot of trial and believe me ... error. So I apologize in advance for the fact that the document isn't "pretty." I simply ran out of time.

Didn't get an engraved invitation to the webinar that I did where I walk through everything in the document and wish to watch it ... you can catch it on demand at:

https://event.on24.com/eventRegistration/EventLobbyServlet?target=reg20.jsp&referrer=&eventid=1282829&sessionid=1&key=91C8EDD7772211BCCC9A72BAA1BAFCF0&regTag=&sourcepage=register

QlikView has two features which allow it to send alerts and data to the user, using Alerts in the front end or QMC notifications on the Server. Presently Sense lacks these out of the box, but it is possible to implement the same using the free Qlik Web Connectors SMTP Connector.

This application implements the sending of data as an HTML table embedded in an email each time an app is reloaded.

It was produced to go alongside this blog post, which describes the process in detail:

https://www.quickintelligence.co.uk/send-data-from-qlik-load-script/

I hope that you find the application useful.  You will find other applications that I have uploaded under my profile on QlikCommunity or on our Downloads page..

Steve

https://www.quickintelligence.co.uk/blog/

• UPDATE: 
  
  The Qlik Help Channel on YouTube has the latest and greatest how-tos for the latest releases.
  Don't forget about the Qlik Online Help as well. It has videos, samples, tutorials and more. Also you can check out our Qlik Support Qlik Fix video series for many troubleshooting videos.

Please also check out our free structured how-to modules to learn more on the Qlik Continuous Classroom.

The following is a consolidated collection of videos and blog topics that demonstrate various capabilities and features that are helpful when understanding the many aspects, features, releases and core competencies of Qlik products. 

Data Alerts for Qlik Sense Enterprise SaaS

• Attached is .qvf file (Sales Dashboard.qvf) you can use to demonstrate data alerts
  Instructions are on 2nd sheet

Do More with Qlik - Webinar Series - Archive

• Take Me to the Archive

View and register for future session here

What's New 2021

• Qlik Sense SaaS In 60 - weekly updates

Qlik Sense Unique Features and Capabilities:

• Comparative Analysis - Alternate States
• QlikViews in Qlik Sense
• Natural Language Processing (NLP)
• Qlik Sense and Salesforce
• The Qlik Associative Difference - Introduction
• Qlik Associative Difference in Action - A Practical Example
• Associative Insights - feature
• Global Smart Search - feature
• Chart Suggestions - feature
• The Cognitive Engine
• The Insight Advisor - (Visual Insights) - feature
• Precedent-based Machine Learning - feature
• What is Data Storytelling
• Working with Qlik Sense selections
• Mobility with Qlik Sense
• Qlik Sense Mobile - Offline Mobile App
• Ways to Collaborate in Qlik Sense

Qlik Data Integration

• Qlik Replicate - A "Hello World" Demonstration

How - Tos, Tips, Tricks Techniques:

• Dimension Limits
• Master Items Library Shortcut (Promoting Measures and Dimensions)
• Introduction to Set Analysis Part 1 and 2
• Using Bookmarks in Set Expressions
• REST Connector - JSON , pagination and multiple schemas
• Power of Qlik Script using Crosstable() - Parts 1 - 3
• Using the Web File Connector
• MS Access and other ODBC data
• Binary Loading Data from Qlik Sense and QlikView files
  
• The Visual Exploration feature
• Scatter Plot - Smart Data Compression
• Data Load Editor Walk-through 
• Creating Drilldown Master Item Dimension
• Data Preparation Capabilities with Qlik Sense
• Qlik Sense Accessibility features
• Understanding the Master Calendar
• Qlik Sense Variables 
• Dashboard Bundle - Variable Input
• Creating a Dimension Group
• Export to PowerPoint
• Converting a QlikView Data Model
• Using Segment Colors with Master Item Measures
• Qlik Sense Custom Themes
• Webinar - Qlik Sense Tips and Tricks - various cool things you may not know about
• Get Familiar with Qlik GeoAnalytics Series
• Qlik Sense GeoAnalytics - Improving Location Accuracy 
• Qlik GeoAnalytics - Qlik Geocoding

Extras

• Creating a Qlik Sense App Thumbnail
• An IoT Perspective with Qlik Sense and some Retrogaming
• Introduction to Qlik Sense Extensions
• Qlik Sense in 60 YouTube Playlist - 60 second videos on a Qlik Sense topic
• Generating sample data with Mockaroo

Hello,

For those not using GeoAnalytics, I have seen some very interesting codes and loops inside qlik using google maps API.

As an alternative I made a python script to use google maps’ URL to parse the html response and bring the coordinates of a list of addresses.

[IMPORTANT] Note: I don’t know if this is against googles’ policies because it is a set of requests without using the API. If you know let us know and discuss it here in this topic. I did this to parse small lists so I think it would not be a good idea to run this if you have thousands of records.

In this short video I explain how it works: https://youtu.be/XuY0O-m3Im4

In short:

Checking google url developer’s guide we have the location search that can be passed through the url (as seen in google’s example).

When we enter this url, google automatically (in most cases – when it recognizes the address) gives back in the html the coordinates of the searched place. We can see that in the source code:

So basically the script identifies the coordinates in this html and store it in a dataframe.

It iterates through the list of addresses and creates the coordinates column to relate to the original database.

Basically this is the conversion:

Then, when we have the coordinates we can plot the points on the map:

The script (It can definitely be improved):

import requests
import bs4
from bs4 import BeautifulSoup as BS
import re
import pandas as pd

headers = {'User-agent': 'Mozilla/5.0'}
##name of file
file = 'companies.csv'
companies = pd.read_csv(file)
addr = companies['Address']
city = companies['City']

address = pd.DataFrame([addr,city])

address = address.T

##number of rows and columns in the dataframe
rows, columns = address.shape

rend = []
rlat =[]
rlong = []

##it will loop in the number of rows
for i in range(rows):
    x = addr.loc[[i]].str.cat(city.loc[[i]], sep='+')
    x = x.to_string()
    x = re.sub(' +',' ',x).replace(' ','+')
    ## The x becomes the address + city and will be passed in the url
    url = 'https://www.google.com/maps/search/?api=1&query='+x

    response = requests.get(url, headers=headers)

    soup = BS(response.text, 'html.parser')
    soup = str(soup)

    ## Finding whatever is between center and amp
    center = soup.find('center')
    amp = soup.find('&amp')

    latlong = soup[center+7:amp]
    ## Breaking the string in the middle to split lat and long
    division = latlong.find('%2C')

    lat = latlong[:division]
    long = latlong[division+3:]
    
    if len(lat) > 20 :
        print('invalid - ',i)
    else:
        print('i: '+str(i)+' | latitude: '+lat+' | longitude: '+long)
        
        rend.append(i)
        rlat.append(lat)
        rlong.append(long)
        
        ## Organizes ID Lat and Long to build the dataframe later on
        result = {'ID':rend,'Latitude':rlat,'Longitude':rlong}

df = pd.DataFrame(result)

print (result)

df.to_excel('coordinates.xls',index=False,encoding='utf-8-sig')

  Hope it helps.

Cheers

From the previous blog/video you have seen the ability to combine multiple data sets together to find my dream HDB home based on flat type, avg resale price, distance to the nearest MRT station and within 1km of a Primary School.

In this document I will show you how to add in an address to lookup the latitude longitude coordinate and then calculate the travel distance by car within 5, 10, 15 mins.  These 2 actions can be initiated when doing the analysis and the results are calculated on the fly.

First, I will create 2 variables vAddress and vAddressPoint, and add in the variable input box and action button.

Next assign the vAddress to the variable input box so that when I type in the address to add a point, the address will be stored inside vAddress.

Next I will use GeoOperation AddressPointLookup to return me the AddressPoint by passing in the vAddress as input.

And then assign this as an action to the action button.  Set the action to set variable value, variable to vAddressPoint and the value to the GeoOperation.

When I click on the action button, the AddressPointLookup function will return the latitude longitude coordinate of the address and stored inside vAddressPoint.  I can then add this point onto the map as a point layer.

I will repeat this for the travel area: create 2 variables vTravelTime and vTravelArea, and add in the variable input dropdown and action button.

Next assign the vTravelTime to the variable dropdown and set the value to 5, 10, 15 and 20 mins.  Remember to set the value to the actual value.

Next I will use GeoOperation TravelAreas to return me the TravelArea by passing in the vTravelTime and vAddressPoint as input.

And then assign this as an action to the action button.  Set the action to set variable value, variable to vTravelArea and the value to the GeoOperation.

When I click on the action button, the TravelAreas function will return the shape of the TravelArea and stored inside vTravelArea.  I can then add this point onto the map as an area layer.

This will be the end result.

Do refer to my other blogs:

Part 1 - Using Qlik Sense to find my dream home

Part 1a - How to build the base dashboard (Video) 

Part 1b - How to build the base dashboard (Guide) 

Part 2 - How to geocode a HDB block and Binning 

Part 3 - How to calculate distance to MRT/LRT 

Part 4 - How to calculate within 1km of Primary School 

Part 5 - How to add a point and travel area 

From the previous blog/video you have seen the ability to combine multiple data sets together to find my dream HDB home based on flat type, avg resale price, distance to the nearest MRT station and within 1km of a Primary School.

In this document I will show you where to get Singapore Primary School information, how to calculate the 1km circle coverage of the school and which HDB unit falls within the 1km circle.

The information for Singapore Primary School is from https://data.gov.sg/dataset/school-directory-and-information

We can use RestfulAPI call to retrieve the information from https://data.gov.sg/api/action/datastore_search?resource_id=ede26d32-01af-4228-b1ed-f05c45a1d8ee&limit=100 

As I only need Primary School so I will add in a filter where mainlevel_code='PRIMARY'.  I have created another column to combine address, postal code and 'Singapore' to be used later to lookup latitude longitude coordinate.  RowNo is also added to create a unique identifier for the Primary School.

Next I will use the GeoOperation AddressPointLookup to geocode the lookup column to the respective latitude longitude coordinate as return as PrimarySchoolPoint.  (For more information on the operation AddressPointLookup do refer to https://help.qlik.com/en-US/geoanalytics/Subsystems/GeoOperationsService/Content/connector/connector...)

This will return a table containing both the Primary School information and the PrimarySchoolPoint.

Next for Singapore Citizen to register for a Primary School in Singapore, distance to the Primary School is taken into consideration.  I will need to find out the 1km circle of all the Primary School if I want to have priority during school admission.

Next I will use the GeoOperation TravelArea to calculate the 1km circle by using the transportation="bird" option.  This will draw a 1km circle regardless of the roads.  If I need to calculate the distance by car I can change the transportation="car".   (For more information on the operation TravelArea  do refer to https://help.qlik.com/en-US/geoanalytics/Subsystems/GeoOperationsService/Content/connector/connector-geoanalytics_reference-Operation.htm) 

This will return a table containing the PrimarySchoolID and the shape of the 1km circle.

I can then use GeoOperation Within to determine which HDB unit falls within the 1km circle of the Primary School.  However, since the ScriptEval cannot take in 2 intable, I will need to store one of them into online storage and be referenced using the URL method.  (For more information on the operation Closest and the use of URL do refer to  https://community.qlik.com/t5/GeoAnalytics/GeoOperations-use-CLOSEST-with-two-loaded-tables/td-p/172...) 

This will return a table containing the PrimarySchoolID and a list of HDB units that falls within the 1km circle.

I can then put all these data into the map.  For e.g., if I select a Primary School, I will be able to see the 1km circle and all the HDB units that falls within.

Do refer to my other blogs:

Part 1 - Using Qlik Sense to find my dream home

Part 1a - How to build the base dashboard (Video) 

Part 1b - How to build the base dashboard (Guide) 

Part 2 - How to geocode a HDB block and Binning 

Part 3 - How to calculate distance to MRT/LRT 

Part 4 - How to calculate within 1km of Primary School 

Part 5 - How to add a point and travel area 

From the previous blog/video you have seen the ability to combine multiple data sets together to find my dream HDB home based on flat type, avg resale price, distance to the nearest MRT station and within 1km of a Primary School.

In this document I will show you where to get the MRT/LRT information and how to calculate the nearest routes from HDB to the closest MRT/LRT stations.

The information for the Singapore MRT/LRT is from https://www.kaggle.com/yxlee245/singapore-train-station-coordinates 

This is a screenshot of the data:

Next I will use the GeoOperation Closest to calculate the shortest distance between the HDB and the newly acquired MRT dataset.  However, since the ScriptEval cannot take in 2 intable, I will need to store one of them into online storage and be referenced using the URL method.  (For more information on the operation Closest and the use of URL do refer to  https://community.qlik.com/t5/GeoAnalytics/GeoOperations-use-CLOSEST-with-two-loaded-tables/td-p/1721665) 

I also get to know that if I use dropbox I will need to change the prefix www. to dl. and suffix dl=0 to dl=1 .  (For more information on this do refer to https://community.qlik.com/t5/GeoAnalytics/GeoAnalytics-Shapefile-issue/td-p/1456366)

While running the GeoOperation I faced another issue, the dataset is too big to run.   I will need to split the dataset into chunks of 100 and run it individual.  (For more information on how to split up the data do refer to https://community.qlik.com/t5/Knowledge/Splitting-up-GeoAnalytics-connector-operations/ta-p/1715346 )

This will return a table containing the mapping of MRTID and HDBID, the distance and routes between the 2 points.

I can then use the routes to create a line layer in the map to show the path between the 2 points

Do refer to my other blogs:

Part 1 - Using Qlik Sense to find my dream home

Part 1a - How to build the base dashboard (Video) 

Part 1b - How to build the base dashboard (Guide) 

Part 2 - How to geocode a HDB block and Binning 

Part 3 - How to calculate distance to MRT/LRT 

Part 4 - How to calculate within 1km of Primary School 

Part 5 - How to add a point and travel area