Hi all,

Mini Charts have always been a good feature in QlikView and It's really 'frustrating' that this has not been added (yet) in Qlik Sense.

With hopes that the Qlik Team will think about such improvements for the next releases, let's discuss here how to create our own mini chart in a Qlik Sense Table.

The following expressions might seem 'complicated' at first sight, but remember that It will be generic.

You'll only have to change your : Dimension(s) and Measure to adapt to your need.

Thus, their use will be very easy.

Let's begin :

Suppose we have:

As a dimension: OICA Region

As measures:

Sum(Sales)

Sum("Commercial Vehicle Sales")

Sum(Sales) / (Sum(Sales)+Sum("Commercial Vehicle Sales"))

Here is our current table:

Now, we want to add, a mini 'bar' to represent the %:

How to do so:

As an expression:

=repeat('█',

                  rangemax(rangemin( ceil(((column(1)/column(2))-1)*2.5),10),0)

                  )

With that, we repeat the square sign and precise the limits.

And as a text Color expression:

if( (sum([Car sales])/(sum([Car sales])+sum([Commercial vehicle sales])))>=0.8,Green(),

if((sum([Car sales])/(sum([Car sales])+sum([Commercial vehicle sales])))>=0.7,Yellow(),

Red()))

Result:

We can also Add the % in front of the bar by adding it in the expression:

From :

=repeat('█',

                  rangemax(rangemin( ceil(((column(1)/column(2))-1)*2.5),10),0)

                  )

To:

=repeat('█',

                  rangemax(rangemin( ceil(((column(1)/column(2))-1)*2.5),10),0)

                  ) & num((sum([Car sales])/(sum([Car sales])+sum([Commercial vehicle sales]))),'# ##0,00%')

Result:

You can even alter it as follow:

repeat(chr(09608),round( sum([Car sales])/(sum([Car sales])+sum([Commercial vehicle sales])) * 10))

&repeat(chr(09617),10-round( sum([Car sales])/(sum([Car sales])+sum([Commercial vehicle sales])) *10))

&num((sum([Car sales])/(sum([Car sales])+sum([Commercial vehicle sales]))),'# ##0,00%')

&if(sum([Car sales])/(sum([Car sales])+sum([Commercial vehicle sales]))>0.7,'▲','▼')

result:

Now, let's suppose we want to add another mini chart, a trend one, to see the % expression by Year:

If it has increased or decreased from a year to the next one.

How to do so?

Expression:

=concat(

        aggr(

            if((sum([Car sales])/(sum([Car sales])+sum([Commercial vehicle sales]))) > above((sum([Car sales])/(sum([Car sales])+sum([Commercial vehicle sales])))),

              '▀',if((sum([Car sales])/(sum([Car sales])+sum([Commercial vehicle sales]))) < above((sum([Car sales])/(sum([Car sales])+sum([Commercial vehicle sales])))),'▄',' ')

            )

            ,[OICA region], (Year,(NUMERIC, ASCENDING))

        )

,'',[OICA region])

Wirh:

Red: Our Dimension

Green: The trending dimension

Blue: our expression compared to the previous one (by year, the trending expression)

If expression by Year > expression Previous Year then , Square Up

If expression by Year < expression Previous Year then , Square down

Result:

Special thanks to blog Von Heldendaten in which I found this:

QlikView + Qlik Sense Blog von Heldendaten: Qlik Sense Calendar Measures & "Minicharts" in Tabellen.

Hope you like it !

Omar,

I stumbled upon this feature while trying desperately to simply get a list of items displayed in the QMC exported into an Excel spreadsheet. If there are other simpler methods, please let me know.

The tip requires OneNote and Excel and Internet Explorer (Other browsers not tested)

If you have any questions let me know.

Shaun

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/