When applying a Qlik Sense License Key to Qlik Sense Enterprise, the license cannot be activated.

An error is returned:

There was an error when getting license information from the license server

When the issue is connectivity related, the following string may also be registered among the log lines:
...
wsarecv: An existing connection was forcibly closed by the remote host.","errorType":"*url.Error"},...

OR, in the Repository Trace System logs:

...

ERROR QlikServer1 System.Repository.Repository.Core.Licensing.LicenseServer.LicenseServerFacade 29 58250808-d4f0-40d8-ba4c-73290bc63979 DOMAIN\qvservice There was an error when getting license information from the license service. LicenseDefinition: {"error":"LICENSES-InternalServerError","message":"Post \"https://license.qlikcloud.com/v1/definitions/signed\": context deadline exceeded (Client.Timeout exceeded while awaiting headers)","errors":[{"code":"LICENSES-030","title":"Post \"https://license.qlikcloud.com/v1/definitions/signed\": context deadline exceeded (Client.Timeout exceeded while awaiting headers)"}]}, LicenseOverview: The remote server returned an error: (500) Internal Server Error.

...

Environment:

• Qlik Sense Enterprise on Windows , with Signed License Key (SLK)

Resolution:

Internet Access

Make sure the HTTPS port for outbound connections is opened (example: port 443 - mostly used - but the port number might differ for the end-user). The port is used for accessing the endpoint at https://license.qlikcloud.com  which is required to activate the license key. See Testing connection to license.qlikcloud.com should return HTTP 404 Not Found  

If a Web proxy is required for Internet access,  see steps in image below which are documented under proxy configuration for Qlik License Service communication.

Proxy configured

If the license cannot be applied even though HTTPS port (example: port 443) is opened, or the proxy configured for Qlik License Service communication correctly; please reach out to the technical Product Support team. (How to raise a Technical case).

Verify what product the license is for

Verify that the license you purchased is for the correct product. 

Example: Signed License Keys cannot be used if a license is Token-based Only. In this case, a license number and Control Number need to be used.  

A token license looks like:

00000000000000
OVERAGE;NO;;
PRODUCTLEVEL;50;;2020-12-31
SPECIAL_EDITION;TEST;;
TEST_EDITION;YES;;
TOKENS;3000;;

Related Content:

• List of IP Addresses behind license.qlikcloud.com and lef.qliktech.com  
• Top 5 Things to Know About Modern Qlik Licensing  
• Qlik Licensing Service Reference Guide 
• How to license a QlikView or Qlik Sense server without Internet access  

March 14th 2023: You may have upgraded Qlik Sense and located a warning banner in your Management Console stating that this version of PostgreSQL is not supported. If you are running a standalone PostgreSQL database later than 9.6, this message can be disregarded. We are working on resolving the underlying cause. 

In this article, we walk you through the requirements and process of how to upgrade an existing Qlik Sense Repository Database from PostgreSQL 9.6 to PostgreSQL 12.5 (see supported scenarios) or install a brand new Repository based on PostgreSQL 12.5.

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 

PostgreSQL 9.6 has reached End of Life Reference: PostgreSQL Versioning Policy.

Index

• Supported Scenarios
• Considerations
• Installing anew Qlik Sense Repository Database using PostgreSQL 12.5
• Qlik PostgreSQL Installer - Download Link
• Upgrading anexisting QRD to PostgreSQL 12.5
• The Upgrade:
• Finalizing the Upgrade/Uninstalling the old Database Service:
• Compatibility with PostgreSQL installers
• Troubleshooting
• Related Content

Supported Scenarios

• Upgrades: Before using the Qlik PostgreSQL Installer, upgrade to a supported version of Qlik Sense Enterprise on Windows. Then use the Qlik PostgreSQL Installer to upgrade PostgreSQL.
  
  We tested and validated Qlik Sense Enterprise on Windows from February 2022 to February 2023. Note that November 2022 and later do not support 9.6, and a warning will be displayed during the Sense upgrade.
  
  
• New installs: The Qlik PostgreSQL Installer supports installing a new Qlik Sense Repository Database cluster on a new server, setting up a new environment or migrating an existing Database to a separate host.

Considerations

• This installer will not detect a standalone PostgreSQL database installed by any other means, other than through an official Qlik installer package, installed on the standard folders in the C drive. 
• The installer itself does not provide a rollback feature. This is described below. 
• The user who runs the installer has sufficient permissions to execute it and install services 
• The user who runs the installer has sufficient permissions to create folders and files in the backup destination 
• The backup destination has sufficient free disk space to dump the existing database  
• The backup destination is not 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 

Installing a new Qlik Sense Repository Database using PostgreSQL 12.5

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, standard: C:\Program Files\PostgreSQL\12
   
   
7. Set the database data folder, standard: C:\Program Files\PostgreSQL\12\data
   
   
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 version 1.2.1 Release Notes

Upgrading an existing QRD to PostgreSQL 12.5

Before using the Qlik PostgreSQL Installer, upgrade to a supported version of Qlik Sense Enterprise on Windows. Then use the Qlik PostgreSQL Installer to upgrade PostgreSQL.

We tested and validated Qlik Sense Enterprise on Windows from February 2022 to February 2023. Note that November 2022 and later do not support 9.6, and a warning will be displayed during the Sense upgrade.

Qlik Sense Enterprise on Windows installations other than in C:\Program Files\Qlik\Sense are currently not supported. 

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.
    
    
    
    
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-12 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.

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

Finalizing the Upgrade/Uninstalling the old Database Service:

No further steps are needed unless you plan to upgrade Qlik Sense Enterprise on Windows to the next major release. If you plan to upgrade to, for example, November 2022, the old Qlik Sense Repository Database service must be removed from the server.

Perform the following steps to delete the Qlik Sense Repository Database Service:

1. Open a command prompt and run the command:
   cd "C:\ProgramData\Package Cache"
2. From there, run:
   c:\ProgramData\Package Cache\dir /s PostgreSQL.msi
3. The folder containing PostgreSQL.msi will be revealed.
4. Right-click on the PostgreSQL.msi file and select uninstall from the menu.

Compatibility with PostgreSQL installers

This version of the upgrade tool is fully compatible with the official PostgreSQL installers from https://www.enterprisedb.com/downloads/postgres-postgresql-downloads.

If you need to upgrade to a higher release than Qlik provides, you can install the same minor PostgreSQL release 12.x.

Troubleshooting

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\9.6
   
   The original database data folder has been renamed to:
   
   C:\ProgramData\Qlik\Sense\Repository\PostgreSQL\9.6_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-12 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.2.1 Release Notes  
Techspert Talks - Upgrading Qlik Sense Repository Service
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

Loading files larger than 50MB from either Dropbox or SharePoint with their respective connectors leads to: 

Data file size exceeded file streaming buffer size. Adjust the StreamingBufferSizeMB setting.

Environment

Qlik Sense Enterprise on Windows 
Qlik Cloud 

Resolution

Qlik Sense Enterprise on Windows (client-managed):

The solution requires us to modify the Qlik Sense Engine settings.ini. See How to modify Qlik Sense Engine settings.ini for details on the procedure.

1. Stop the Engine Server
2. Go to C:\ProgramData\Qlik\Sense\Engine
3. Create a backup of the Settings.ini 
4. Open the Settings.ini file
5. Add StreamingBufferSizeMB=1024 line  (higher values are not currently supported by connectors)
6. Add an empty line after the above parameter if the parameter is the last one to be added
7. Save
8. Restart the Engine Service

Qlik Cloud

The value cannot be configured in Qlik Cloud. A workaround is to split the big data files by e.g. months.

Internal Investigation ID(s) 

QLIK-83207
CP-4120
QLIK-82097

When trying to connect to Qlik Sense Hub/QMC or QlikView Access point/QMC using a DNS ALIAS (or CNAME) record you are prompted to authenticate. 

After validating your credentials, it doesn't work and you are prompted again until the page goes completely blank. 


In the Windows System Event logs you will see this message: 

Connecting to Qlik Sense/QlikView outside the QlikSense Server works.
Connecting from Qlik Sense/QlikView Server using the server name or the IP address in the URL works.

For this issue with Qlik Sense you may also have the below Audit_Proxy warning:

4783    20200414T025339.978+0200    WARN    SERVERNAME    Audit.Proxy.Proxy.Core.Connection.Validation.ConnectionDataValidator    27    03f227fe-53e8-478f-857c-8099358a7963    DOMAIN\QLIKSENSEACCOUNT   Http request Host is not allowed: 79.137.105.56.    0    18716f09-d684-48fc-bf1b-feb97f63e430                ::ffff:193.118.52.26                0024ddbcac7413c72adefc2ff4ce78e0f91eae88


Environment:

Resolution

Cause

This article shows you how to locate your Qlik Cloud license number (Subscription ID) as well as your Tenant Hostname, your Tenant ID, and your Recovery Address. 

If you are looking to change your Tenant Alias or Display name, see Assigning a hostname Alias to a Qlik Cloud Tenant and changing the Display Name.

1. Log in to your Qlik Cloud Account and hover over your Profile Icon
2. Click About
   
   
   
   
3. This will show your:
   
   
   • Tenant hostname
   • Tenant ID
   • Subscription ID
   • Recovery address
     
     

Environment

• Qlik Cloud 

Qlik Sense allows for three settings that may influence the perceived connection and session timeout period. These are the "Session Inactivity Timeout", "Keep-Alive Timeout", and "TCP Websocket keep-alive"  settings. 

Note: Adjusting the below settings can help when working with slow internet connectivity or wanting to extend the session inactivity. However, session disconnect issues can be caused by other network connectivity issues and by system resource shortage as well and may require additional troubleshooting.  See Hub access times out with: Error Connection lost. Make sure that Qlik Sense is running properly 

1.  The connection "Keep Alive" setting

This is the maximum timeout for a single HTTP request. The default value is 10 seconds. During the defined keep alive timeout value, the connection between end user and Qlik Sense will remain open.

It serves as protection against denial-of-service attacks. That is, if an ongoing request exceeds this period, Qlik Sense proxy will close the connection.

Increase this value if your users work over slow connections and experience closed connections for which no other workaround has been found. Make sure to take the mentioned DoS consideration above into account. 


 

2.  The "Session Inactivity Timeout"

This is the browser authentication session time out ( 30 minutes by default set under Virtual Proxy in QMC ). This sets a cookie on the client machine with the name X-Qlik-Session. This cookie can be traced in Fiddler or Developer tools under the  header tab. 

If the session cookie header value does not get passed, is destroyed, or modified between the end user client and the Qlik Sense server while 'in-flight' the user session is terminated and the user is logged out.

By default, it will be destroyed after 30 minutes of inactivity or when the browser is closed.

3. TCP Websocket keep-alive

This is another setting that may help keep the connection open in certain environments. See Enabling TCP Keep Alive Functionality In Qlik Sense. Note that customers who don't experience any issues with web sockets terminated by the network due to inactive SHOULD NOT switch this feature ON since it may potentially cause Qlik Sense to send unnecessary traffic on the network towards the client.

Related Content:

• Hub access times out with: Error Connection lost. Make sure that Qlik Sense is running properly 

Environment:

• Qlik Sense Enterprise on Windows, all versions

Hard to Imagine

Let's imagine you have millions/billions of rows of disparate data. That's not hard to imagine. You do.

Let's imagine that you need to create analytics from all that data. That's not hard to imagine. You do.

Let's imagine that you have a data integration pipeline with change data capture to keep changes constantly flowing for those sources. That's not hard to imagine. You do.

Let's imagine that you want to quickly get those changes into your analytics applications being used by thousands of end users without having to touch the other millions/billions of rows of disparate data you already loaded. WOW! Now that is hard to imagine.

Qlik Basics

Before I propose a super simple solution to that hard to imagine issue, I need to share just a few Qlik basics to solidify my proposal:

• Qlik encourages you to load each table individually so that you can take advantage of the Qlik Associative Engine, rather than pushing down SQL statements with JOINS.
• Qlik encourages you to store the tables into binary* storage (QVD files).
• Qlik encourages you to separate the concept of all that data from your visual applications so that your QVD files can be reused by multiple applications.

Those concepts are different. They are unique. Sometimes those things can be frustrating for those new to Qlik because it makes them think differently than they have for their entire life. I get that. It is these differences that allow Qlik customers to associate disparate data sources in-memory for speed of thought insights with minimal impact to underlying source systems.

Don't get me wrong. You do not have to take advantage of them, or follow this model at all. You can absolutely use Qlik like other tools. You can push down any SQL you want, including statements that contain as many JOIN clauses as needed. You can pull data live from your data sources on the fly via Direct Query, On Demand Application Generation or Dynamic Views. Each of those options have use cases that they are well suited for. They just aren't suited for this enterprise use case where we have millions/billions of rows of data that we need to analyze. While also rapidly incorporating any changes that might be flowing through our data integration pipelines so that our impact of reading the data, and the costs associated are kept to a minimum. 

Incremental Loads

Those Qlik basics I referred to, have always enabled Qlik architects to perform incremental loads of data. The changes. Regardless of whether the source systems involved:

• Only Insertions
• Insertions plus Updates
• Insertions, Updates and Deletes

Qlik architects could write script that would load and merge those incremental changes as required. Allowing for super rapid data refreshes that had little impact on the source systems. While awesome, there were some minor issues. Architects had to know what the source systems allowed, and had to write different types of scripts for each of the use cases. Deletions were thought provoking for sure. Neither is hard to overcome because there are a ton of posts online you can find with examples. Including Qlik's own help site.

Simplicity

The only thing that could make the situation any easier is if the source systems would flat out say "You need to insert/update/delete this row of data." If they did that, Qlik data architects could simply say "Here is the existing data I already have loaded into memory, now go merge these changes that the system has."

Assuming of course that Qlik provided a MERGE function that would:

• Insert the rows that the source system said needed inserted.
• Update the rows that the source system said needed updated.
• Delete the rows that the source system said needed deleted.

The great news is that Qlik does indeed provide a MERGE function that will do just that. But what about the source systems? As it turns out whether you are using Qlik Replicate on-premise, or Qlik Cloud Data Integration in your SaaS/PaaS tenant, the change tables written provide the information needed for the merge function to work. Here is an example of data stored in the change table.

Well lookie theire. The update, insertion and deletion are clearly identified as well as the values impacted.

Here is an example of the code I wrote that passes that data to the Merge function in my load script:

To understand the merge function ... assume that I currently have a table called "DemoTable" in memory that has millions/billions of rows with the following columns in it: KeyField, DemoTable_Field1, DemoTable_Field2, DemoTable_Field3.

The "on" clause of the merge command indicates the field that represents the primary key for the table, in my case it is simply called "KeyField."

The "concatenate" clause of the merge command indicates the in-memory table that you want to merge data into/with.

The "select" clause is simply a typical SQL select statement that will pull the data that you want to merge. The first field of the return will have the values for update, insert, or delete so that the merge function will know what to do with that row.

The "where" part of the clause simply filters the selection to the rows in the change data capture table that have occurred since the last time I reloaded my data. Which in my case is stored in a variable and updated after each time the merge is performed.

Conclusion

Let's imagine that you want to quickly get changes that are flowing from your source systems with change data capture into your analytics applications being used by thousands of end users without having to touch the other millions/billions of rows of disparate data. Hard to imagine?

Not anymore, my friends, not anymore. Your load scripts can now quickly and easily incorporate these merges keeping your QVD files/applications up to date with very minimal impact on your systems, with minimal cost on your budget.

But wait, there's more! Because it is so lightweight/efficient, this merge functionality also provides the benefit of being able to be utilized via partial reloads. Meaning you can allow your end users to take action in their own sessions to perform the merge in use cases where it makes sense. Thus, enabling them to have the confidence that they are indeed seeing the very latest/greatest/newest/freshest data. You gotta be loving that.

Related Content 

If you aren't already using Qlik Replicate or Qlik Cloud Data Integration and still want to take advantage of the Merge function you can. You just need a method of getting the update, insert and delete information from the system you are reading from. If you are using Snowflake you can take advantage of their time travel functionality as well. Click this link to read my post on using Snowflake's time travel with the merge function. 

* Qlik QVD files typically offer 70-95% space savings over original data/text files. 

Analyzer Capacity Licenses can be assigned automatically based on Analyzer Capacity License Rules. These rules can be set up to provide Analyzer Capacity to users, groups, or anonymous users.

This article covers what function the license type has, how to assign it, and how to track usage. 

Index:

• What is an Analyzer Capacity license?
• How to assign Analyzer Capacity Licenses
• Assigning Analyzer Capacity in Qlik Sense Enterprise SaaS:
• Create an Analyzer Capacity License Rule in Qlik Sense Client-Managed:
• How to monitor Analyzer Capacity usage
• Qlik Cloud
• Qlik Sense Enterprise on Windows Client Managed
• In QlikView
• How Qlik Sense Analyzer Capacity Works (video)
• Related Content

What is an Analyzer Capacity license?

The Qlik Sense Analyzer Capacity license type is automatically assigned to users not assigned a Professional or Analyzer license and consumes time during an active session with the minimum session length set to 6 minutes.

Overview:

• An Analyzer Capacity license type would typically be used by users not assigned to any of the available named user types (Professional or Analyzer).
• The capabilities for the Analyzer Capacity license type are similar to the current Analyzer user type and supports the same features. 
• To use the new Qlik Sense Analyzer Capacity license type, you must install Qlik Sense February 2019 or later and activate it using the new License Service component, which is also introduced in this release.
• Analyzer Capacity works with Both Signed License Key and Signed License Definition.

Details:

• The minutes are based on a monthly subscription, which resets at 0:00 UTC time on the first day of the calendar month.
• Consumption is in 6-minute blocks. No consumption during idleness.
• Overage can be added to the subscription. See How to manage Overage for Analyzer Capacity.

How to assign Analyzer Capacity Licenses

Assigning Analyzer Capacity in Qlik Sense Enterprise SaaS:

No setup is required. If no Professional or Analyzer License was assigned, Analyzer Capacity is automatically allocated. 

See Enabling dynamic license assignment for details. 

Create an Analyzer Capacity License Rule in Qlik Sense Client-Managed:

1. Open the Qlik Sense Management Console
2. Go to Licenses Management
3. Open Analyzer Capacity Rules in the right-hand menu (See fig 1)
4. Click Create new 
   
   Fig 1
   
   
5. Provide details for your rule. In our example, we are enabling users in the group capacity to receive Analyzer Capacity Licenses. 
   
   Name: Name of your rule
   Description: Description of your rule
   Actions: Allow access 
   
   Populate the conditions accordingly. See Fig 2.
   
   Fig 2
   
   If you are looking to provide access for anonymous users, see How to allow Anonymous Hub access in Qlik Sense Enterprise on Windows.
   
   
6. Click Apply
7. All users in the defined group will now receive an Analyzer Capacity License. 
   
   
   

How to monitor Analyzer Capacity usage

Qlik Cloud

The Entitlement Analyzer for Qlik Cloud provides detailed usage data for Analyzer Capacity, but a tenant admin can gain an overview through the Management Console at any time:

1. Open the Qlik Cloud Management Console
2. Open the User allocation tab
3. Open the Overview tab
4. Review the current Analyzer capacity usage 
   
   
   
   

Qlik Sense Enterprise on Windows Client Managed

The bundled License Monitoring app provides usage data for Analyzer Capacity, but an administrator can gain an overview through the Qlik Sense Management Console at any time:

1. Open the Qlik Sense Enterprise on Windows Management Console
2. Go to License Management
3. The License usage summary tab (a) lists the current Analyzer capacity usage (b)
   
   
   
   

In QlikView

1. Open the QlikView Management Console and go to System
2. Open the Licenses tab
3. Select License 
4. Switch to the Professional and Analyzer access tab.
5. The General tab lists the current Analyzer Capacity usage 
   
   

How Qlik Sense Analyzer Capacity Works (video)

Related Content

Analyzer capacity license and monitoring (Client Managed)
Qlik Licensing Service Reference Guide 
Assigning user allocation (Qlik Cloud)
Analyzer capacity license (Qlik Cloud)
Top 5 things to know about modern Qlik Licensing

When performing an  ALTER TABLE ADD COLUMN on a DB2 z/OS  application table, the newly added column is not captured.  Even though DATA CAPTURE CHANGES is SET at the application table level,  the column is not coming through to the target. 

Resolution

There is a simple solution to this situation.  Ask the DB2 DBA to set :

ALTER TABLE SYSIBM.SYSTABLES  DATA CAPTURE CHANGES  on this system table. 

Cause 

The cause is a mere oversight of turning on this setting on the SYSIBM.SYSTABLES

Related Content 

Installing and upgrading the R4Z product in z/OS | Qlik Replicate Help

Environment

Qlik Replicate  and corresponding R4Z  -  Any Version
DB2 z/OS   - Any Version

Global expressions are not propagated to all tables. 

Defining a soft delete transformation can be done on each individual table as needed. However, if you want to do it for all tables in the task you will want to use a Global Transformation.  

Issue

The issue manifested when defining a Global Expression to do a soft delete.

The field is supposed to be automatically added to all tables in the task. The field was not added to all tables. It was random results as some tables had the field and others did not.

When reviewing the process of defining the expression we noticed there are two screens which would allow you to enter the expression.

When we used both the Transformation Scope / Advanced Options screen and the Transformation Action / Value screen, not all tables got the field. 

Example: Transformation Scope, with Advanced Options expanded:

Resolution

Environment

#Qlik Replicate 

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

Transformation - Operation Indicator - Archive User - Soft Delete

Using Engine API, creating "Hidden Bookmarks" will require Professional User license. Analyzer user, can  not create "Hidden Bookmarks".

User with "Analyzer license", will have "Access denied" when creating "Hidden Bookmarks" using Engine API explorer

{
"handle": 1,
"method": "CreateBookmarkEx",
"params": {
"qProp": {
"qInfo": {
"qId": "",
"qType": "hiddenbookmark"
},
"qMetaDef": {}
},
"qObjectIdsToPatch": [
""
]
}
}
Getting this error:
{
"jsonrpc": "2.0",
"id": 3,
"error": {
"code": 5,
"parameter": "Access denied",
"message": "Access denied"
},
"isHandled": true
}

Environment

• Qlik Sense Enterprise (all versions)

Resolution

Bug QB-17807 reported to R&D and closed as WAD (working as designed) with the following explanation.

---------------

This is by design. A professional user can create all types of object whereas an analyzer user cannot. 

---------------

Information provided on this defect is given as is at the time of documenting. For up to date information, please review the most recent Release Notes, or contact support with the ID XX-QB-17807 for reference.

Cause

Product Defect ID: QB-17807

In this article, we detail the 12 steps necessary to successfully configure Qlik Sense Enterprise on Windows after an Active Directory Domain name change or moving Qlik Sense to a new domain.

Depending on how you have configured your platform, not all the steps might be applicable. Before updating any hostname in Qlik Sense, make sure that the original value is using the old domain name. In some case, you may have configured “localhost” or the server name without any reference to the domain so there is no need in these cases to perform any update.

Scenario

In this scenario we are updating a three-node environment running Qlik Sense February 2021:

The domain has been changed from DOMAIN.local to DOMAIN2.local

The servers mentioned below are already part of the new domain name DOMAIN2.local and their Fully Qualified Domain Name (FQDN) is already updated as followed:

• QlikServer1.domain.local to QlikServer1.domain2.local: Central node
• QlikServer2.domain.local to QlikServer2.domain2.local: Rim node
• QlikServer3.domain.local to QlikServer3.domain2.local:  Dedicated Postgres and hosting the Qlik shared folder

All Qlik Sense services have been stopped on every node.

Step 1: Update the Postgres Configuration File

The first step is to update the postgres.conf file to listen on the correct hostname

1. Open the postgres.conf (make sure to make a copy) under
   • C:\ProgramData\Qlik\Sense\Repository\PostgreSQL\<version> (for an environment running the native Qlik Sense Repository Database)
   • C:\Program Files\PostgreSQL\<version>\data (for an environment running a dedicated PostgreSQL database)
2. Search for the parameter listen_addresses and update the hostname value if required
   
   
   
   
3. Save the file, close it and start the Qlik Sense Repository Database or PosgresSQL service depending if you are using the native PostgreSQL or a dedicated instance

Step 2: Backup your Qlik Sense site

Before doing any further changes, it is important to take a backup of your Qlik Sense Platform

• Backing up a Qlik Sense site

Step 3: Rename Server Node in QSR database

Now we update the node names in the database with the new Fully Qualified Domain Name.

1. Connect to the Qlik Sense Database using Installing and Configuring PGAdmin4 to access the PostgreSQL
2. Open the QSR Database and navigate through Schemas -> public ->Tables
3. Right-click on ServerNodeConfiguration -> View/Edit Data -> All Rows
4. Modify the hostname of each node if required
   
   
   
   
5. Save by clicking on the below icon available in the toolbar
   
   
   
   
6. Close PGAdmin

Step 4: Rename User Directory for existing users

1. Connect to the Qlik Sense Database using Installing and Configuring PGAdmin4 to access the PostgreSQL
2. Right-click on the QSR Database and select “Query Tool”
3. Run the following query
   

   UPDATE public."Users"
   SET "UserDirectory" = 'DOMAIN2'
   WHERE "UserDirectory" = 'DOMAIN';​

4. Validate the change by running the following query
   

   SELECT * FROM public."Users"​

   
   The User Directory should be updated to the new domain:
   
   
   
   
5. Close PGAdmin

Step 5: Update Service Cluster configuration

1. On the central node run the program C:\Program Files\Qlik\Sense\Repository\Util\QlikSenseUtil\QlikSenseUtil.exe and Connect to Database
   
   
   
   
2. Once connected, click on Service cluster and then OK
3. Update the FQDN with the new domain if required and press Save
   
   
   
   
4. Close QlikSenseUtil.exe

Step 6: Update the repository Connection Strings

1. On the central node run the program C:\Program Files\Qlik\Sense\Repository\Util\QlikSenseUtil\QlikSenseUtil.exe
2. Select Connection String Editor and click on Read
3. Update the FQDN with the new domain if required and press Save Value in config file encrypted
   
   
   
   
4. Close QlikSenseUtil.exe
5. Repeat the above steps on every rim node

Step 7: Update the dispatcher services connection strings

Verify if a hostname update is required

1. On the central node, open File Explorer and navigate to C:\Program Files\Qlik\Sense\Licenses and open the appsetting.json
2. Check if the parameter host contains the old domain name. If it does then you need to follow the below steps to update it otherwise you can jump to the next section

Update hostname on all Qlik Sense Dispatcher subservices

1. Search for the file called Configure-Service.ps1
   
   In Qlik Sense February, there are 8 of these files placed in different Qlik Sense Dispatcher sub-services. We need to run each of these files with a specific argument. In the previous or later version of Qlik Sense, the list presented below may need to be adapted.
   
   
2. Run the following command in PowerShell ISE as Administrator
   

   cd "C:\Program Files\Qlik\Sense\NLAppSearch\install"
   .\Configure-Service.ps1 QlikServer3.domain2.local 4432 qliksenserepository <qliksenserepository_password>
   
   cd "C:\Program Files\Qlik\Sense\AppDistributionService"
   .\Configure-Service.ps1 QlikServer3.domain2.local 4432 qliksenserepository <qliksenserepository_password>
   
   cd "C:\Program Files\Qlik\Sense\HybridDeploymentService"
   .\Configure-Service.ps1 QlikServer3.domain2.local 4432 qliksenserepository <qliksenserepository_password>
   
   cd "C:\Program Files\Qlik\Sense\Licenses"
   .\Configure-Service.ps1 QlikServer3.domain2.local 4432 qliksenserepository <qliksenserepository_password>
   
   cd "C:\Program Files\Qlik\Sense\NotifierService\install"
   .\Configure-Service.ps1 QlikServer3.domain2.local 4432 qliksenserepository <qliksenserepository_password>
   
   cd "C:\Program Files\Qlik\Sense\MobilityRegistrarService\install"
   .\Configure-Service.ps1 QlikServer3.domain2.local 4432 qliksenserepository <qliksenserepository_password>
   
   cd "C:\Program Files\Qlik\Sense\PrecedentsService\install"
   .\Configure-Service.ps1 QlikServer3.domain2.local 4432 qliksenserepository <qliksenserepository_password>
   
   cd "C:\Program Files\Qlik\Sense\DataPrepService\install"
   .\Configure-Service.ps1 QlikServer3.domain2.local 4432 qliksenserepository <qliksenserepository_password>

3. Repeat the above steps on every node (Including the verification)

Step 8: Update the Qlik Logging Service

If you are using the Qlik Logging Service;

1. Run the following command in Command Prompt:
   

   cd "C:\Program Files\Qlik\Sense\Logging"
   Qlik.Logging.Service.exe validate​

2. If you get something like:
   
   Failed to validate logging database. Database does not exist or is an invalid version.
   
   Then it probably means that the hostname needs to be updated
   
   
3. If so, run the following command in Command Prompt:
   

   cd "C:\Program Files\Qlik\Sense\Logging"
   Qlik.Logging.Service.exe update -h QlikServer3.domain2.local​

4. Once done, run the validate command again to verify that the connectivity is working
   
   
   
   
5. Repeat the above steps on every rim node

Step 9: Update the service account running the Qlik Sense services

1. On the central node, open the Windows Service Console an update each Qlik Sense services with the new domain (DO NOT START THE SERVICE AT THIS POINT)
   
   
   
   
2. Repeat the above step on every rim node
   
   

Step 10: Update the host.cfg and remove the certificate

1. On the central node login as the service account
2. Backup the Qlik Sense certificate following Backing up certificates
3. Remove the certificate you have previous backed up from the MMC
4. Make a copy of %ProgramData%\Qlik\Sense\Host.cfg and rename the copy to Host.cfg.old
   • Host.cfg contains the hostname encoded in base64. You can generate this string for the new hostname using a site such as https://www.base64encode.org/ (You may want to decode the original string to see if it contains the old domain name.
5. Open Host.cfg and replace the content with the new encoded hostname.
6. Run Windows Command Prompt as Administrator and execute the following command:
   

   "C:\Program Files\Qlik\Sense\Repository\Repository.exe" -bootstrap -iscentral -restorehostname​

7. In parallel start the Qlik Sense Dispatcher services
8. When the command has completed successfully you should see the following message in command prompt:
   
   Bootstrap mode has terminated. Press ENTER to exit..
   
   
9. Start every Qlik Sense services on the central node and try to access the QMC with https://localhost/qmc

Step 11: Distribute the certificate on the rim node(s)

1. On the rim node(s) login as the service account
2. Backup the Qlik Sense certificate following Backing up certificates
3. Remove the certificate you have previous backed up from the MMC
4. Start the Qlik Sense services
5. On the central node, open the QMC and go to Nodes
6. After a few minutes the rim node should have the status The certificates has not been installed
   
   
   
   
7. Click on Redistribute and follow the instructions to redistribute the certificates on the rim node(s)
8. After a few minutes, the services should be running
   
   

Step 12: Additional updates

User Directory Connector

If you are syncing your users with a User Directory Connector you will need to change the LDAP path to point to the new domain.

1. In the QMC -> User Directory Connector
2. Edit your existing User Directory Connector, check the LDAP Path and update if necessary
   
   

Monitoring Application

You will need to update the monitoring apps and their associated data connection to use the new Fully Qualified Domain Name

• Configuring the Monitoring apps

Data Connections password

• Part of the steps followed earlier, the certificates had to be recreated. As a result, the passwords for the existing data connection needs to be re-typed and saved for all Data Connections and User Directory Connectors that include password information.

Security Rules and Licenses rules

It is possible that you have created rules based on the User Directory. As you have changed the domain name, the user directory has also changed in Qlik Sense so you will need to update those to reflect that.

Environment

• Qlik Sense Enterprise on Windows 

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 Qlik Community is a great resource where all Qlikkies can connect, collaborate and share with their peers, Qlik support engineers, Qlik product management, and Qlik experts!

Since multiple forums are available for each product, community users can post discussions in the most relevant area. Both new and experienced Qlik product users help others learn new tips, share information and provide solutions by replying to discussions. Qlik product managers, Qlik support engineers, Qlik product and services experts as well as Qlik community administrators are moderating all of the forums and blogs. Look for the headset icon to identify Qlik Support responses and the star to designate other Qlik employee responses.

What types of questions should be asked in the Qlik Community?

Search first before posting. Chances are someone else has already posted your question and answer.  If the search does not result in an answer to your question, continue with posting on the Qlik Community. For step by step instructions on posting a question, check out this post.  Any kind of question can be asked on the Qlik Community!

The Community is an excellent resource for asking how-to, scripting, app development and best practice questions. Some examples of questions include but are not limited to:

• How do I connect using a specific connector?
• How do I use a certain function?
• My syntax is not working as expected, what am I doing wrong?
• What kind of visualization works best for this scenario?
• How do I format a number?
• What can I do to improve data load times?
• What is the approach for creating my data model?

Note:  Qlik Community pages, Qlik Help documents, and Qlik public knowledge articles are all regularly indexed by the major search services like Google.  Be sure to append the product name when searching to retrieve specific results regarding Qlik (ex: Qlik, Qlik Sense, QlikView, NPrinting, etc).  Qlik Community results as well as results from Qlik.com, Qlik Help and Qlik Support Portal display.   

What information should I include when posting to the Qlik Community?

The more information the better! Depending on the question, more details may be required to get the conversation flowing. Try to provide as much detail as possible and don't be surprised if peers or other product experts may ask for more information. A solid post should include:

• A detailed description/ask that includes examples
• Provide screenshots to illustrate the issue
• A sample file that users can test with (examples:  .qvf, .qvw, .qvd)
• Code snippets 
• Error messages
• Do not include any sensitive data! Files uploaded to the community will be publicly available.  

Once the question has been posted, make sure to set up notifications so you do not miss when others respond to and like your post. If a response is received that answers the question or solves the issue, be sure to mark the answer as correct by clicking 'Accept as Solution'. This will mark the post as solved and other Qlikkies will gravitate towards this post as it as a possible solution for their issue. Multiple responses can be accepted as a solution so make sure to select all that apply.

How can I participate in this great Community?

It's easy! Create a Qlik Community login and start surfing the forums. Add your thoughts and ideas to any post, let others know you've experienced the same behavior, or like any resolution that has helped you answer a question. Be kind, encouraging and offer any insight you can! Don't be afraid to post a question as others probably have the same one. For more information on using the Qlik Community, start here!  Once you get started, navigate to our popular blogs and subscribe.  Join a group.  There so much you can learn and share when you are connected to the Qlik Community.  

Thank you for being apart of this wonderful Community! Happy Qlikking!

Starting at Qlik Sense Enterprise on Windows May 2021, the bundled PostgreSQL has been upgraded to version 12.5.

However, PostgreSQL 12.5 will only be deployed during a fresh installation of Qlik Sense Enterprise on Windows May 2021. This means that, if you are upgrading to Qlik Sense Enterprise on Windows May 2021, PostgreSQL will remain on 9.6.

For many organizations, running on the latest supported PostgreSQL version is a security requirement. To achieve this:

• Move Qlik Sense database to standalone PostgresSQL database in which case you will have control of which PostgreSQL version is deployed as long as it is supported by the system requirements
• Continue to use the bundled PostgreSQL with version 12.5 (see the process below)

Content:

• Step 1: Upgrading your Qlik Sense site
• Step 2: Backing up your Qlik Sense site
• Step 3: Uninstall Qlik Sense on the central node
• Step 4: Install Qlik Sense Enterprise on Windows May 2021 on the central node
• Step 5: Restoring your Qlik Sense site

The information in this article is provided as-is and to be used at your own 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.

This process requires a full reinstallation of the product. Despite the fact that a backup is being taken as part of the process, it is highly recommended to have a second backup plan in place such as a Virtual Machine snapshot and/or a Server Backup.

It is highly recommended to test this process in a test environment to get familiar with it.

Step 1: Upgrading your Qlik Sense site

The first step is to upgrade your environment to Qlik Sense Enterprise on Windows May 2021 following the steps and recommendations provided on this link.

Once the upgrade is done make sure your Qlik Sense site is completely operational.

Step 2: Backing up your Qlik Sense site

At this point, you have an operational Qlik Sense site running on Qlik Sense Enterprise on Windows May 2021, however, the bundled PostgreSQL is still on version 9.6.

The next step will be to backup your entire Qlik Sense site following these instructions (including the certificates) . The backup is essential as it will need to be restored later on.

Step 3: Uninstall Qlik Sense on the central node

You will now need to uninstall Qlik Sense from the central node following these instructions.

During the uninstall:

• Have all rim nodes stopped.
• Don't check the box Remove Qlik Sense demo apps, certificates, and data folders.
• After uninstall and before reinstall rename C:\programdata\qlik to C:\programdata\qlik.old and rename C:\program files\qlik to C:\program files\qlik.old (if you changed it on another drive adjust accordingly)

Step 4: Install Qlik Sense Enterprise on Windows May 2021 on the central node

Now that Qlik Sense Enterprise on Windows May 2021 is uninstalled on the central node, you will reinstall it as a result will deploy a bundled PostgreSQL 12.5. Instructions available here.

• select the same host name and database settings as before
• The file share can point at a temporary one since you will take the original one again after restoring the QSR database in step 5.

Do not check the box Start theQlik Senseservices when the setup is complete at the end of the installer.

Step 5: Restoring your Qlik Sense site

You now have a clean Qlik Sense Enterprise on Windows May 2021 deployed on your central node with PostgreSQL 12.5. You now need to restore your environment to retrieve all your configurations and apps.

Please follow the Restoring a Qlik Sense site (including the certificates).

Note: While restoring the database backup you will most likely receive the following error which can be ignored. This error is caused by the version discrepancies between the pg_dump that made the dump file and the pg_restore that's restoring it - in example files coming from PostgreSQL version 9.6 vs files coming from PostgreSQL version 12.5. QSR backup taken and restored with the same version of PostgreSQL will not produce that error.

pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 3; 2615 2200 SCHEMA public postgres
pg_restore: error: could not execute query: ERROR: schema "public" already exists
Command was: CREATE SCHEMA public;

pg_restore: warning: errors ignored on restore: 1

Once the environment is restored, you should be able to start your central node and rim nodes successfully.

Environment

Qlik Sense Enterprise on Windows May 2021 and later

Within the available connectors in Qlik Application Automation are Microsoft Onedrive & Microsoft Sharepoint . This article intends to describe some potential use cases that can be performed through the use of these connectors and provide an easy to use template to do so. Most examples are built using the Onedrive connector, however the same use cases can be performed for shared drives using the Sharepoint connector.

• Monitoring changes to a user's drive.
• Automatically modifying file locations and renaming of files
• Create sharing links for files and share with emails
• Set up sharing permissions for files.

Authentication for the connector happens with oAuth2. We are making use of the following scopes for the Onedrive connector:

• Files.ReadWrite.All
• User.Read
• offline_access

These scopes should be accessible for all users in a default Office 365 tenant. If you are having troubles attempting to authorize Qlik Application Automation to obtain these scopes consult your Office 365 administrator.

Monitoring changes to a user's Onedrive

Using Automations in Qlik Cloud it is possible to monitor changes to a user's personal Onedrive or to a Sharepoint shared drive. When changes to a personal or shared drive happen, automatic actions can be performed, such as automatically obtaining data from an uploaded excel spreadsheet and updating a Qlik Sense app.

Onedrive makes use of a delta token to view changes that have happened since that token. As we do not have a data store within Automations, we are bound to use a separate store for the delta tokens. Options could be an excel file / text file stored on Onedrive, an app on Qlik Sense or any other place where data is persisted.

An example automation to monitor changes might look like the following. The automation is also added as JSON to the article to import into a Qlik Cloud tenant.

Modifying file locations and renaming files

The Microsoft Onedrive and Microsoft Sharepoint connectors in Qlik Application Automation provide blocks to Move and Rename items on drives. These can be used for example in conjunction with other connectors to perform these actions whenever … happens

An example could be to move all files from an incoming folder to an archive folder after a Qlik Sense reload is complete.
A simple example automation is added as an attachment to this article. In the automation editor this might look like the following:

Create sharing links for files and share with emails

The block Create Sharing Link can create a shareable URL that can be send over email to a user.
It offers the user the choice whether the URL works for the organization of the user or anonymous. The scope can be set to view, edit or embed and it is possible to provide a password as well as an expiration time for the shareable link.

The following automation would obtain data from a table in Qlik Sense, copy the rows to an Excel file hosted on Onedrive, create a shareable link and share this link to a user by email:

Setup sharing permissions for files

Sharing permissions can be done with the Send Sharing Invitation. This block sends an invitation to an email recipient including a message, subject, reading or writing permissions and the choice if an invitation has to be sent out and whether a user has to be signed in. A password can also be provided, but only if the user is using a personal Onedrive account.

Furthermore it is possible to list sharing permissions, updating these permissions and removing permission to access a file on a user's Onedrive.

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.