Hi community,

I assume we all, once in a while, faced this question : "Is it possible to store a table from QlikSense into our database?"

Since QlikSense is not made to write into a database but rather select from it, the idea is to create a procedure/function within the sql database, that will populate the table when called from the Qlik Sense script.

So, without further due, the answer is : YES !

I'll now share with you how I did to implement this:

1) The database should allow the creation of Procedures/functions.

For this example, I will be using postgresql.

a) create the table :

The first step is to create the table within the sql database :

example:

CREATE TABLE alerts

(

    alertname character varying(100) ,

    desk character varying(100)

)

b) create the Function:

The function writeback will be responsible of populating the table alerts:

CREATE OR REPLACE FUNCTION writeback(

alertname character varying,

desk character varying)

    BEGIN

      INSERT INTO alerts VALUES (alertname,desk);

    END;

c) Connect to the postgresql database and call the function:

Now, in the Qlik Sense script; what we want to do is store the table QlikSenseAlerts into the table alerts created within the Postgres db

QlikSenseAlerts:

//  1) the idea is to add rowNo() field to go through all the lines of our QlikSenseAlerts table

alerts:

load *,RowNo() as row inline [

Alert , Desk

alert1, desk1

alert2 ,desk1

alert3 , desk2

alert4, desk4

alert5, desk5

alert6, desk6

alert7, desk7

alert8, desk8

alert9, desk9

alert10, desk10

alert11, desk11

alert12, desk12

alert13, desk13

alert14, desk14

alert15, desk15

alert16, desk16

alert17, desk17

alert18, desk18

alert19,desk19

alert20,desk20

];

//2) connect to our database

LIB CONNECT TO 'PostgreSQL_localhost';

//3) line by line, store the value of each column in a variable

for a=0 to FieldValueCount('row')-1

Let vAlert = Peek('Alert',$(a), 'alerts');

Let vDesk = Peek('Desk',$(a),'alerts');

//4) call the function writeback that will store our 2 variables into the sql table; each variable contain the columns'value of the 1st row

SELECT public.writeback('$(vAlert)','$(vDesk)');

//5) do the same thing for all the lines

next a;

Result:

Table alerts in the sql table before reloading qlik sense script:

We, run the script:

Table alerts in the sql table after reloading qlik sense script:

Hope you'll like it

Omar BEN SALEM.

Update, the example app now have the drawing as a background.

Floor plans and cad drawings can be useful, for instance in production dashboards, to visualize status and flows in a factory. However cad drawings are in many cases a mess to work with.

Here's a simple, pragmatic approach to digitize rather than import the actual drawing. The major issue with most CAD drawings is lack of structure, details without meaning and the objects on the right level are missing. It’s not the format or alignment that is the problem. In many cases it's better to use the drawing as a input to produce the polygons and information needed.

In this tutorial I use QGIS for digitizing and out put to a format that is easy to import to QGA. In this example the drawing is digitized in a local reference system, ie no alignment is made with the rest of the world.

Step-by-step guide

1. Install QGA basic, Plus not needed if Dropbox et al is used.
2. Install QGIS (https://www.qgis.org)
3. Get a picture of the floor plan in an image format like PNG or JPG.
4. Drag and drop the image to the layers panel of QGIS. Choose a meter based Coordinate Reference System, for instance EPSG:3857 is a good choice.
   
   
   
   
5. Layer→ Create Layer→ New Shapefile Layer... Choose Polygon, same CRS, 3857. Add "Name" text field for convenience.
   
   
   
   
6. Save layer in a folder. Preferable in dropbox folder.
   
   
   
   
7. Right click on the new polygon layer and "Toggle editing" to start editing.
   
   
   
8. Start adding features (Ctrl +)
   
   
   
9. Click on the edges of the desired polygon, set id and name. Use node tool if fine tuning of edges is necessary.
   
   
   
10. When done "Toggle editing"to save.
    
    
    
11. Zip the shapefile to an archive and get the dropbox url. (replace www with dl to get direct link)
    
    
    
12. Create a new Sense app and use the drop url in the Load operator of QGA connector. CRS: EPSG:3857. Note change url from 'www' to 'dl' for direct file access in dropbox.
    
    
    

Example app

The example app features sites and a drawing visible when one site is selected.

If you want to add and image background, here's how to scale and translate the image, in my example:

Tip 1: get a white background use ='[[ [-180, -80], [-180,  80], [ 180,  80], [ 180, -80], [-180, -80] ]]' for location, set color to white and make the layer non selectable and not part of autozoom.

Tip 2, Use a bubble layer with coordinates for different sites, when one site is selected turn on drawing and the white background.

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.

Monitoring current performance and availability of Qlik Sense site is not out-of-the-box feature, but can be easily achieved with native REST connector and requesting engine\healthcheck JSON API.

There are other tools and approaches, but since I already have the best BI tool available 😉, I found it reasonable to use it. All that is necessary is just:

1. download and import the attached app
2. create REST Data Connections and adjust the settings in the script
3. schedule app reload

This Qlik Sense app:

• loads engine\healthcheck JSON data from multiple nodes
• parses and consolidates it to two tables
• stores them to QVDs with all previous snapshots
• visualizes key metrics of engine utilization and availability in time

It's recommended to deploy and schedule this app on DEV or TEST site, pointing to all production nodes. Otherwise, there will be no data loaded in case the production engine (or the whole server) crashes.
(just FYI - DEV and TEST sites are included in the professional license subscription)

It's also recommended to read the official help site info about the engine API before you deploy this app.

After importing the app to a suitable site, follow these steps:

1. Decide on target folder for result QVD files, where parsed snapshot of JSON data will be stored. Create a folder Data Connection (if it doesn’t already exist) and set its Lib name to vQVDpath variable in Settings script section.

2. Create a REST Data Connection to each node:

URL: https://<server domain>/engine/healthcheck OR https://<node IP address>:4747/engine/healthcheck

Authentication Schema: Windows NTLM
User name & password: type root admin or service user credentials
check "Skip server certificate validation" if there is no valid certificate on target node

Query headers:
User-Agent: Windows
Cache-Control: no-cache

3. Adjust the INLINE table in the Settings section of the script according to your site

node - name of the node that will be shown for app’s user
restConnection - name of the REST connection that points to node’s engine/healthcheck
RAM - amount of node's memory in GB
CPU - number of node's logical processors

4. Schedule a reload task. The recommended app reload frequency is 5 minutes or less (tested reload duration on the 4-node site is 12 seconds). The script will return "N/A" in [isSaturated] field when the engine is not responding.

The current roadmap includes - script for email notifications, possibility to load only N days back, mapping app name to IDs, node comparisons.

PLEASE! If you make adjustments or additions to the script or front-end visualizations, please share them back on the Qlik Community.

Happy Qliking 🙂
-RADO-

This post explains how to connect to an Oracle database using the OLE DB connector and a tnsnames.ora file.

• Download the latest version of the Oracle Database Client for Windows (x64). You can use this link or Google it.
• Unzip the file and double-click on setup.exe.
• Select custom installation and follow the installation instructions.

• Choose an intallation folder (E.g., C:\oracle).

• Select components Oracle Net, Oracle ODBC Driver and OLE DB.

• Click on Next and finish the installation.
• Type environment variables in Windows' search bar and open the app. Then, click on Environment variables.

• Create a user variable named TNS_ADMIN and set its value to the path of the folder where your tnsnames.ora file is located.

• Open Qlik Sense and create a new OLE DB connection. In the Data Source textbox, type the name of the connection as it is in the tnsnames.ora file.

Examples for QlikView with Qlik GeoAnalytics.

Binning
This app shows an example of how the Binning feature can be used to create rectangular or hexagonal bins over Sweden. It also shows a drill-down hierarchy with two levels of rectangular bins and a point level. 

Chart Layer example
An example of how the Chart Layer can be used to plot data points on graphs (both bars and pies) using two dimensions. This example shows how the average house prices in Sweden have developed over time.

Polylines and Symbols example
An example showing how to use the Line Layer to create Polylines and the Bubble Layer to create Symbols from image URLs. 

Line Layer example – Storms
Example of Line Layer using Storm data to visualize historical storms. 

EU statistics example
Uses: Bubble Layer with latitude and longitude measures, bubble size and color by measure, Area Layer with location ID for country codes, area color by measure, transparency.

Drill down example
A drill down example with three different ways of using drill down data to visualize data. Sheet 1: Drill down based on State - County - Zip Sheet 2: Drill down based on Zip. 

Heatmap example
Uses: Heatmap Layer, drill down with layers active in different levels, Bubble Layer with visibility restricted to resolution limits. 

US flight delay example
This example shows how Line Layer, among others, can be used to show routes between different positions and include average departure/arrival delays in the appearance of the lines.

The examples requires v 12 or later of QlikView.

Sense Map chart

Example adding a WMS background

1. Add in Content Security Policy (Qlik SaaS)
Origin: nowcoast.noaa.gov Directive : connect-src and img-src
2. Add Map chart
3. Add background layer WMS
url: https://nowcoast.noaa.gov/arcgis/services/nowcoast/radar_meteo_imagery_nexrad_time/MapServer/WMSServer
version:1.3.0
crs: EPSG:3857, transparent, png, layer: Image

Note, use CRS 3857 if the base map has the Mercator projection (default).
Pick CRS 4326 if the base map has the Adaptive projection.

Link to documentation and example.

(QGA Extension map, old kept for reference)

Start by figure out the correct parameters. In this case the source is a WMS with data from the Dutch city Zaanstad.

Note, the urls below has chnaged and not working anymore, principles still applies)

1. Easiest way to pick up the WMS source and at the same time verify that it works is through a demo page,
   for example http://geo.zaanstad.nl/mapproxy/demo/?srs=EPSG%3A28992&format=image%2Fpng&wms_layer=Zaanstad
2. Select CRS (coordinate system), 4326 for use together with the default adaptive basemap or 3857 with the defualt basemap. Right click on the map image and "Open image in new tab..." . Copy the image url:
   http://geo.zaanstad.nl/mapproxy/service?LAYERS=Zaanstad&FORMAT=image%2Fpng&SRS=EPSG%3A28992&EXCEPTIONS=application%2Fvnd.ogc.se_inimage&TRANSPARENT=TRUE&SERVICE=WMS&VERSION=1.1.1&REQUEST=GetMap&STYLES=&BBOX=77018.328125,474244.265625,153485.671875,512224.734375&WIDTH=1208&HEIGHT=600
3. Remove the following parameters (these are filled in by the map) : BBOX, WIDTH, HEIGHT, VERSION, SRS.
   That produces the url to use: http://geo.zaanstad.nl/mapproxy/service?LAYERS=Zaanstad&FORMAT=image%2Fpng&EXCEPTIONS=application%2Fvnd.ogc.se_inimage&TRANSPARENT=TRUE&SERVICE=WMS&REQUEST=GetMap&STYLES=
4. Fill in CRS and VERSION (in my example EPSG:3857 and 1.1.1 check the WMS url for the version).
   
   Pro tip, "Load via Server" is practical to use if the the WMS doesn't allow CORS.
   
   Pro tip 2, run Sense in the browser (http://localhost:4848/hub/my/work) and use the dev console (F12), good to spot errors from the WMS server.
   
   

{
  "appId": "58f563b6-ccda-45b0-926c-e2992ca8b470",
  "recurrence": [
    "RRULE:FREQ=MINUTELY;INTERVAL=15"
  ],
  "startDateTime": "2022-03-23T00:00:00",
  "timeZone": "America/New_York",
  "type": "scheduled_reload"
}

This guide was written for use with the Windows PowerShell program. While PowerShell can be run on non Windows systems, use of bash techniques for parsing json (e.g. jq) is possible but not covered in this guide.

Examples of advanced schedules:

Reload a specific app every 15 minutes

# Specify the appId
$appId = '<myAppId>'
# Define the rrule
$schema = 'RRULE:FREQ=MINUTELY;INTERVAL=15'
$startDate = ((Get-Date)).ToString("yyyy-MM-ddT00:00:00") # Get the current date and convert to required format
# Get the app's reload tasks
$reloadTask = qlik reload-task ls --appId $($appId) | ConvertFrom-Json
# Delete the associated reload task, if present
if($reloadTask.data.Length -gt 0) {
    $null = qlik reload-task rm $($reloadTask.data.id)
}
# Create the task
$null = qlik reload-task create --appId $appId --recurrence $schema --startDateTime $startDate --timeZone "America/New_York" --type "scheduled_reload"

Reload a specific app every hour on Thursday

# Specify the appId
$appId = '<myAppId>'
# Define the rrule
$schema = 'RRULE:FREQ=HOURLY;INTERVAL=1;BYDAY=TH'
$startDate = ((Get-Date)).ToString("yyyy-MM-ddT00:00:00") # Get the current date and convert to required format
# Get the app's reload tasks
$reloadTask = qlik reload-task ls --appId $($appId) | ConvertFrom-Json
# Delete the associated reload task, if present
if($reloadTask.data.Length -gt 0) {
    $null = qlik reload-task rm $($reloadTask.data.id)
}
# Create the task
$null = qlik reload-task create --appId $appId --recurrence $schema --startDateTime $startDate --timeZone "America/New_York" --type "scheduled_reload"

Reload a specific app on the 2nd Monday of the month

# Specify the appId
$appId = '<myAppId>'
# Define the rrule
$schema = 'RRULE:FREQ=MONTHLY;WKST=MO;BYDAY=MO;BYSETPOS=2'
$startDate = ((Get-Date)).ToString("yyyy-MM-ddT00:00:00") # Get the current date and convert to required format
# Get the app's reload tasks
$reloadTask = qlik reload-task ls --appId $($appId) | ConvertFrom-Json
# Delete the associated reload task, if present
if($reloadTask.data.Length -gt 0) {
    $null = qlik reload-task rm $($reloadTask.data.id)
}
# Create the task
$null = qlik reload-task create --appId $appId --recurrence $schema --startDateTime $startDate --timeZone "America/New_York" --type "scheduled_reload"

• Note: The hour  which the reload is executed with daily / monthly / yearly is determined by the startDateTime value. In this example, the value is set to midnight, adjustment to align with desired business or batch hour is encouraged.

Today, I'd like to share with you our current App backup solution using Powershell and Qlik-Cli.

As you know, a production environment needs backups and Qlik Sense Enterprise does not offer such a thing by default so I decided to build a solution.

Using Qlik-Cli for Windows, I put in place a solution that will not only backup my published and unpublished apps but will also create a folder structure to match your streams.

The unpublished apps are backed up inside the "My Work" folder using the app owner's name as a directory name. This allows to easily find the app owner when performing a search in the folders.

Square brackets in the App's name will be removed because you cannot create a file with these characters in Windows.

The script is scheduled using the Windows Task Scheduler with the following command:

Here's the code:

$ErrorActionPreference = "Stop"

Try
{
# set export drive and directory
D:
cd D:\QlikBackups

# connect to the Qlik Server. Replace <ServerName> with your Qlik Sense hostname
Connect-Qlik -ComputerName <ServerName> -UseDefaultCredentials -TrustAllCerts

# Export each apps from the previously defined streams to qvf
Get-QlikStream | % {New-Item -Name ".\$($_.name)" -Force -ItemType 'Directory'} | foreach {Get-QlikApp -filter "stream.name eq '$($_.name)'"} | foreach {Export-QlikApp -skipdata -id $_.id -filename "$($_.stream.name)\$($_.name -replace '[[\]]','').qvf"}

# set the export folder to the My Work area for unpublished apps
cd "D:\QlikBackups\My Work"

# Create a folder structure for all the app owners
Get-QlikApp -full -filter "published eq False" | % {New-Item -Name ".\$($_.owner.userId)" -Force -ItemType 'Directory'} 

# Export each app into their folder
Get-QlikApp -full -filter "published eq False" | foreach {Export-QlikApp -skipdata -id $_.id -filename "$($_.owner.userId)\$($_.name -replace '[[\]]','').qvf"}
}
Catch # In case of error, send an email to the Qlik administrators
{
    $ErrorMessage = $_.Exception.Message
    $FailedItem = $_.Exception.ItemName
    $Time=Get-Date
    "Error: $FailedItem Message: $ErrorMessage Time: $Time" | out-file d:\log\QlikBackup.log -append
    
    $FailMailParams = @{
To = 'qlikadmin@MyCompany.com'
        From = 'MyQlikServer@MyCompany.com'
        Port = '25'
        SmtpServer = 'smtprelay.MyCompany.com'
        Subject = 'MyQlikServer Backup Failure'
        Body = "The backup failed. The error message was: $ErrorMessage"
        }

         Send-MailMessage @FailMailParams
}

My next steps are:

• Add timestamps to the filename
• Keep the most recent copy and Archive the rest.
• Add qvf file retention

Video explanation here: https://www.youtube.com/watch?v=8hDJlc6p5lE

Something that I come across on a regular basis is customers asking about dates in Qlik.  Fiscal calendars, complete calendars, creating current year to date vs last year to date, and matching dates between two date fields are all things that have come up.

The answer to all of these is to create a master calendar.  That said, creating a master calendar is not always the easiest thing to do or learn from online posts.

I have attached 'Master Calendar.zip' that contains two .txt files designed to automatically create a master calendar from your data. 

In 'Master Calendar Variables.txt' you can paste the contents after your current load script in your app and fill in the variables:

- Table name that contains the date

- Whether you want to create a fiscal calendar and which month that fiscal calendar should start in

- Your Date field(s) - if you insert two date fields, it will perform an IntervalMatch

In 'Master Calendar Script.txt' you can either paste the contents below the Variables or use an Include statement  below the Variables to call on the script.

That's it!  Hope this helps!

I have configured the SAML as suggested in the documentation. And when i tried to access the Qlik Sense URL with SAML as suggested in the documentation

https://[node]/[prefix]/

the URL is getting redirected to the windows authentication like this https://server:port/windows_authentication/?targetId=11234 

and prompting for windows authentication. And it works fine.

(a) How to validate it is authenticated through SAML. Is there any logs associated with it ? Is it expected to prompt for windows authentication and validated through SAML.

Is there any specific setting has to be changed or additional coding required apart from the QMC settings

Brind,

No, you shouldn't be redirected to a windows auth through the browser.  How are you configuring SAML?  Put another way, what identity management solution are you using as an identity provider?

Can you send a screen shot of your virtual proxy configuration?

jg

It is ping federate

Virtual proxy configuration as follows

Identification

Description: SSO integration

Prefix : SSO

Session inactivity Timeout(Minutes) :30

Session Cookie header name : X-SSO-Session

Authentication

Anonymous access mode: Allow anonymous user

Authentication method: SAML

SAML host URI : https://a1234d.abc.com. 

(--------------https://a1234d.abc.com/qmc/ and https://a1234d.abc.com/hub---------------)

SAML entitity Id : ssoqliksense

SAML Medtadata Idp : uploaded the metadata

SAML attribute for userid : {id }

SAML attribute for user active-directory:{id}

And linked to default proxy. Let me know if you need any additional information

Ok, have you configured a virtual proxy in Qlik Sense to talk to PingFederate with PFs idp metadata and then performed similar configuration on PF with Qlik Sense SP metadata?

For example, here is a screenshot of my SAML config for Salesforce on my Qlik Sense server.

See the  SAML Metadata IdP?  Have you uploaded the PF metadata there?

For config examples, here is a set of videos for Salesforce and ADFS

I wonder if Allow anonymous user is tripping it up.  What happens if you set to no anonymous users?  In addition, have you set up PF with the SP metadata from Qlik Sense?

And to clarify, the userid attribute should be the attribute name or the schema reference url, and the user directory if static uses square brackets and not curly braces.

jg

Thanks Jg

Do i need do the same for the SAML attribute mapping. Brackets for both SAML and QlikSense attributes

SAML Attribute mapping

SAML attribute  QlikSense Attribute

[id]                     [id]

If they are static (meaning that you aren't using an OID or schema definition) you need the brackets.  The SAML attribute and the Qlik Sense attribute do not need to have the same name.

jg

Thanks jg

When i try the url servername/hub/saml .it redirects to windows authentication. If i try with servername/prefix i am getting the error as No available qliksense engine was found refresh your browser or contact your administrator.

Is there any port has to changed or any log files. how to look for request and response flow. I tried with fiddler didnt get anything.

ok, so with all virtual proxies (ticketing, header, session, or SAML) the prefix is mandatory or you are going to go the central proxy virtual proxy which is going to pop up windows authentication.  So you do need to do this:

https://servername/virtualProxyprefix/hub

As for ports, no ports should have to change.

Logs are located in c:\programdata\qlik\sense\logs\proxy\trace and the audit proxy log.

servername/hub/saml is not valid.

Try the servername/virtualproxy/hub and see if you get redirected to PF.  Check the logs and if you want attach them here and I can take a look.

jg

Is there any way i can send the log only to you?

Hi,

I am trying to SAML-authenticate Qlik Sense with Google as my identity provider and have followed the instructional video and your instructions from this thread. The error I am getting is "The user cannot be authenticated by the SAML response through the following proxy: QlikSense"

QlikSense is my virtual proxy. Here's the configuration

Identification

Description: SSSO authentication with Google

Prefix : sso

Session inactivity Timeout(Minutes) :30

Session Cookie header name : X-Qlik-Session-SSO

Authentication

Anonymous access mode: No anonymous user

Authentication method: SAML

SAML host URI : https://testdashboard.irri.org

SAML entitity Id : sso

SAML Medtadata Idp : uploaded the metadata in QMC

SAML attribute for userid : email

SAML attribute for user active-directory: [GOOGLE]

have linked to default proxy Central.

The link https://testdashboard.irri.org/sso produce the error i mentioned above. The Google part seemed to be working as it passes through Google authentication:

Google login window:

2 factor authentication

then the error in Qlik

Any idea where to look at to fix this?

Thanks!

Eric

I figured it out and it's working like a charm!

Anyone who wants to implement the same in their organization, send me an email‌.

Cheers

eric

Google-QlikSAMLSSO.pdf

Hi All,

I have integrated SAML with 1 proxy node for PF IDP which works fine. Now I have added one more proxy node and I have linked the same node in SAML virtual proxy. When I try to access https://localhost/saml/hub I get below error.

Do I need to anything on top of this?

Thanks for your help in advance.

Please help!!

Immediately after seeing this error, look at the log file ????_audit_proxy.txt (found under

c:\programdata\qlik\sense\logs\proxy\trace\) and check the last few entries.

The log file will tell you why the authentication is failing. It could be your ID provider rejecting the request. Find out if something was changed in your ID provider side. If you have changed/updated your security certificate recently, you may have to send your metadata again to the ID provider and get it imported there.

This document was generated from the following discussion: QlikSense SAML

Link table example 3

Script:

Sales:
LOAD
Year & '_' & Month & '_' & Branch & '_' & [Item Number] as SalesKey,
//"Year",
//"Month",
// Branch,
//"Item Number",
"Customer Number",
"Invoice Number",
"Order Number",
"Salesman Number",
"Invoice date",
"Sales Amount",
"Sales Qty",
"Cost Amount",
"Margin Amount",
"Unit of Measure"
FROM [lib://DataFiles/LinktableExample.xlsx]
(ooxml, embedded labels, table is Sales);

Inventory:
LOAD
Branch & '_' & [Item Number] as InvKey,
//Branch,
//"Item Number",
"On Hand Qty"
FROM [lib://DataFiles/LinktableExample.xlsx]
(ooxml, embedded labels, table is Inventory);

Purchasing:
LOAD
Year & '_' & Month & '_' & Branch & '_' & [Item Number] as POKey,
//"Year",
//"Month",
//Branch,
// "Item Number",
"PO Number",
"Req Delv Date",
"PO Amount",
"Ordered Qty"
FROM [lib://DataFiles/LinktableExample.xlsx]
(ooxml, embedded labels, table is Purchasing);

/* Create Link Tables */

//Sales
LinkTable:
Load DISTINCT
Year & '_' & Month & '_' & Branch & '_' & [Item Number] as SalesKey,
Branch & '_' & [Item Number] as InvKey,
Year & '_' & Month & '_' & Branch & '_' & [Item Number] as POKey,
Year,
Month,
[Branch],
[Item Number]
FROM [lib://DataFiles/LinktableExample.xlsx]
(ooxml, embedded labels, table is Sales);

//Inventory
LinkTable:
Load DISTINCT
Null() & '_' & Null() & Branch & '_' & [Item Number] as SalesKey,
Branch & '_' & [Item Number] as InvKey,
Null() & '_' & Null() & Branch & '_' & [Item Number] as POKey,
Null() as Year,
Null() as Month,
[Branch],
[Item Number]
FROM [lib://DataFiles/LinktableExample.xlsx]
(ooxml, embedded labels, table is Inventory);

//Purchasing
LinkTable:
Load DISTINCT
Year & '_' & Month & '_' & Branch & '_' & [Item Number] as SalesKey,
Branch & '_' & [Item Number] as InvKey,
Year & '_' & Month & '_' & Branch & '_' & [Item Number] as POKey,
Year,
Month,
[Branch],
[Item Number]
FROM [lib://DataFiles/LinktableExample.xlsx]
(ooxml, embedded labels, table is Purchasing);

Hope this help

Vikas

There´s a common request to any Qlik Sense Enterprise Administrator:

"Is there any way to be informed by e-mail when a reload task fails?"  Yes, there is!

Before that, let´s have some technical small talk (you can jump to session "Creating your config xml" if you want)

Qlik Sense Enterprise relies on log4Net a very mature Apache Project  that is very reliable and powerful. It, besides a lot of features  , has the concept of Appender. That concept means that the application being logged (Qlik Sense in our case) does not to take care where the log is being stored, log4net config files handles this.

You can change a config file and choose if your log are meant to be stored into a database, a flat file, a hadoop data lake and you can even send an e-mail for each error message.   Qlik Sense Enterprise help to Appenders

1 - Creating your config xml

Pick your favorite text editor and write your config file according this https://logging.apache.org/log4net/release/config-examples.html

No, wait!

I´m posting here a functional config file, you can copy and paste it.

I´m supposing you´re going to use Gmail as the SMTP but is up to you chose any working SMTP that you want

<?xml version="1.0"?>
<configuration>
    <!-- Mail appender-->
    <appender name="MailAppender" type="log4net.Appender.SmtpAppender">
        <filter type="log4net.Filter.LevelRangeFilter">
            <param name="levelMin" value="WARN" />
            <param name="levelMax" value="ERROR" />
        </filter>
        <filter type="log4net.Filter.DenyAllFilter" />
        <evaluator type="log4net.Core.LevelEvaluator">
            <param name="threshold" value="ERROR"/>
        </evaluator>

        <param name="to" value="admin@domain.com" /> <!-- please use your admin e-mail address here -->
        <param name="cc" value="someone.else@domain.com" />
        <param name="from" value="yourgmailaccount@gmail.com" /> <!-- you´re supposed to change this -->
        <param name="subject" value="Qlik Sense Task Fail!!!" />
        <param name="smtpHost" value="smtp.gmail.com" />
        <param name="port" value="587" />
        <param name="EnableSsl" value="true" />
        <param name="Authentication" value="Basic" />
        <param name="username" value="yourgmailaccount@gmail.com" /> <!-- you´re supposed to change this -->
        <param name="password" value="yourpasswordforGmail" /> <!-- you´re supposed to change this -->
        <param name="bufferSize" value="0" />
        <!-- Set this to 0 to make sure an email is sent on every error -->
        <param name="lossy" value="true" />
        <layout type="log4net.Layout.PatternLayout">7
<!-- You can change below code if you want to pretify your e-mail message -->
            <param name="conversionPattern" value="%newline%date %-5level %newline%property{TaskName}%newline%property{AppName}%newline%message%newline%newline%newline" />
        </layout>
    </appender>
    <!--Send mail on task failure-->
    <logger name="System.Scheduler.Scheduler.Slave.Tasks.ReloadTask"> <!-- Filter what kind of exceptionname is beeing captured -->
        <appender-ref ref="MailAppender" />
    </logger>
</configuration>

2 - Saving your config xml

You must save your file with LocalLogConfig.xml name and under C:\ProgramData\Qlik\Sense\Scheduler (at least until version April2018). Anything different from this will cause your configuration not work.

3 - Restarting your Scheduler Service

Check if any task is running before restarting the service!!

4 - Test

• Create an app and create an very "silly always fail" script like Store notable into [lib://neverland/notable.qvd](qvd)
• Schedule your app as usual (under QMC/Apps/[your app]/More options button/Create New Reload Task

    (https://help.qlik.com/en-US/sense/April2018/Subsystems/ManagementConsole/Content/create-reload-tasks.htm)

Get a cup of coffee and wait for your task fail and your alert e-mail arrives at your inbox.

This blog post was written with fundamental contribution of ile‌ that sent me a functional config file

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/

Document Chaining in Qlik Sense made simple.

As we all know in QlikView document chaining is drilling down from one application to another automatically transferring selections.

Qlik Sense does not have document chaining yet,but we can do this in two ways.

1. We can make a extension that will do every thing for us it has all the property set (i'll leave my link to GITHUB for the same).
2. Using a simple method/function GetCurrentSelections() in Qlik Sense

To make this possible i'll use the second method to get this action in your qlik sense application

1. We are going to use Sheet Navigation + Actions for Qlik Sense
2. Under Navigation Behavior -> Navigation Action -> Open website (uncheck Open in same window option)

    Now open to make it happen open a application sheet in chrome browser, copy the url and add the below code

    ='Your_URL' & '/select/' & GetCurrentSelections('/select/','/',';')

So it will look somewhat like this

='http://exampleQsDev.com/sense/app/aak0-aa55-4ahwe/sheet/f0a33/state/analysis' & '/select/' & GetCurrentSelections('/select/','/',';')

NOW ADD THIS TO THE EXPRESSION OF Website Uri.

when you do your selection the rest of url is made using GetCurrentSelections() function

4th parameter for the GetCurrentSelections('/select/','/',';',200)

Visit : GetCurrentSelections to know more

I have noticed if selection is more than 5 or 6 it will not generate the link as we need for performing the actions.

I am planing to make a proper extension to make this happen but till then this is what we have.

Thank You,

Ajay Kakkar

ajaykakkar93@gmail.com

Qlik Sense Integration, Extensions and APIs‌

Hi All,

As we all know Qlik Analytical Platform is quite new in market. People are still in confusion as what QAP is, how it is different than Qlik Sense Enterprise. How to configure QAP Server, Manage QAP and especially what exactly the output of QAP. There are lots of tickets which give you chunk of information like difference between QAP and Qlik Sense Enterprise, how to reload on QAP server when there is no HUB under QAP but there is nowhere how to configure a QAP solution from scratch to end with details.

This document guide you to know what QAP is , how it is different than QSE, setup QAP Server, create and publish Mashup(Embed Analytics ) a, manage QAP Objects and reload application on QAP when there is no HUB at all.

Question: Why Qlik Sense client needs QAP ?

Answer:

1. QAP is for external users simply which doesn't need self service or QSE capabilities.
2. DMZ network where external users are not allowed to access application from internal network.
3. Client doesn't want to buy hundreds of Qlik Sense tokens for such users which don't need deep analysis.
4. No certain details of Qlik Sense end users like users count, user details(anonymous).
5. Embed Qlik Sense objects in client’s web portal like any news paper, public portal etc.

QAP setup needs knowledge of Qlik Sense Enterprise (server configuration with security rules) and web developer  skills (css and java script and basic HTML) who will help you to embed Qlik Object to web portal.

I have attached one QAP product presentation, you may start with it then below tutorial video so you have enough functional knowledge of product. 

Important  video tutorial of QAP:

Let’s start from beginning:

Difference b/w Qlik Sense Enterprise and Qlik Analytical Platform:

In principle QAP and QSE is same but in general QAP is subset of QSE means QSE without HUB(No Self Service). QAP is a solution of embed analytics. If no self service is there in QAP it means no "create things" but still developers can create visuals by APIs.

Https://HostName/hub you can view the application but when you open it, it will give you an error you don't have access pass, it means you can't view any apps object on hub.

QAP enables you to display sheet or charts into your website.

Commercial difference, QAP has core based license model unlike QSE which has token based license model (It means no user and login passes, which means you just need to grant the access to users or authorized users to the content which is embedded in web portal )

Recently Qlik introduced core bases license for self service too.

QAP is intended for using our APIs (Extensions, Mashups etc.) to deploy custom solutions.

The Qlik Sense client is disabled in QAP (when you will login to hub you would view the streams and apps but when you will click on any app, it will give you error of access passes).

It has same installation package as QES but applying a QAP license key will disable hub it means you can view the hub, streams list, and application icons but apps objects like sheet, charts, measures and dimensions will not open on HUB. It will give you an error, you hve no access passes.

QAP license policy: when a client buys a QAP token, it comes with minimum 4 cores, it means no matter how many cores or virtual processors are available on deployment server, it will only consume or utilize 4 core among all available cores. So, let’s say client's server has 8 cores or 16 logical processors on Qlik Sense Enterprise server and your application size is 1 GB and you are planning to use same application on QAP, So when you import this application to QAP, It will not work as it works on QSE sever cause QAP(as per the license capacity) will consume only 4 cores out 8 cores and your 1 GB application need 8 cores to perform or load objects like QSE.

Note: Do not be confused, what will happen when you will activate the Qlik Server with QAP license, you will get everything same i.e. QMC as Qlik Sense Enterprise but there will be no Tokens means you can't create any user or login pass which we use to authorize users to access the Qlik Sense content.

To implement QAP server, configured it security with custom properties so you would manage security of data for users with

https://community.qlik.com/docs/DOC-18066

These 9 points will create a stream, import the application and publish the application to the newly created stream and grant access to application and app objects.

Try to use Dev-Hub to view the object i.e single configurator.

Now, we will add one more new security rule which will grant all the users to access the APIs like extensions, mashups, Widget, Single configurator.

***You may create one new custom property QAPExtension with resource type Extension and User and the create a new security rule with Extension Template and write a security rule as:

((User.@QAPExtension=resource.@QAPExtension))

Then go to the extension by Click one start => Extensions => select your extension(Mashup) => go to custom properties and click on the Space bar and select the value you have given while creating Custom properties do the same exercise for users too .

Now, we don’t need to create any custom property because QAP is for all users (no limit) above activity started with *** is when you created a mashup and then you need to provide access of some particular Extension(Mashup) to any specific users.

So, for QAP, write a simple security rule as with template Extension:

((user.name="*" or resource.name="*"))

Which means all user will access the all the APIs (extensions, mashups, Widget, Single configurator).

Often, People ask what if my client hasn't any web portal so in that case above Mashup solution will work, Mashup is kind of website(.html file) or web solution.

This is best with Qlik comes Sense as we can create a HTML page with our Qlik Objects so easily(Basic HTML and JS  and CSS knowledge requires).

Now point comes, if all user will come how to secure the data, here comes the real QAP solution,

Generally what QAP says, create an application on Qlik Enterprise or Desktop, import on the QAP server and publish its Objects to client website (Mashup is a HTML page but it’s not a web portal).

So above, you have imported an application, created stream, published application, granted access to all users. What is left now, go to single configurator, select one object, there will be two links come on top side, Iframe and URL, Copy anyone and go to the website page where you need to show these objects and paste it(Please take the help of web developer because every object needs a proper space and alignment).

If any user who is not authorized to view Qlik objects but has rights to login web portal where Qlik Sense objects embedded then it will show an error, so you may ask your web designer to handle this case and put any image instead of INVALID OBJECT message from Qlik Sense.

Now when any user will come it will see an error, access denied or ask for credentials , so You may configure the single sign on here ,Please refer the very useful document for single sign on :

https://community.qlik.com/community/qlik-sense/support/blog/2016/05/11/qlik-sense-ticket-based-authentication-step-by-step-configuration-example

Last Point, how to manage the QAP server application. I mean, reload and all. So, You can’t open any application on QAP HUB and there will be no script editor so how to reload if you have imported the application from Qlik sense enterprise:

If you have imported the apps from another Qlik Sense Server the required data connections are unfortunately not imported together with the qvf-file. That is probably why the tasks fail since the data connections are missing. Check in QMC > Data Connections if they are there and if they are properly set up.

Please refer this article to reload data in QAP: https://qliksupport.force.com/articles/000025412

” When you import an app developed on Qlik Sense Desktop, existing data connections are imported to the QMC. When you export an app from a server, existing data connections are not exported with the app.”

Since the QAP is a Qlik Sense-server without the "hub" and aimed for integration purposes only you have to find a way to import the data connections into the QAP QMC for reloads to work. One way could be to create an empty app in Qlik Sense Desktop with the required data connections and then import this qvf into the QAP. Then you can manage and set up the imported data connections properly in the QAP QMC.

Reach to me for further assistance, Please drop an email to kumar.rohit1609@gmail.com

Add me on Linked In : https://in.linkedin.com/pub/rohit-kumar/2b/a15/67b

Add yourself on Qlik intellectuals page on Facebook at https://www.facebook.com/QlikIntellectuals

Please like this page or add as bookmark and add your ratings so my I will know how effective my solution is for Qlik Users.

You may add your queries below so I would come up with solution. I will try to keep update this document with more powerful inputs. Your queries and inputs will help me to improve the quality of this solution. The aim is to make a perfect document which will guide you to a perfect solution.

During a workshop, the most tedious part is to assign a QlikCloud user into the workshop space.  With Qlik Application Automation , this process can be automated from the creation of the space to the assignment of the user into the specific space.

First, I created a google sheet that contains the list of users email, and 1 sheet correspond to 1 workshop.

I used the List Sheets Of Spreadsheet block to list out all the sheets within this workbook

Then I used the Condition block to check whether the sheet is prefix with *.  This provide a mechanism to skip any sheet by renaming the sheet.

If it is not prefix with *, the Create Space block will create a space for each sheet using the name of the sheet.  You will need to set the type of share and most importantly, set on error to ignore and continue automation and ignore errors.  This will ignore this block when the space has already been created.

Once the space is created, the Search Spaces block is used to retrieve the ID of the space as the space assignment is by the ID

Next, the Get Data From Sheet With Headers block is used to retrieve the list of users email from the google sheet

For each user email, the Search Users block is used to retrieve the ID based on the email address.

With both the Space ID and User ID, the Add Member To Space block is used to assign the user into the space.  Note the type of roles to assign to the user and the type of member.  Also set the on error to ignore and continue automation and ignore errors in case the user has not signed up yet.  This will ignore this user and move to the next user.

The following is the entire flow of the Qlik Application Automation

This can then be scheduled to run everyday and possibly more frequently just before the start of the workshop.