Environment

• Qlik Sense Enterprise SaaS
• Qlik Application Automation

This article describes how an Excel file can be sent as an attachment by using the Send Mail block in Qlik Application Automation.

Automation Structure

The creation of an Excel report from a Qlik Sense straight table is explained in the first part of this article.

Let's walk through the blocks used in the automation workflow to send the Excel file as an attachment using the "Send Mail" block.

1. Open the Excel file saved in Microsoft SharePoint using the "Open File on Microsoft SharePoint" block.
   
   
2. Use the "Send Mail" block from Mail Connector to send the Excel file as an attachment via email.
   
   
   

A JSON file containing the above automation is attached to this article.

Please follow the steps provided in the How to import automation from a JSON file article to import the automation.

Related Content 

• Generate Excel Report 
• Copy data from a Qlik Sense straight table to an Excel sheet 

It is important to understand the options which PostgreSQL uses to allow connectivity. These principles are valid no matter whether you are using the Qlik Sense Repository Database service to host the database or using a standalone PostgreSQL instance. The two key files which PostgreSQL uses to define who can connect, how they connect, where they can connect from, and how many connections are allowed are:

• postgresql.conf
• pg_hba.conf

For bundled PostgreSQL instances, the files are located in: 

C:\ProgramData\Qlik\Sense\Repository\PostgreSQL\x.x

If you have unbundled PostgreSQL using the Qlik PostgreSQL installer (QPI), the default location is:

C:\Program Files\PostgreSQL\xx\data

For any other standalone PostgreSQL instances, consulting with your database admin to locate the data directory. 

Note: Any changes to these files will require a restart of the Qlik Sense Repository Database or PostgreSQL.
Note: Any lines prefixed with a # are commented out and not active.

postgresql.conf

In the postgresql.conf, in a basic Qlik Sense configuration, there are two key configuration options which are important:

• listen_addresses
  • This configuration defines which NICs PostgreSQL will listen on. For standalone PostgreSQL servers or multi-node Qlik Sense sites, the recommended setting is * (example below). This allows PostgreSQL to bind to any NICs presented to the server. Multiple comma-separated DNS entries may be added as well. (e.g.  listen_addresses = 'localhost,QlikServer2.domain.local' ) More advanced configurations are possible here but will not be covered in this guide.
• max_connections
  • This configuration defines how many connections PostgreSQL will allow.  It is best to use a rule of thumb of about 110 connections per node +20.  For example, for 4 nodes set to 460. See Installing Qlik Sense in a multi node for more details. 

Where these lines are can vary but an expected single node Qlik Sense site would look like this:

listen_addresses = '*'
max_connections = 100

pg_hba.conf

The pg_hba.conf file defines a variety of aspects:

• What IP addresses or ranges can connect to PostgreSQL and for what accounts
• What method of authentication those IP ranges / accounts can use
• For more details on configuration options for IP listeners, such as samenet, see https://www.postgresql.org/docs/9.6/auth-pg-hba-conf.html

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::/0                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#host    replication     postgres        127.0.0.1/32            md5
#host    replication     postgres        ::1/128                 md5
host	all	            all	            0.0.0.0/0	            md5

In this example pg_hba.conf file there two active lines. The first line specifies that all users can connect to all databases from the local loopback IP using MD5 authentication (host all all 127.0.0.1/32 md5). The second line specifies that all users can connect to all databases from any valid IPv4 address using MD5 authenication (host all all 0.0.0.0/0 md5)

Please review our help for references to more sophisticated configurations where specific IP addresses or IP address ranges are covered.

To recap, when ensuring connectivity in a Qlik Sense Shared Persistence environment, especially a multi-node site, the core configurations which are needed are:

• PostgreSQL configured to listen to an external IP address (listen_address in postgresql.conf)
• Sufficient connections available for Qlik Sense (max_connections in postgresql.conf)
• Explicit allowing the IP address which is used to connect (pg_hba.conf)

When using the tRedshiftOutputBulk or tRedshiftOutputBulkExec component in Talend Studio and setting the "Credential provider" to "Inherit credentials from AWS role", the following error is found in the karaf.log.

java.lang.IllegalArgumentException: Not supported S3 connection credential type: INHERIT_CREDENTIALS. Please use "Static Credentials."

Cause

Redshift does not support “Inherit credentials from AWS role” for "Credential provider".

Resolution

• tRedshiftOutputBulk
• tRedshiftOutputBulkExec

Redshift never supported "Inherit credentials from AWS role". In the UI, it appears that "Inherit credentials from AWS role" can be used, but in reality, it was "Static Credentials" running in the background.

If you were able to run the Job using "Inherit credentials from AWS role" in the past, you may have already set the necessary parameters for "Static Credentials" (Access key and Secret key), which allowed it to run without errors.

Environment

• Talend Data Integration 8.0.1

In larger organizations, the number of groups a user is a member of may exceed the limit that Azure Active Directory will add to a token. 150 groups for a SAML token, and 200 for a JWT. 

Environment

• Qlik Cloud 

Solution

Qlik Cloud does not read the groups into the ID token returned by Azure, but instead will fetch the groups for the user from the MS Graph API (a maximum of 1000 groups is fetched).

The groups are fetched from the following endpoint and nested groups are not supported.

https://docs.microsoft.com/en-us/graph/api/user-list-memberof

However, please note that if sending only specific groups to Qlik Sense is needed (and not all groups assigned to the user in Azure), then you will have to use the IdP type "ADFS" to read attributes from the ID token and use roles in Azure instead of groups to pass only the wanted groups.

https://joonasw.net/view/using-groups-vs-using-app-roles-in-azure-ad-apps

The information in this article is provided as-is and to be used at own discretion. Depending on tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support.

Related Content 

https://docs.microsoft.com/en-us/azure/active-directory/hybrid/how-to-connect-fed-group-claims

https://joonasw.net/view/using-groups-vs-using-app-roles-in-azure-ad-apps

Qlik Sense Enterprise on Windows frequently logs the following error:

Error: QmcTaskTableCacheDisabled 

The solution documented in Tasks in the Qlik Sense Management Console don't update to show the correct status after an upgrade has been implemented successfully, but the error continues to be logged.

Resolution

Clearing the browser cache will resolve the issue temporarily. To more permanently make certain the cache is updated:

1. Add additional response headers to the Qlik Sense Virtual Proxy:
   

   Strict-Transport-Security: max-age=31536000; includeSubDomains; preload
   Cache-Control: no-cache, no-store, must-revalidate

   For more information about configuring response headers, see How to add additional response headers in Qlik Sense.
   
   The additional headers implement a feature which checks if the cache is up to date, prompting the browser to make a new request using the correct data for each accessed version.
   
   
2. Clear the browser cache

Cause

Browser cache. Clearing it will temporarily resolve the issue.

Related Content

• Tasks in the Qlik Sense Management Console don't update to show the correct status after an upgrade
• How to add additional response headers in Qlik Sense

Environment

• Qlik Sense Enterprise on Windows 

Content:

• Step-by-Step:
• Limitations
• Related Content

Step-by-Step:

To create a Mail connection in Application Automation, do the following:

1. In Qlik Sense SaaS Hub select Add New > New automation
2. On the create dialog, select Connections in the menu
   
   
   
   
3. In Explore, search for Mail and select the Mail connector. Press Add
4. Fill out the details for your SMTP server. Press Save and Ok

The Mail connection will now be listed in My connections.

Verify the Mail connection by creating a simple automation.

1. In Qlik Sense SaaS Hub, select Add New > New automation
2. Give the automation a name and press Save
3. In the automation editor, click on the Mail connection in the left pane
   
   
   
   
4. Drag the Send Mail block on to the canvas, connecting it to the Start block
   
5. In the right pane, fill out the required fields to send a test email
   
   
   
   
6. Press Save and Run to execute the automation
7. Verify the test email being sent successfully and received in the mailbox
   
   If you receive any error when running the test automation, verify the details of your SMTP server being correct (Host, Password still accurate, Port etc):
   
   
   
   
8.  In the menu select Connections to go back to the connections page
9. In My connections, select the Mail connection and press Edit connection
10. Confirm all settings being correct and press Save
11. Run the test automation again to confirm the email being sent

Limitations

• The Mail Connector does not at present support SSO. See Qlik Application Automation: "Send Mail" block doesn't work with Gmail's with Okta integration
  
  
• The configuration for an Email server that already exists on a tenant cannot be used within an automation. A new connection must be created in Qlik Application Automation.
  
  
  
  
• The connection will not be verified during creation. As a best practice it is always recommended to verify the Mail connection with a simple automation, before using the Send Mail block in more advanced automations.

Related Content 

The information in this article is provided as-is and to be used at own discretion. Depending on tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support.

For information about Qlik Sense failover candidates, see Qlik Sense Failover Central node: Requirements and Considerations.

Out of the box, you need to toggle the option to enable a rim node as a failover node during first time setup. In this article, we will outline how to accomplish elevating an existing node to being a failover candidate primarily using the QRS APIs.

Steps:

1. Perform a GET /qrs/ServerNodeConfiguration/full API call
   
   
2. Copy the JSON response
   
   
3. Edit the JSON to include only the node that you want to elevate
           Remember to remove the []'s which bracket the JSON response
   
   
4. Change the failoverCandidate to be true and slightly modify the modifiedDate
   
   
5. Perform a PUT /qrs/ServerNodeConfiguration/{GUID_of_note_to_elevate} with the edited JSON in the body
   
   
6. Export full trusted root from existing Central candidate
   
   
7. Stop services on the newly elevated rim
   
   
8. Deleted the existing trusted root certificate on the newly elevated rim
   
   
9. Import the copy of the certificate that was previously exported from an existing Central candidate
   
   
10. Start all services on the rim

Environment

Qlik Sense Enterprise on Windows 

Related Content 

https://community.qlik.com/t5/Official-Support-Articles/Removing-a-Rim-Node-not-to-be-a-Failover-candidate-in-the/ta-p/1711472 

Accessing the QlikView Server in the Management Console, or making changes in its settings, throws a "Failed to load because item with ID does not exist. Make sure that the QVS is available with the correct credentials." error.

Environment

• QlikView 

Cause

QVPR is corrupted or includes a faulty Distribution Service / Reload Engine references.

Resolution

This error message indicates that QlikView QVPR is corrupted.

Solution One

If you have only started experiencing this recently, reverting to a backup of a previous QVPR version is advisable. 

Follow those steps to fix the issue, or see How To: Restore QVPR From Backup for details. 

1. Stop all the services
2. Open folder C:\ProgramData\QlikTech\ManagementService\QVPR\
3. Make a manual backup of all XML files, then delete them
4. Open sub-folder Backups
5. Select the zip file from the day before you faced the issue and unzip it in the QVPR folder.
6. Restart the services.

Solution Two

If this issue has existed for a long while, it may be necessary to look into removing faulty Distribution Service or ReloadEngine references.

For QlikView 11.20 SR15 and up, as well as QlikView 12.x see ReloadEngine@SERVERNAME is down displayed in the QlikView Management Console after an upgrade

Solution Three

If the issue presents itself during a new installation, the best approach may be to rename the ProgramData\QlikTech\ManagementService folder, restart the Qlik Management Service which should recreate the directory and all the default settings and QVPR data. Note: all the save settings are restored to default and tasks are flushed)
 

Qlik Sense Enterprise Client-Managed offers a range of Monitoring Applications that come pre-installed with the product.

Qlik Cloud offers the Data Capacity Reporting App for customers on a capacity subscription, and additionally customers can opt to leverage the Qlik Cloud Monitoring apps.

This article provides information on available apps for each platform.

Content:

• Qlik Cloud
• Data Capacity Reporting App
• Access Evaluator for Qlik Cloud
• Answers Analyzer for Qlik Cloud
• App Analyzer for Qlik Cloud
• Automation Analyzer for Qlik Cloud
• Entitlement Analyzer for Qlik Cloud
• Reload Analyzer for Qlik Cloud
• Report Analyzer for Qlik Cloud
• How to automate the Qlik Cloud Monitoring Apps
• Other Qlik Cloud Monitoring Apps
• OEM Dashboard for Qlik Cloud
• Monitoring Apps for Qlik Sense Enterprise on Windows
• Operations Monitor and License Monitor
• App Metadata Analyzer
• The Monitoring & Administration Topic Group
• Other Apps

Qlik Cloud

Data Capacity Reporting App

For more information, see Qlik Help.

Access Evaluator for Qlik Cloud

For more information, see Qlik Cloud Access Evaluator.

Answers Analyzer for Qlik Cloud

For more information, see Qlik Cloud Answers Analyzer.

App Analyzer for Qlik Cloud

For more information, see Qlik Cloud App Analyzer.

Automation Analyzer for Qlik Cloud

For more information, see Qlik Cloud Automation Analyzer.

Entitlement Analyzer for Qlik Cloud

The Entitlement Analyzer is a Qlik Sense application built for Qlik Cloud, which provides Entitlement usage overview for your Qlik Cloud tenant for user-based subscriptions.

The app provides:

• Which users are accessing which apps
• Consumption of Professional, Analyzer and Analyzer Capacity entitlements
• Whether you have the correct entitlements assigned to each of your users
• Where your Analyzer Capacity entitlements are being consumed, and forecasted usage

For more information, see The Entitlement Analyzer.

Reload Analyzer for Qlik Cloud

For more information, see Qlik Cloud Reload Analyzer.

Report Analyzer for Qlik Cloud

For more information, see Qlik Cloud Report Analyzer.

How to automate the Qlik Cloud Monitoring Apps

Do you want to automate the installation, upgrade, and management of your Qlik Cloud Monitoring apps? With the Qlik Cloud Monitoring Apps Workflow, made possible through Qlik's Application Automation, you can:

1. Install/update the apps with a fully guided, click-through installer using an out-of-the-box Qlik Application Automation template. 
2. Programmatically rotate the API key that is required for the data connection on a schedule using an out-of-the-box Qlik Application Automation template. This ensures that the data connection is always operational. 
3. Get alerted whenever a new version of a monitoring app is available using Qlik Data Alerts. 

For more information and usage instructions, see Qlik Cloud Monitoring Apps Workflow Guide.

Other Qlik Cloud Monitoring Apps

OEM Dashboard for Qlik Cloud

For more information and to download the app and usage instructions, see Qlik Cloud OEM Dashboard & Console Settings Collector.

The Qlik Cloud monitoring applications are provided as-is and are not supported by Qlik. Over time, the APIs and metrics used by the apps may change, so it is advised to monitor each repository for updates and to update the apps promptly when new versions are available.

If you have issues while using these apps, support is provided on a best-efforts basis by contributors to the repositories on GitHub.

Monitoring Apps for Qlik Sense Enterprise on Windows

Operations Monitor and License Monitor

The Operations Monitor loads service logs to populate charts covering performance history of hardware utilization, active users, app sessions, results of reload tasks, and errors and warnings. It also tracks changes made in the QMC that affect the Operations Monitor.

The License Monitor loads service logs to populate charts and tables covering token allocation, usage of login and user passes, and errors and warnings.

For a more detailed description of the sheets and visualizations in both apps, visit the story About the License Monitor or About the Operations Monitor that is available from the app overview page, under Stories.

Basic information can be found here:

The License Monitor 
The Operations Monitor 

Both apps come pre-installed with Qlik Sense. 

If a direct download is required: Sense License Monitor | Sense Operations Monitor. Note that Support can only be provided for Apps pre-installed with your latest version of Qlik Sense Enterprise on Windows. 

App Metadata Analyzer

The App Metadata Analyzer app provides a dashboard to analyze Qlik Sense application metadata across your Qlik Sense Enterprise deployment. It gives you a holistic view of all your Qlik Sense apps, including granular level detail of an app's data model and its resource utilization.

Basic information can be found here: 

App Metadata Analyzer (help.qlik.com)

For more details and best practices, see: 

App Metadata Analyzer (Admin Playbook)

The app comes pre-installed with Qlik Sense.

The Monitoring & Administration Topic Group

Looking to discuss the Monitoring Applications? Here we share key versions of the Sense Monitor Apps and the latest QV Governance Dashboard as well as discuss best practices, post video tutorials, and ask questions.

Monitoring & Administration 

Other Apps

LogAnalysis App: The Qlik Sense app for troubleshooting Qlik Sense Enterprise on Windows logs 
Sessions Monitor, Reloads-Monitor, Log-Monitor 
Connectors Log Analyzer 

All Other Apps are provided as-is and no ongoing support will be provided by Qlik Support.

<addressname> =
 (DESCRIPTION =
   (ENABLE=BROKEN) 
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(Host = <hostname>)(Port = <port>))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = <service_name>)
 )
)

(DESCRIPTION=(ENABLE=BROKEN)(ADDRESS=(PROTOCOL=TCP)(HOST=<hostname>)(PORT=<port>))(CONNECT_DATA=(SERVICE_NAME=<service_name>)))

Environment

• Qlik Sense Enterprise on Windows 

Resolution

Use different Proxies.

The underlying issue is that when the “Copy” of the App is being made, the copy would be unpublished, ergo the node that is making the Copy cannot see it, so the process fails.

It is suggested to use different virtual proxies instead of only one to load balance nodes for Published and Non published apps.

Internal Investigation ID(s) :

JIRA ID SHEND-631

Loop and Reduce will remove an app's section access that was defined through the load script. Offline usage needs to be enabled in your tenant's management console in order to use the Loop And Reduce block on an app outside a personal space. More information on offline usage.

See How to Loop and Reduce with always one selected value fields if your app contains always one selected value fields.

This article explains how a loop and reduce operation can be performed on a Qlik Sense app by using Qlik Application Automation. It covers two examples, in the first one, the reloaded apps will be (re)published to one space. In the second example, each reduced app will be (re)published to a separate space.

This article covers two advanced examples, a more basic example can be found here .

Covered in this article:

• 1. Loop and reduce to one space
• 2. Loop and reduce to multiple spaces

The source app used in this article can be downloaded here.

Attached to this article are 2 files of the exported automations used in this article. More information on importing automations can be found here.

1. Loop and reduce to one space

In this example, all reduced apps will be (re)published to the same space.

Create a new automation and follow these steps to perform a loop and reduce action on a Qlik Cloud app:

1. Go to the Qlik Cloud Services connector and search for the block "Loop And Reduce". Drag this block inside the automation editor.
   
   
2. Specify the id for the app that should be used as a source app. The reduction will be performed on this app. Next, specify the field name over which the loop should run, for each unique value of the specified field name, a reduced app will be created. In this example, CountryName will be used as the reduction value.
   
   

    

3. The reduced apps are stored on the Temporary File services. The Loop And Reduce block will return a list of file ids together with the unique value of the reduction field and a list of sheet ids for the sheets that were public on the source app.
   
   
4. The next step is to import the apps from the Temporary File services. To do this search for the block "Import App From Export Id" and drag this block in the loop that's created by the Loop And Reduce block. This block will import one app from the temporary file services into your personal space.
   Tip: specify the reduced value that's outputted by the Loop and Reduce block in the Name parameter of the Import block. See the below image for an example:
   
   

    

5. When an app is imported, all sheets will be made private, this is the default behavior of the import app block. The next step is to publish the sheets that were public in the source app.
   
   
6. Because each reduced app will have a list of sheets that were public, a Loop block is necessary to loop over the list. Search for a "Loop" block in the block menu or find it under Basic and connect it right below the "Import App From Export Id" block. Specify the "Public Sheets" list returned by the Loop And Reduce block as the value for the "Loop over items of list" input parameter of the Loop block.
   
   

    

7. Now to publish each sheet of the imported app, search for the block "Publish Sheet" in the Qlik Cloud Services connector and drag that block in the loop generated by the "Loop" block. In this block, specify the id that's returned by the Import App block as App Id and use the current item in the loop as Sheet Id.
   
   

    

8. Nex step is to publish or republish the imported app to a managed space. This can be done with the block "Publish App To Managed Space". Search for this block in the Qlik Cloud Services connector and connect it below the loop block. Set the Run Mode to "(Re)publish by name", if the managed space already contains an app with the same name as the imported app, the imported app will be republished, or else it will be published.
   
   
9. In the configuration of the Publish App To Managed Space block, set the App Id equal to the id returned by the Import App From Export Id block. For the Space Id, perform a lookup or manually enter an id, make sure you're specifying a managed space. The Data parameter should be set to "source".
   
   

    

10. Finally, when your app is published to a managed space, the imported app will still exist in your personal space. To remove this app, search for the block "Delete App" in the Qlik Cloud Services connector and connect it to the Publish App To Managed Space block. Specify the id returned by Import App From Export Id as the App Id in the block's settings.
    
    

An example of a completed automation:

Attached example file: loop_and_reduce_to_same_space.json

2. Loop and reduce to multiple spaces

In this example, each reduced app will be published to a different space. We'll use the Insurance Claims app and the automation for the first example again. Since this app reduces on the field CountryName, we'll start by creating a managed space for each unique value that's found for CountryName. This results in the following list of spaces:

Next is to create a mapping for each unique reduction value and the corresponding space's id. There are multiple ways to achieve this. In this example, this mapping is stored in a JSON list of objects with the keys reduction_value and space_id. Feel free to use an automation to create the managed spaces and build this list.

Example:

[
	{
		"reduction_value": "Scotland",
		"space_id": "6138a3062c1054d8158c189a"
	},
	{
		"reduction_value": "Northern Ireland",
		"space_id": "6138a318faed485d36ae911f"
	},
	{
		"reduction_value": "England",
		"space_id": "6138a337faed485d36ae9126"
	},
	{
		"reduction_value": "Wales",
		"space_id": "6138a33f98b0d0bf7e719dfb"
	},
	{
		"reduction_value": "Guernsey",
		"space_id": "6138a349faed485d36ae912b"
	},
	{
		"reduction_value": "Isle of Man",
		"space_id": "6138a35aba392246d331e611"
	},
	{
		"reduction_value": "Jersey",
		"space_id": "6138a3653ec592fe53a8d55b"
	}
]

The next step is to store the mapping in the automation. Execute the following steps to do this:

1. Search for the Variable block and add this to the automation.
   
   
2. Go to the block's settings, click Manage variables and add a new variable of type list.
   
   
3. Go to the operations section of the variable and set the first operation to empty the variable.
   
   
4. Add a second operation Merge other list into... and click the List input field of that operation, in the dropdown that appears, select Add Formula.
   
   
5. From the formula picker, search for the formula Object and select it. Copy the JSON mapping to the json input field of the Object formula and click Save.
   
   
6. Add the block to the beginning of the automation from the first part of this article:
   
   

    

7. The next step is to find the right space id for each reduced app. To do that, add the block Lookup Item In List in the automation right before the block Publish App To Managed Space.
   
   
8. Go to the settings of the Lookup Item In List block and specify the variable that contains space_id/reduction_value mapping as input list. The condition inside the lookup should compare the mapping's reduction_value to the Reduced Value of the current item in the loop of Loop And Reduce. See the below image for an example:
   
   
   

    

9. Finally, specify the space_id from the output of the lookup block as Space Id in the settings of the Publish App To Managed Space.
   
   

    

Attached example file: loop_and_reduce_to_mulitple_spaces.json

The information in this article is provided as-is and to be used at own discretion. Depending on tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support.

Since upgrading to Talend Runtime R2023-10,  the Pax web server component uses the standard full chain certificate based on PFX format keystore (includes intermediate and end-entity certificates) which makes the speed of server call slower.

Resolution

• Use the JKS format-based keystore/trust store for Talend Runtime directly, and import the certificates chains into the JKS keystore used by the pax web component. 
• Consider converting the existing PFX file to the Java Keystore (JKS) format, which is natively supported by Java and might offer better performance.

Cause

When working with PFX certificates in Java, the slower performance issue is caused by a few potential reasons:

• Large Certificate Chains: If the PFX file contains a long chain of certificates, validating the entire chain can take much time.
• Inefficient Keystore Implementation: The underlying keystore implementation in Java might not be optimized for certain operations.
• Complex Cryptographic Operations: Some cryptographic operations, like signing or decrypting, can be computationally intensive.

Environment

Talend ESB 

In this article, we walk you through the requirements and process of how to upgrade and unbundle an existing Qlik Sense Repository Database (see supported scenarios) as well as how to install a brand new Repository based on PostgreSQL. We will use the Qlik PostgreSQL Installer (QPI).

For a manual method, see How to manually upgrade the bundled Qlik Sense PostgreSQL version to 12.5 version.

Using the Qlik Postgres Installer not only upgrades PostgreSQL; it also unbundles PostgreSQL from your Qlik Sense Enterprise on Windows install. This allows for direct control of your PostgreSQL instance and facilitates maintenance without a dependency on Qlik Sense. Further Database upgrades can then be performed independently and in accordance with your corporate security policy when needed, as long as you remain within the supported PostgreSQL versions. See How To Upgrade Standalone PostgreSQL.

Index

• Supported Scenarios
• Upgrades
• New installs
• Requirements
• Known limitations
• Installing anew Qlik Sense Repository Database using PostgreSQL
• Qlik PostgreSQL Installer - Download Link
• Upgrading an existing Qlik Sense Repository Database
• The Upgrade
• Next Steps and Compatibility with PostgreSQL installers
• How do I upgrade PostgreSQL from here on?
• Troubleshooting and FAQ
• Related Content

Video Walkthrough

Video chapters:

• 01:02 - Intro to PostgreSQL Repository
• 02:51 – Prerequisites
• 03:24 - What is the QPI tool?
• 05:09 - Using the QPI tool
• 09:27 - Removing the old Database Service
• 11:27 - Upgrading a stand-alone to the latest release
• 13:39 - How to roll-back to the previous version
• 14:46 - Troubleshooting upgrading a patched version
• 18:25 - Troubleshooting upgrade security error
• 21:15 - Additional config file settings

Supported Scenarios

Upgrades

The following versions have been tested and verified to work with QPI (1.4.0): 

Qlik Sense February 2022 to Qlik Sense November 2023.

If you are on a Qlik Sense version prior to these, upgrade to at least February 2022 before you begin.  

Qlik Sense November 2022 and later do not support 9.6, and a warning will be displayed during the upgrade. From Qlik Sense August 2023 a upgrade with a 9.6 database is blocked.

New installs

The Qlik PostgreSQL Installer supports installing a new standalone PostgreSQL database with the configurations required for connecting to a Qlik Sense server. This allows setting up a new environment or migrating an existing database to a separate host.

Requirements

• Review the QPI Release Notes before you continue

• Using the Qlik PostgreSQL Installer on a patched Qlik Sense version can lead to unexpected results. If you have a patch installed, either:

  • Uninstall all patches before using QPI (see Installing and Uninstalling Qlik Sense Patches) or
  • Upgrade to an IR release of Qlik Sense which supports QPI

• The PostgreSQL Installer can only upgrade bundled PostgreSQL database listening on the default port 4432.
• The user who runs the installer must be an administrator.
• The backup destination must have sufficient free disk space to dump the existing database  
• The backup destination must not be a network path or virtual storage folder. It is recommended the backup is stored on the main drive. 
• There will be downtime during this operation, please plan accordingly
• If upgrading to PostgreSQL 14 and later, the Windows OS must be at least Server 2016

Known limitations

• Cannot migrate a 14.8 embedded database to a standalone
• Using QPI to upgrade a standalone database or a database previously unbundled with QPI is not supported.
• The installer itself does not provide an automatic rollback feature.

Installing a new Qlik Sense Repository Database using PostgreSQL

1. Run the Qlik PostgreSQL Installer as an administrator 
   
   
2. Click on Install 
   
   
3. Accept the Qlik Customer Agreement 
   
   
4. Set your Local database settings and click Next. You will use these details to connect other nodes to the same cluster. 
   
   
5. Set your Database superuser password and click Next
   
   
6. Set the database installation folder, default: C:\Program Files\PostgreSQL\14

   Do not use the standard Qlik Sense folders, such as C:\Program Files\Qlik\Sense\Repository\PostgreSQL\ and C:\Programdata\Qlik\Sense\Repository\PostgreSQL\.

7. Set the database data folder, default: C:\Program Files\PostgreSQL\14\data

   Do not  use the standard Qlik Sense folders, such as C:\Program Files\Qlik\Sense\Repository\PostgreSQL\ and C:\Programdata\Qlik\Sense\Repository\PostgreSQL\.

8. Review your settings and click Install, then click Finish
   
   
9. Start installing Qlik Sense Enterprise Client Managed. Choose Join Cluster option.
   
   The Qlik PostgreSQL Installer has already seeded the databases for you and has created the users and permissions. No further configuration is needed.
   
   
10. The tool will display information on the actions being performed. Once installation is finished, you can close the installer.  
    
    If you are migrating your existing databases to a new host, please remember to reconfigure your nodes to connect to the correct host. How to configure Qlik Sense to use a dedicated PostgreSQL database 
    

Qlik PostgreSQL Installer - Download Link

Download the installer here.

Qlik PostgreSQL installer Release Notes

Upgrading an existing Qlik Sense Repository Database

The following versions have been tested and verified to work with QPI (1.4.0): 

February 2022 to November 2023.

If you are on any version prior to these, upgrade to at least February 2022 before you begin.  

Qlik Sense November 2022 and later do not support 9.6, and a warning will be displayed during the upgrade.  From Qlik Sense August 2023 a 9.6 update is blocked.

The Upgrade

1. Stop all services on rim nodes 
   
   
2. On your Central Node, stop all services except the Qlik Sense Repository Database 
   
   
   
   
3. Run the Qlik PostgreSQL Installer. An existing Database will be detected. 
   
   
   
   
4. Highlight the database and click Upgrade 
   
5. Read and confirm the (a) Installer Instructions as well as the Qlik Customer Agreement, then click (b) Next.
   
   
   
   
6. Provide your existing Database superuser password and click Next.
   
   
7. Define your Database backup path and click Next.
   
   
   
   
8. Define your Install Location (default is prefilled) and click Next.
   
   
   
   
9. Define your database data path (default is prefilled) and click Next.
   
   
    
10. Review all properties and click Upgrade.
    
    The review screen lists the settings which will be migrated. No manual changes are required post-upgrade.
    
    
    
    
11. The upgrade is completed. Click Close.
     
12. Open the Windows Services Console and locate the Qlik Sense Enterprise on Windows services.
    
    You will find that the Qlik Sense Repository Database service has been set to manual. Do not change the startup method. 
    
    
    
    You will also find a new postgresql-x64-14 service. Do not rename this service. 
    
    
    

     

13. Start all services except the Qlik Sense Repository Database service. 
    
    
14. Start all services on your rim nodes.
    
    
15. Validate that all services and nodes are operating as expected. The original database folder in C:\ProgramData\Qlik\Sense\Repository\PostgreSQL\X.X_deprecated
    

     

16. Uninstall the old Qlik Sense Repository Database service.

    This step is required. Failing to remove the old service will lead the upgrade or patching issues.

    1. Open a Windows File Explorer and browse to C:\ProgramData\Package Cache
       
       
    2. From there, search for the appropriate msi file.
       
       
       
       If you were running 9.6 before the upgrade, search PostgreSQL.msi
       If you were running 12.5 before the upgrade, search PostgreSQL125.msi
       
       
    3. The msi will be revealed.
       
       
    4. Right-click the msi file and select uninstall from the menu.
       
       
       
       
    
    
17. Re-install the PostgreSQL binaries. This step is optional if Qlik Sense is immediately upgraded following the use of QPI. The Sense upgrade will install the correct binaries automatically. 
    

    Failing to reinstall the binaries will lead to errors when executing any number of service configuration scripts. 

    If you do not immediately upgrade:
    
    
    1. Open a Windows File Explorer and browse to C:\ProgramData\Package Cache
       
       
    2. From there, search for the .msi file appropriate for your currently installed Qlik Sense version
       
       
       
       For Qlik Sense August 2023 and later: PostgreSQL14.msi
       Qlik Sense February 2022 to May 2023: PostgreSQL125.msi
       
       
    3. Right-click the file
    4. Click Open file location
       
       
       
       
    5. Highlight the file path, right-click on the path, and click Copy
       
       
       
       
    6. Open a Windows Command prompt as administrator
       
       
    7. Navigate to the location of the folder you copied
       
       Example command line: 
       
       cd C:\ProgramData\Package Cache\{GUID}
       
       Where GUID is the value of the folder name.
       
       
    8. Run the following command depending on the version you have installed: 
       
       Qlik Sense August 2023 and later
       
       msiexec.exe /qb /i "PostgreSQL14.msi" SKIPINSTALLDBSERVICE="1" INSTALLDIR="C:\Program Files\Qlik\Sense"
       
       Qlik Sense February 2022 to May 2023
       
       msiexec.exe /qb /i "PostgreSQL125.msi" SKIPINSTALLDBSERVICE="1" INSTALLDIR="C:\Program Files\Qlik\Sense"
       
       This will re-install the binaries without installing a database. If you installed with a custom directory adjust the INSTALLDIR parameter accordingly. E.g. you installed in D:\Qlik\Sense then the parameter would be INSTALLDIR="D:\Qlik\Sense".

If the upgrade was unsuccessful and you are missing data in the Qlik Management Console or elsewhere, contact Qlik Support.  

Next Steps and Compatibility with PostgreSQL installers

Now that your PostgreSQL instance is no longer connected to the Qlik Sense Enterprise on Windows services, all future updates of PostgreSQL are performed independently of Qlik Sense. This allows you to act in accordance with your corporate security policy when needed, as long as you remain within the supported PostgreSQL versions.

Your PostgreSQL database is fully compatible with the official PostgreSQL installers from https://www.enterprisedb.com/downloads/postgres-postgresql-downloads. 

How do I upgrade PostgreSQL from here on?

See How To Upgrade Standalone PostgreSQL, which documents the upgrade procedure for either a minor version upgrade (example: 14.5 to 14.8) or a major version upgrade (example: 12 to 14). Further information on PostgreSQL upgrades or updates can be obtained from Postgre directly.

Troubleshooting and FAQ

1. If the installation crashes, the server reboots unexpectedly during this process, or there is a power outage, the new database may not be in a serviceable state. Installation/upgrade logs are available in the location of your temporary files, for example:
   
   C:\Users\Username\AppData\Local\Temp\2
   
   A backup of the original database contents is available in your chosen location, or by default in:
   
   C:\ProgramData\Qlik\Sense\Repository\PostgreSQL\backup\X.X
   
   The original database data folder has been renamed to:
   
   C:\ProgramData\Qlik\Sense\Repository\PostgreSQL\X.X_deprecated
   
   
2. Upgrading Qlik Sense after upgrading PostgreSQL with the QPI tool fails with:
   
   This version of Qlik Sense requires a 'SenseServices' database for multi cloud capabilities. Ensure that you have created a 'SenseService' database in your cluster before upgrading. For more information see Installing and configuring PostgreSQL.
   
   See Qlik Sense Upgrade fails with: This version of Qlik Sense requires a _ database for _.
   
   To resolve this, start the postgresql-x64-XX service.

The information in this article is provided as-is and to be used at own discretion. Depending on tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support. The video in this article was recorded in a earlier version of QPI, some screens might differ a little bit.

Related Content

Qlik PostgreSQL installer version 1.3.0 Release Notes  
Techspert Talks - Upgrading PostgreSQL Repository Troubleshooting
Backup and Restore Qlik Sense Enterprise documentation
Migrating Like a Boss
Optimizing Performance for Qlik Sense Enterprise
Qlik Sense Enterprise on Windows: How To Upgrade Standalone PostgreSQL
How-to reset forgotten PostgreSQL password in Qlik Sense
How to configure Qlik Sense to use a dedicated PostgreSQL database
Troubleshooting Qlik Sense Upgrades 

The text in a KPI object is not always at the same size. It changes according to the lenght of the text and to the size of the KPI.

Environment

• Qlik Sense Enterprise on Windows 
• Qlik Cloud 
• Qlik Sense Business 

Font size in KPIs is set to change automatically according to the space available in the object. If the text is too long, the font size is reduced. If it is short, it becomes larger in order to use all the space.

KPI object's font size can be adjusted to Small, Medium and  Large but like all other visualizations it will be responsive and automatically resize content when chart is resized.

When the chart is resized, the font size is automatically adjusted to fit the chart. It is not possible to set an absolute value for the KPI font as of February 2019 release. A feature request has been registered in order to allow the font size in KPI objects to be set to absolute.

See Presentation section under KPI Properties

Font size: Set the relative font size of the measure value. When the chart is resized, the font size is adjusted to fit the chart.

How to enable "Integrated Windows Authentication" (IWA authentication) in Qlik Sense and QlikView. Windows Integrated Authentication allows a user’s Active Directory credentials to pass through their browser to a web server.

If you would like to do the reverse and always prompt for username and password, then the opposite settings will need to be taken. 

Environment:

QlikView 
Qlik Sense Enterprise on Windows 
Qlik NPrinting 

Internet Explorer:

Step 1, enable IWA: 

1. Go to the Windows Control Panel and select Network and Internet
2. Open Internet Options
   
   
   
   
3. Select the Network Zone you want to configure (Trusted sites or Local intranet) 
4. Open custom level...
   
   
   
   
5. Scroll to the bottom and click Automatic Logon with current username and password
   
   
   
   

If you are looking to configure your Trusted sites and add your Qlik server:

1. Click Trusted Sites
2. Click Sites
   
   
   
   
3. Add your desired domain name.
   
   

Google Chrome:

Google Chrome in Windows will use Internet Explorer settings

Firefox:

1. Navigate to the URL about:config in Firefox. 
2. Proceed by clicking Accept the Risk and Continue
   
   
   
   
3. In the search bar, search for: network.automatic-ntlm-auth.trusted-uris
   
   Add your domain.
   
   
   
   

The following messages will appear in the engine trace system logs:

QVGeneral: when AAALR(63.312046) is greater than 1.000000, we suggest using new row applicator to improve time and mem effeciency.
QVGeneral: - aggregating on 'RecruiterStats'(%DepartmentID) with Cardinal(87), for Object:  in Doc: ffe8a825-b52e-4ceb-aea2-30de0f2c3306

There has also been reports of end users seeing the message "Internal Engine error" when opening apps when the error above is present.

Also for QlikView see article SE_LOG: when AAALR(1072.471418) is greater than 1.000000, we suggest using new row applicator to improve time and mem efficiency.

Environment:

• Qlik Sense Enterprise on Windows, all versions

"AAALR" is a very low level concept deep in the engine. Generally speaking it means the average length of aggregation array. The longer this array is, the more memory and CPU power are to be used by the Engine to get aggregation results for every hypercube node.
 
When AAALR is greater than 1.0, normally the customer has a large data set and suffers slow responses and high memory usage in their app. In this case, Qlik Sense has a setting called DisableNewRowApplicator (default value is 1).
 
By setting this parameter to “0”, Qlik Sense will use a new algorithm which is optimized for large data set to do the aggregation, and will use much less memory and CPU power.

Changing this setting when they have AAALR warnings, making this change has resulted in drastic performance increases.

Possible setting values for DisableNewRowApplicator:

Resolution:

• Stop all Qlik Sense services from the windows control panel / services
• Locate the “C:\ProgramData\Qlik\Sense\Engine\Settings.ini”
• Add this portion to the Engine Settings.ini file (press <enter> at the end of DisableNewRowApplicator=0 so the cursor rests on a blank line below it)

[Settings 7]
DisableNewRowApplicator=0
         <---- the cursor should be here when saving the file

• Restart all Qlik Sense services
• (If multinode) Complete the above steps on every node.

Related Content:

• How to start investigating a suspected Qlik Sense Performance issue  

This article provides an overview of how to notify your team on Microsoft Teams using data from multiple apps with Qlik Application Automation.

The template is available via the template picker. You can find it by navigating to Add new -> New automation -> Search templates searching for 'Notify your team on Microsoft Teams with data from multiple apps' in the search bar, and clicking the Use template option.

You will find a version of this automation attached to this article: "Notify-your-team-on-Microsoft-Teams-with-data-from-multiple-apps.json".

Content:

• Full Automation
• Automation structure
• Related Content

Full Automation

Automation structure

The following steps describe how to build the demo automation: 

1. Add two 'Get Measure Value' blocks from the Qlik Cloud Services connector to get a measure value from the first and second apps.
   
   
2. Add the 'Select Field Value' block from the Qlik Cloud Services connector to apply a selection to the third app before getting the measure value.
   
   
3. Now add 'Get Measure Value' block from the Qlik Cloud Services connector to get a measure value from the third app.
   
   
4. Add a 'Send Message' block from the Microsoft Teams connector to send a message to the Teams channel with the information from the above three apps. Specify the Team ID and the Channel ID input parameters. You can use the do-lookup functionality to find value for all these parameters. 

Related Content

• How to get started with Microsoft Teams

Environment

• Qlik Cloud  
• Qlik Application Automation 

This article provides an overview of how to send straight table data to Microsoft Teams as a table using Qlik Application Automation.

The template is available on the template picker. You can find it by navigating to Add new -> New automation -> Search templates, searching for 'Send straight table data to Microsoft Teams as a table' in the search bar, and clicking the Use template option.

You will find a version of this automation attached to this article: "Send-straight-table-data-to-Microsoft-Teams-as-a-table.json".

Content:

• Full Automation
• Automation structure
• Related Content

Full Automation

Automation structure

The following steps describe how to build the demo automation: 

1. Add the 'Get Straight Table Data' block from the Qlik Cloud Services connector to get data from the straight table for a specific app to send to the Microsoft Teams channel as a table. Specify the app ID, Sheet ID, and Object ID in the input parameters. You can use the do-lookup functionality to find value for all these parameters. There is a limit of 100,000 records that can be retrieved through the 'Get Straight Table Data' block. This block will not work with other table types.
   
   
2. Add a 'Loop' block to loop over the keys from the 'Get Straight Table Data' block response which acts as the table header. Use the '{getkeys: {$.GetStraightTableData}}' formula in the 'Loop' block. Within the 'Loop' block add the following blocks:
   
   
   1. Add an object variable called 'teamsTextBlockObject' to build the header and pass this variable to another list variable called 'cardTableColumn'.
      
      
   2. Add another 'Loop' block to loop over all the values for the current key of the straight table. Within the 'Loop' block add the following blocks:
      
      
      1. Add a 'Condition' block to check whether the column value is empty or not.
         
         
         • If the condition block outcome evaluates to true:
           
           
           • Add the 'teamsTextBlockObject' object variable, set the text value as 'NA' since the column value is null/empty, and pass this variable to the 'cardTableColumn' list variable.
             
             
         • If the condition block outcome evaluates to false:
           
           
           • Add the 'teamsTextBlockObject' object variable, set the text value as the column value of the straight table, and pass this variable to the 'cardTableColumn' list variable.
             
             
   3. Add another object variable called 'columnObject' and pass this variable to the 'columns' list variable. These variables are used to build the straight table data into a specific format which is required to build the structure of the team's message as a card attachment.
      
      
3. Add a string variable called 'title' which is used to provide the title to the message.
   
   
4. Add the 'Create Attachment Adaptive Card' block from the Microsoft Teams connector to build the structure for card attachment. Make sure to transform the 'columns' list to JSON text using the JSON formula.
   
   
   
   
5. Add 'Send Message' block from the Microsoft Teams connector to send straight table data as a table to the Teams channel with the card attachment which was created in the previous block.
   Specify the Team ID and the Channel ID in the input parameters. You can use the do-lookup functionality to find the value of all these parameters. 
   
   
   

An example output of the table sent to the Teams channel:

The information in this article is provided as-is and will be used at your discretion. Depending on the tool(s) used, customization(s), and/or other factors, ongoing support on the solution below may not be provided by Qlik Support.

Related Content

• How to get started with Microsoft Teams
• Send straight table data to Microsoft Teams as a message 
• Send straight table data to email as HTML table

Environment

• Qlik Cloud  
• Qlik Application Automation 

This article provides an overview of how to send straight table data as a message to Microsoft Teams using Qlik Application Automation.

The template is available on the template picker. You can find it by navigating to Add new -> New automation -> Search templates, searching for 'Send straight table data to Microsoft Teams as a message' in the search bar, and clicking the Use template option.

You will find a version of this automation attached to this article: "Send-straight-table-data-to-Microsoft-Teams-as-a-message.json".

Content:

• Full Automation
• Automation structure
• Related Content

Full Automation

Automation structure

The following steps describe how to build the demo automation: 

1. Add the 'Select Field Value' block from the Qlik Cloud Services connector to apply a selection to the straight table.
   
   
2. Add the 'Get Straight Table Data' block from the Qlik Cloud Services connector to get data from the straight table for a specific app to send to the Microsoft Teams channel as a table. Specify app ID, Sheet ID, and Object ID input parameters. You can use the do-lookup functionality to find value for all these parameters. The 'Get Straight Table Data' block is limited to retrieving  100,000 records. This block will not work with other table types.
   
   
3. Add a string variable called 'message' within 'Get Straight Table Data' block to append a list item element (<li>) to the message variable. Specify key-value pairs for the dimensions from your straight table to be included in the message.
   
   
4. Add a 'Send Message' block from the Microsoft Teams connector to send straight table data as a message to the Teams channel. Map the 'message' variable to the Content input parameter and enclose it in an unordered list element <ul>. Ensure you don't send too many records; Microsoft Teams has a limit of 28 KB per message. Specify the Team ID and the Channel ID input parameters. You can use the do-lookup functionality to find value for all these parameters. 
   
   

An example output of the message sent to the Teams channel:

The information in this article is provided as-is and will be used at your discretion. Depending on the tool(s) used, customization(s), and/or other factors, ongoing support on the solution below may not be provided by Qlik Support.

Related Content

• Video - Send straight table data to a Microsoft Teams channel as a message 
• How to get started with Microsoft Teams
• Send straight table data to Microsoft Teams as a table 

Environment

• Qlik Cloud  
• Qlik Application Automation