Authentication and Authorization

Authentication and authorization with the Microsoft Excel Connector in Qlik Application Automation works through oAuth2. When a user attempts to connect a Microsoft Excel datasource, the user will be redirected to the Microsoft login page where a user is prompted to give permission for Qlik Application Automation to access their Office 365 tenant.

The following scopes are being requested by Qlik Application Automation:

• Offline_access
• User.Read
• Files.ReadWrite.All

Working with excel worksheets

The Microsoft Excel connector has blocks available to perform actions on worksheets, data inside worksheets and tables inside worksheets. When working with data, we always recommend to work with tables rather than data on the worksheet due to ease of use.

Listing rows from Excel File

This block obtains values from an excel file worksheet from a specified range. The following input parameters are used:

• Drive ID: This is a lookup that will query a user's drive.
•  Item ID: ID of an item. Using the lookup a user can query a specific folder to search for excel sheets
• Worksheet name: Can be selected using lookup or alternatively the worksheet name or ID can be manually provided.
• Start Cell: The upper left bound cell of the range to return.
• End Cell: The lower right bound cell of the range to return.

The block returns a double nested array. The first level contains a row, the second level contains the value of a column in that row.

The same block also exists for working with tables. Here the start cell and end cell are replaced by a Table ID.

Create a new worksheet

With the block Add Worksheet to Excel File a user can add a new worksheet to an Excel document. It takes the following input parameters:

• Item ID: ID of an item. Using the lookup a user can query a specific folder to search for excel sheets
• Name: Name for the new worksheet name.

Updating Rows in a Worksheet

Using the block Update Rows in Excel Sheet you can specify a range in which you want to overwrite existing data. The following input parameters are required:

• Drive ID: This is a lookup that will query a user's drive. A sharepoint drive ID can also be provided.
• Item ID: ID of an item. Using the lookup a user can query a specific folder to search for excel sheets
• Worksheet name: Can be selected using lookup or alternatively the worksheet name or ID can be manually provided.
• Start Cell: The upper left bound cell of the range to return.
• End Cell: The lower right bound cell of the range to return.
• Values: A double nested array containing data for each cell in the specified range. This has to match the range specified by the Start Cell and End Cell inputs. It is strongly recommended to work with variables and construct a listOfRows variable. This variable should be a list and it should be filled with multiple lists. An example could look like the following:

Create a new table

Using the block Create Excel Table With Headers a user can create a table in Excel. Using the block Add Table to Excel File Worksheet a user can also create a table without headers. The following input parameters are being requested:

• Drive ID: This is a lookup that will query a user's drive.  A sharepoint drive ID can also be provided.
• Item ID: ID of an item. Using the lookup a user can query a specific folder to search for excel sheets
• Worksheet: When item is selected, a lookup can be used to search for the different sheets in which the table has to be placed. You can also provide the name or ID of the worksheet.
• Start Row: The row where the headers will be placed. Indexes in Excel start from 1.
• Start Column: Use alphabetic letter.
• End column: Use alphabetic letter.
• Headers: Array of strings to use as header values. Keep in mind that the number of columns selected using the start column and end column have to match with the headers array. E.g. C-E as column start/end for an array of 3 headers.
• Name: Name of the table for easy references.

Aside from creating a table, we can also delete is by the use of the Delete Table from Excel File Worksheet block. This requires an item ID, worksheet and Table ID.

Find Row(s) in Excel Worksheet Table

Two blocks serve to find a matching row in an Excel Table. These blocks are Find Row in Excel Worksheet Table and Find Rows in Excel Worksheet Table. The first one will only return the first row found, the second one will return all rows found matching these criteria. The following input parameters are required:

• Drive ID: This is a lookup that will query a user's drive.
• Item ID: ID of an item. Using the lookup a user can query a specific folder to search for excel sheets
• Worksheet: When item is selected, a lookup can be used to search for the different sheets in which the table has to be placed. You can also provide the name or ID of the worksheet.
• Table ID: Can be obtained using the lookup once all above fields have been filled out.
• Column Index For Search: The index of the column that is being searched for a value. The indexes are zero based.
• Search Value: The value to search for. Only fully matching values will work.

Sharing workbooks

Functionality to share workbooks is not included in the Microsoft Excel connector. These actions can be done using the Microsoft Onedrive or Sharepoint connector.

Limitations

• Cannot create a new excel file.

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.

This guide provides step-by-step instruction on how to create an UDC that fetches users from AD in the most common way: default Active Directory UDC. The steps use LDAP Admin for testing the LDAP filter string.

For steps that do not require LDAP Admin see Qlik Sense: Configuring and testing LDAP filters for User Directory Connector  

For connecting AD using Generic LDAP connector, follow article How to connect to Active Directory using the Generic LDAP Connector

Detailed steps:

1. Confirm current server is Central Node and Repository Service is running correctly.

2. In order to confirm AD connectivity, download a 3rd party tool called "LDAP Admin" and run it on current server.

3. In LDAP Admin, create a connection.

4. For the detailed information, consult your Domain Administrator.

5. Make sure the connection passes test.

6. Once connected, go to Search > Custom > set "Path" to the top level > Input appropriate LDAP filter. Make sure there are some users fetched successfully.

For any issues happening before this point, please contact Domain Administrator. For any issues happening after this point, please contact Qlik Support.

[VERY IMPORTANT] Before moving forward, confirm if there is any RootAdmin assigned to a domain user in Qlik Sense.

If there is, make sure that user appears in the search result of above filter otherwise it will be marked as inactive and could potentially lock users out from QMC. Also follow How to avoid the RootAdmin(s) from becoming inactive. But this step should not be relied on so please still make sure the filter fetches current RootAdmin.

7. Go to QMC > User directory connector > Create new > Active Directory:

Name: Give this UDC a user-friendly name
Sync user data for existing users: Only uncheck this setting if you know the amount of users is reasonably low -- for instance, less than a thousand. If not sure, always keep it checked.
Path: LDAP://["host" used in step 3]
Username / password: Credentials used in step 3
Under certain conditions, these fields can be left blank.
Additional LDAP filter: Filter used in step 5

9. Hit Apply, go back and do a Sync. Confirm the Sync is successful.

10. Go to QMC > Users > Filter by "User directory". Confirm the users are fetched.

 Note: if "Sync user data for existing users" is checked in step 8, this list might be shorter than expected. That is because UDC would not fetch the users who are not already in the list.
Again, for any issues happening before step 6, please contact Domain Administrator. For any issues happening after step 6, please contact Qlik Support.

Related Content:

• Qlik Sense: Configuring and testing LDAP filters for User Directory Connector  

With content cache-controls, you can modify the cache behavior of the browser. The cache-control is used on endpoints handled by the repository service. This functionality is disabled by default and can be enabled by modifying the files Repository.exe.conf and capabilities.json, followed by a restart of the Qlik Sense Service Dispatcher and the Qlik Sense Repository Service.

Cache-control for endpoints handled by the repository service was introduced in Qlik Sense Enterprise on Windows February 2022 (Patch 1). In the May 2022 release further enhancements were made to extend caching policies for requests specific to the Hub service. Upgrade Qlik Sense to make use of the new features. 

In this article, we walk you through modifying the cache control policy and how to apply it globally across your Qlik Sense Enterprise on Windows environment. 

Before modifying the cache-controls

Please note the following before modifying cache control headers:

• It is generally not recommended to manually alter the cache-control headers.
• Make sure that you understand how the browser caching behavior is affected by the changes.

• Prior to applying any changes in a production environment, evaluate the risks of the changes.

Enable the feature

The feature is disabled by default.  For details, see Configuring content cache-control.

1. Open C:\Program Files\Qlik\Sense\Repository\Repository.exe.conf
   
   
2. Modify the following key and set it to true:
   
   <add key="ContentCacheControl" value="true" />
   
   Save the file.
   
   
3. Open C:\Program Files\Qlik\Sense\CapabilityService\capabilities.json
   
   
4. Add the following flag:
   
   {"contentHash":"2ae4a99c9f17ab76e1eeb27bc4211874","originalClassName":"FeatureToggle","flag":"QMC_CONTENT_CACHE_CONTROL","enabled":true}
   
   Save the file.
   
   
5. Restart the Qlik Sense Repository Service and Qlik Sense Dispatcher Service

Modify cache-controls in the Qlik Sense Management Console

1. After enabling cache-control, open the Qlik Sense Management Console
2. Go to Content Libraries
   
   
   
   
3. Open the Content Library you wish to configure
   
   
   
   
4. In the Associated Items menu, select Content cache-controls
5. Click Link new content cache control
   
   
   
   
6. Configure the cache-controls as per Content cache-controls  and click Add
   
   The cache control will automatically be linked.
   
   
   
   
7. Restart the Qlik Sense Service Dispatcher and Qlik Sense Repository Service

These are the predefined cache-control header values:

"CachePolicyEnum": {
    "values": [
        "0: Normal", -> public, max-age=3600
        "1: MustRevalidate", -> public, must-revalidate, max-age=0
        "2: PrivateNormal", -> private, max-age=3600
        "3: PrivateMustRevalidate", -> private, must-revalidate, max-age=0
        "4: NoStore" -> no-store
    ]
}

To modify the cache-control policy on a global level

You can create a global cache control policy (not tied to a content library) by using either the API or an SQL query against the QRS database. A restart of the Qlik Sense Service Dispatcher and Qlik Sense Repository service is required after.

API example:

POST /qrs/ContentCacheControl

{
    "id": "e07ca071-7d72-417d-93c2-d60687f747a8",
    "name": "api",
    "contentLibrary": null,
    "filter": "api",
    "maxAge": 3600,
    "cachePolicy": 4,
    "privileges": null
}

Database Query example:

INSERT INTO public."ContentCacheControls" ("ID", "CreatedDate", "ModifiedDate", "ModifiedByUserName", "Name", "Filter", "MaxAge", "CachePolicy", "ContentLibrary_ID")
VALUES ('e07ca071-7d72-417d-93c2-d60687f747a8', '2022-08-19 14:28:48.077709', '2022-08-19 14:28:48.077709', 'RDLUND\svc-silver', 'api', 'api', 3600, 4, null);

• Please update the values of "CreatedDate", "ModifiedDate", "ModifiedByUserName" keys and keep the rest of them unchanged. The ID can be any unique quid value.
• Please modify the value of "Filter" to a customised filter by using regex as per Content cache-controls.

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 

What's new in Qlik Sense February 2022 
What's new in Qlik Sense May 2022 

Environment

Qlik Sense Enterprise on Windows 

Internal Investigation ID(s):

QB-11410

Installing, upgrading, and managing the Qlik Cloud Monitoring Apps for user-based subscriptions has just gotten a whole lot easier! With two new Qlik Application Automation templates coupled with Qlik Data Alerts, you can now: 

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 required for the data connection on a schedule using an out-of-the-box Qlik Application Automation template. This ensures the data connection is always operational. 
3. Get alerted whenever a new version of a monitoring app is available using Qlik Data Alerts. Repeat Step 1 to complete the update. 

The above allows you to deploy the monitoring apps to your tenant with a hands-off approach. Dive into the individual components below. 

Content:

• Part One: 'Qlik Cloud Monitoring Apps for user-based subscriptions' template overview
• Part Two: 'Qlik Cloud Monitoring Apps API Key Rotator' template overview
• Part Three: How to setup a Qlik Alert to know when an app is out of date
• A Qlik Cloud Monitoring Apps Workflow Demo (Video)
• FAQ

Part One: 'Qlik Cloud Monitoring Apps for user-based subscriptions' template overview

This automation template is a fully guided installer/updater for the Qlik Cloud Monitoring Applications, including but not limited to the App Analyzer, Entitlement Analyzer, Reload Analyzer, and Access Evaluator applications. Leverage this automation template to quickly and easily install and update these or a subset of these applications with all their dependencies. The applications themselves are community-supported; and, they are provided through Qlik's Open-Source Software GitHub and thus are subject to Qlik's open-source guidelines and policies.

For more information, refer to the GitHub repository.

Features

• Can install/upgrade all or select apps.
• Can create or leverage existing spaces.
• Programmatically handles pre-requisite settings, roles, and entitlements.
• Installs latest versions from Qlik’s OSS GitHub.
• Creates required API key.
• Creates required analytics data connection.
• Can create custom reload schedules.
• Can reload post-install.
• Tags apps appropriately to track which are installed and their respective versions.

Click-through installer:

Flow:

Note that if the monitoring applications have been installed manually (i.e., not through this automation) then they will not be detected as existing. The automation will install new copies side-by-side. Any subsequent executions of the automation will detect the newly installed monitoring applications and check their versions, etc. This is due to the fact that the applications are tagged with "QCMA - {appName}" and "QCMA - {version}" during the installation process through the automation. Manually installed applications will not have these tags and therefore will not be detected.

 

Part Two: 'Qlik Cloud Monitoring Apps API Key Rotator' template overview

This template is intended to be used alongside the Qlik Cloud Monitoring Apps for user-based subscriptions template. This automation provides the ability to keep the API key and associated data connection used for the Qlik Cloud Monitoring Apps up to date on a scheduled basis. Simply input the space Id where the monitoring_apps_REST data connection should reside, and the automation will recreate both the API key and data connection regularly. Ensure that the cadence of the automation’s schedule is less than the expiry of the API key.

Configuration

Enter in the Id of the space where the monitoring_apps_REST data connection should reside.

Ensure that this automation is run off-hours from your scheduled monitoring application reloads so it does not disrupt the reload process.

Part Three: How to setup a Qlik Alert to know when an app is out of date

Each Qlik Cloud Monitoring App has the following two variables:

• vLatestVersion: the latest version of the app released on Qlik’s OSS GitHub.
• vIsLatestVersion: a Boolean of whether the app’s current version is equal to the latest version of the app released on Qlik’s OSS GitHub (1 if true, 0 if false).

With these variables, we can create a new Qlik Data Alert on a per-app basis. For each monitoring app that you want to be notified on if it falls out of date:

1. Right-click on a chart in the app, select Alerts and then Create new alert.
2. Clear out the defaults and provide the alert with a name such as <Monitoring App Name> is Out of Date.
3. Select Add measure and select the fx icon to enter a custom expression. Enter the expression ’$(vLatestVersion)'. Ensure it is wrapped in single quotes, then provide the label for the expression Latest Version.
4. Select Add measure and select the fx icon to enter a custom expression. Enter the expression $(vIsLatestVersion), then provide the label for the expression Is Latest Version.
5. Confirm the Details & data tab resembles the following:
   
   
   
   
6. Navigate to the next tab, Conditions.
7. Set the Measure to Is Latest Version, the Operator to Equal to, and the Value to 0. This way, if the app is not the latest version (0), it will trigger the alert.
8. Confirm that the Conditions tab resembles the following:
   
   
   
   
9. Navigate to the Distribution tab and confirm that the alert is evaluated When data is refreshed.

Here is an example of an alert received for the App Analyzer, showing that at this point in time, the latest version of the application is 5.1.3 and that the app is out of date:

A Qlik Cloud Monitoring Apps Workflow Demo (Video)

FAQ

Q: Can I re-run the installer to check if any of the monitoring applications are able to be upgraded to a later version?

A: Yes. Run the installer, select which applications should be checked and select the space that they reside in. If any of the selected applications are not installed or are upgradeable, a prompt will appear to continue to install/upgrade for the relevant applications.

Q: What if multiple people install monitoring applications in different spaces?

A: The template scopes the applications install process to a “target” space, i.e., a shared space (if not published) or a managed space. It will scope the API key name to `QCMA – {spaceId}` of that target space. This allows the template to install/update the monitoring applications across spaces and across users. If one user installs an application to “Space A” and then another user installs a different monitoring application to “Space A”, the template will see that a data connection and associated API key (in this case from another user) exists for that space already and it will install the application leveraging those pre-existing assets.

Q: What if a new monitoring application is released? Will the template provide the ability to install that application as well?

A: Yes. The template receives the list of applications dynamically from GitHub. If a new monitoring application is released, it will become available immediately through the template.

Q: I would like to be notified whenever a new version of a monitoring applications is released. Can this template do that?

A: As per the article above, the automation templates are not responsible for notifications of whether the applications are out of date. This is achieved using Qlik Alerting on a per-application basis as described in Part 3.

Q:I have updated my application, but I noticed that it did not preserve the history. Why is that?

A: The history is preserved in the prior versions of the application’s QVDs so the data is never deleted and can be loaded into the older version. Each upgrade will generate a new set of QVDs as the data models for the applications sometimes change due to bug fixes, updates, new features, etc. If you want to preserve the history when updating, the application can be upgraded with the “Publish side-by-side” method so that the older version of the application will remain as an archival application. However note that the Qlik Alert (from Part 3) will need to be recreated and any community content that was created on the older application will not be transferred to the new application.

This article provides an overview of how to manage space membership for groups using a Qlik Application Automation. This approach can be useful when migrating from QlikView, or Qlik Sense Client Managed, to Qlik Sense Cloud when security concerns prevent the usage of Qlik-CLI and Powershell scripting.

Environment

• Qlik Application Automation

Prerequisites 

• Access to Qlik Cloud tenant
• User assigned the role of Tenant Admin
• Office 365 account (for Microsoft Excel).

Sheet Configuration

In this example, we use Microsoft Excel to configure group Space memberships. A sheet name, for example, Groups, must be added and this must also be provided as input when running the automation. The sheet must also contain the following headers:

• SpaceName
• Group
• Roles
• Flag

Automation Configuration

1. Add an Input block that takes the name of the worksheet (from which data should be read) as input. You also need to specify the first and last cell to read data from, as well as if groups are to be added or deleted.
    
   • Worksheet Name: Name of the Excel Worksheet
   • Excel Start Cell: Enter the Start cell of your Excel Worksheet. Example: A1
   • Excel End Cell: Enter the End cell of your Excel Worksheet. Example: Z23
   • Mode: Add or Delete.
     
     
2. Add two variables of the type number. One to keep track of added groups and one to keep track of deleted groups. These variables will be used to update the run title when the automation has been completed. Observe that only one of them would be used when running the automation since only one of the run modes can be used (Add or Delete).
   Let's call the variables as vGroupsAddedCounter and vGroupsDeletedCounter.
   
   
3. Retrieve the information from the Excel sheets using the List Rows With Headers block.
   
   • Use the "do lookup" function to find the Drive ID and Item ID of the file you will be using as a source. 
   • Configure the other fields to get the values from the input fields.
     
      
     
     
4. Retrieve all the spaces from the tenant using the Search Spaces block inside the List Rows With Headers loop.
   
   
5. Add a Condition block and check if the space exists and if we're in Add mode.
   
   
   
   
6. If the condition is true, list all the members from the space using the List members from Space block by inputting the space ID. 
   
   
7. Retrieve the groups using the Filter Groups block by inputting the filter condition.
   
   
   
   
8. Add a Condition block that checks if the group exists and whether or not the group is a member of the space.
   
   
9. If the conditions are true, add the group using the Add Member To Space block.
   
   
10. After the Add Member To Spaceblock, add a variable block and increment vGroupsAddedCounter with 1.
    
    
11. If the first condition in the List Rows With Headers loop is false, then use a third Condition block to find out if we're in Delete mode and if the space exists.
    
    
    
    
12. If the condition is true, list all the members from the space using the List Members From Space block by inputting the space ID. 
    
    
13. Retrieve the groups using the Filter Groups block by inputting the filter condition.
    
    
14. Add a Condition block that checks if the group exists, and if the group is a member of the space and we delete it.
    
    
15. If the condition is true, delete the group using Delete Member From Space block.
    
    
16. After the Delete Member From Space block, add a variable block and increment vGroupsDeletedCounter with 1.
    
    
17. Lastly, add a Condition block that checks the run mode and updates the automation's run title to display how many groups have been added or deleted.
    
    

     

Considerations

• A multitenant scenario can be supported by utilizing the Qlik Platform Operations connector instead of the Qlik Cloud Services connector. 
• Google Sheets could be used instead of Microsoft Excel.

Qlik Sense patches can be downloaded from the Qlik Download page. Patches are cumulative, and only one patch can be installed at a time.

Example: Patch 4 for Qlik Sense February 2019 will include all fixes from 3, 2, and 1.

Before applying a patch:

• Read the Release Notes to locate any dependencies of new features or changes to features which can affect your environment
• Verify and record all customisations (manual changes to setting files or configuration files not otherwise represented in the GUI). While patches generally do not change settings or revert configuration files to default, a drastically changed feature (see release notes) can impact customisations.

To install:

1. Download the required patch 
2. Schedule a brief downtime as the server will need to be restarted
3. Execute the patch installation package and follow the on-screen instructions
4. Restart the Windows server host

To uninstall:

Executing a patch installation package will automatically uninstall the previous patch and install the new one. If you need to uninstall a patch manually:

1. Open the Windows Add and Remove Programs menu
2. Click View installed updates from the left panel
3. Locate the Patch
4. Uninstall the Patch
5. Restart the Windows Server host

If uninstall through Control Panel is not possible:

1. Locate the Qlik_Sense_update.exe installer package in C:\ProgramData\Package Cache\[guid]\.
   
   If the package required for the uninstall has been removed or renamed, a possible workaround is to obtain an exact copy (same version, release) of the Qlik_Sense_update.exe from a different environment and manually replacing it.
   
   
2. Right click the Qlik_Sense_update.exe and click Uninstall

Related Content:

Custom configuration file changes not kept after Qlik Sense upgrade 

End of Support for Qlik products follows Release Management Policy, as documented in the Product Terms found on the Qlik Site: On-Premise Products Release Management Policy

For detailed information on end of life and release dates, see:

Qlik Sense Enterprise on Windows Product Lifecycle  
Qlik NPrinting Product Lifecycle 
QlikView Product Lifecycle  

Related Content:

• NPrinting 16 to NPrinting 19+ Migration Playbook
• QlikView Release Management and End of Life
• On-Premise Products Release Management Policy

This article describes how to resolve the NPrinting connection verification error:

x Qlik NPrinting webrenderer can reach Qlik Sense hub error

Environment

• NPrinting: all versions
• Qlik Sense: all versions
  • multi-node QS proxy environment
  • NLB (Network Load Balancer or Alias URL) server address used in front of cluster of Qlik Sense proxy nodes (which are linked to a Virtual Proxy. Proxy nodes will not work with NPrinting unless they are linked to a QS virtual proxy)

Resolution

• Although it is possible to use an NLB address in front of your QS proxy nodes that are used by NPrinting, the error generated in the NP connection verification process will appear as described in the article title
• This error can safely be ignored as this does not affect generation of NPrinting connections
• To eliminate the 'error' message, you would need to update your NPrinting connection to use a virtual proxy node address/computer name behind the NLB address . It must be the computer name and not an alias url).
• The proxy node used must be linked to a virtual proxy with the following required attribute
  
  • Microsoft Windows NTML authentication on the Qlik Sense proxy. (SAML and JWT are not supported. If your virtual proxy uses SAML or JWT authentication, you need to add a new virtual proxy with NTLM enabled for Qlik NPrinting connections).
  • A link between the proxy node and virtual proxy.
  • For all NPrinting to Qlik Sense requirements visit https://help.qlik.com/en-US/nprinting/Content/NPrinting/DeployingQVNprinting/NPrinting-with-Sense.htm#anchor-1

Related Content

• https://help.qlik.com/en-US/nprinting/Content/NPrinting/Troubleshooting/Verify-connection-to-Sense-errors.htm
• https://help.qlik.com/en-US/nprinting/Content/NPrinting/DeployingQVNprinting/NPrinting-with-Sense.htm#Requirem

Internal Investigation ID

• QB-2871

The Qlik Sense Repository Service can suddenly come to a situation where the CPU utilization of the central node increases beyond 95%, which causes effects in the Qlik Sense hub (sheets go blank and other unexpected slow behavior). The process that is consuming the maximum CPU can be seen as the Qlik Sense Repository Service. 

Identified in a Problem Investigation with R&D, one of the possible causes is related to large number number of open transactions in the repository database and locks held by large number of sessions impacting on the repository service communication with the repository database. 

Environment:

Qlik Sense Enterprise on Windows November 2017 and later versions 

Resolution: 

Setting the  idle_in_transaction_session_timeout  to 5 minutes would clear the threads the repository database and progresses the new threads in the queue

1. Connect to the QSR database using PGADMIN4
   • Reference: Installing and Configuring PGAdmin 4 to access the PostgreSQL database used by Qlik Sense or NPrinting
   • Qlik Support does not support the direct query and access to the underlying product's database hosted by PostgreSQL. If any issues arise for which direct queries are deemed responsible, Qlik Support will reserve the right to request that a previous working backup is restored to the system in order to bring back full operation or resolve any isolated issue(s). The steps described here in this article are only used by Qlik Support for troubleshooting purposes.
2. Right-click the QSR database and bring up the Query Tool. 
3. Run the following command to look at the default value of the idle_in_transaction_session_timeout. 
   

   SHOW idle_in_transaction_session_timeout;​

4. By default, this is set to 0. 
5. Update the value by running the following command: 
   

   alter system set idle_in_transaction_session_timeout to '5min';​

6. Confirm the value has been updated running the following: 
   

   SHOW idle_in_transaction_session_timeout;  
   # reload the configuration     
   select pg_reload_conf();​

   
   Example:
   
   
   
7. The same setting could be done by editing the following file:
   
   C:\ProgramData\Qlik\Sense\Repository\PostgreSQL\9.6\postgresql.conf
   
   By searching for:
   

   #idle_in_transaction_session_timeout = 0 # in milliseconds, 0 is disabled​

   And changing it to:
   

   idle_in_transaction_session_timeout = 300000        # in milliseconds, 0 is disabled​

   Save, and then schedule a restart. 

Cause:

Changing the value, idle_in_transaction_session_timeout  terminates any session with an open transaction that has been idle for longer than the specified duration in milliseconds. This allows any locks held by that session to be released and the connection slot to be reused decreasing over CPU utilization. 

 

The syntax used for creating expressions on column names that contain the symbol '$' differs between Qlik Replicate versions. This article documents the two different methods.

The syntax changed with the May 2023 release. Previously, the syntax allowed the full column name to be recognized with a '$' symbol in front of the column name. With the current syntax, Qlik Replicate interprets these expressions as containing two separate columns and will error out. 

Content:

• Qlik Replicate May 2023 and later
• Qlik Replicate versionspriorto May 2023

Qlik Replicate May 2023 and later

Starting with the May 2023 release, extra '$' symbols are treated as an additional column name. To correctly format the expression to read it as a single column, the setting Support special characters in column names used in expressions must be enabled and the syntax of double quoting the column name should be used.

Enable the transformation setting:

1. Open your Endpoint's Task settings
2. Go to Task settings
3. Go to Transformations and Filters 
4. Open Expression Defaults
5. Check Support special characters in column names used in expressions
   
   
   

Syntax example:

Column name: IXF$RP
Expression: "IXF$RP"

If support for special characters is not toggled on, the Parse expression will not parse.

If the extra '$' is used, the expression will treat the column name as two separate columns. Running the expression as is will lead to the task failing with "Failed to init column calculation expression".

Qlik Replicate versions prior to May 2023

Previous syntax example:

Column name: IXF$RP
Expression: $IXF$RP

Related Content

Transformations and Filters 

The warning and example provided in the user guide refer to string literals used in an expression and not the column name. Single quotes should be used for the string literals and not the column name.

Environment

Qlik Replicate

This article is intended to get started with the Microsoft Outlook 365 connector in Qlik Application Automation.

Authentication and Authorization

To authenticate with Microsoft Outlook 365 you create a new connection. The connector makes use of OAuth2 for authentication and authorization purposes. You will be prompted with a popup screen to consent a list of permissions for Qlik Application Automation to use. The Oauth scopes that are requested are:

• Mail.Send
• User.Read
• Offline_access

Sending Email with the Microsoft Outlook 365 connector

The scope of this connector has been limited to only sending emails. Currently, we do not enable sending email attachments and are looking to provide this functionality in the future. The suggested approach is to upload files to a different platform, e.g. Onedrive or Dropbox and create a sharing link that can be included in the email body.

The following parameters are available on the Send Email block:

• To: A comma separated list of recipients;
• CC: A comma separated list of recipients to which the email should be copied;
• BCC: A comma separated list of recipients to which the email should be blind copied;
• Subject: A title for your email;
• Type: Choose between plain text email or HTML;
• Body: The content of your email. You can use HTML for your email if chosen as the type.

Generating a report and sending an email with the report

As we do not currently support email attachments, we need to first generate a sharing link in Onedrive or an alternative file sharing service. The following automation shows how to generate a report from a Qlik Sense app, upload the report to Microsoft Onedrive, create a sharing link and send out an email with the sharing link in the body. This automation is also attached as JSON in the attachment to this post.

Environment:

Qlik Application Automation 

During a Replicate Full Load, incoming changes occur while the Full load is in progress. What happens to those changes?

Qlik replicate starts the CDC thread before starting the Full load and all the changes show as cached events while the Full load is running. For example, 2 billion records are applied in the Full load state and Replicate again check at the end of the Full load for committed changes.  If 100k changes are also committed before finished then it will also be applied in the full load otherwise the 100k changes will go to CDC.

Another example, for a full load takes 12 hours, then updates during the 12 hours full load window are also applied. Note- these two examples assume you have CDC enabled in the task.

If CDC is turned off during a full load task, then Replicate will not capture the cached events.

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.

This article explains how to change the default locale in QlikSense Server when you create a new app on the hub.

Environment:

Qlik Sense Enterprise on Windows 

When you create a new application on the hub, the default locale is based on the settings on the QlikSense Server.

In order to change this setting, it is required to change the locale settings on the server for the account that is running the services.

If you run services as local system, you will need to use a tool such as Psexec in order to be able to execute the control panel as local system and change this settings. 

1. Open the IIS Manager console and navigate to (a) Default Website, (b) QvAjaxZfc, (c) Application Settings
   
   
   
   
2. Check for the entry: StrictRedirection with a value as false
   
   
   
   
3. If StrictRedirection entry isn't present, click (a) Add under the Actions menu, and then enter (b) StrictRedirection under Name: and False under Value:
   
   
   
   
4. Click the OK button, then test by restarting the browser running the AccessPoint and log in again.

Environment

QlikView Server May 2022 (12.70)
IIS
SAML authentication

A Qlik Compose task may fail during a full load but will run OK after a retry or when started at a different time. 

The task error log shows:

main 2023-10-30 08:39:51.396 [engine ] [ERROR ] [] Failed to create temporary file for /com/sun/jna/win32-x86-64/jnidispatch.dll library: C:\Windows\TEMP\jna--187113817\jna4331371764969383844.dll (The process cannot access the file because it is being used by another process)

Cause

The error indicates that a virus scan or malware scan was active while the Qlik Compose task was run. This locked the files needed. 

Resolution

Option One: Exclude Qlik Compose from virus scans

Exclude the Qlik Compose installation folder from the virus scan. The default path is: C:\Program Files\Qlik\Compose

Option Two: Schedule scans while no tasks run

Do not run any Qlik Compose task to run during scheduled virus scans.

Using a Sybase ASE source endpoint, Qlik Replicate may not capture or simply ignore all DDL (Data Definition Language) commands, while DML (Data Manipulation Language) commands (Insert/Update/Delete) are captured and applied successfully. 

Example of DDL in a source database:

alter table db_test.dbo.tb_test add notes varchar(50) null

Qlik Replicate ignores the above DDL. Reviewing the task logs (if set to trace or verbose logging level) shows:

[SOURCE_CAPTURE ]T: Captured DDL / type for table dbo.tb_test (identified by objid 672002394) is not known to metadata manager. Irrelevant DDL - ignored... (sybase_endpoint_metadata.c:710)

Resolution

Verify the user's privileges as per Required permissions for SAP Sybase AES.

Example of how to create a user (qruser) which owns sufficient permissions and works fine with DML/DDL operations:

create login qruser with password qruser123
sp_adduser qruser
grant role sa_role, replication_role, sybase_ts_role to qruser
sp_displaylogin qruser;

Internal Investigation ID(s) 

#00112248 

Environment

• Qlik Replicate all versions , on both platforms: Windows and Linux
• Sybase ASE all versions , on both platforms: Windows and Linux

The Qlik NPrinting Engine cannot resolve requests for tasks which include Qlikview Entity reports that output to PDF or is not printing QlikView Entity reports to PDF.

An error is displayed when attempting to edit or print the QlikView Entity report:

Error: QlikView NPrinting PDF Printer not installed or not properly registered

Or the report fails silently while the following is printed in the Qlik NPrinting logs:

resolution aborted with exception System.Runtime.InteropServices.COMException (0x80004005): Error HRESULT E_FAIL has been returned from a call to a COM component.↵↓   at Tracker.PDFXChange.IPXCControlEx.get_Printer(String pServerName, String pPrinterName, String pRegKey, String pDevCode)↵↓   at Qlik.Reporting.Printers.QlikPdfPrinter.Win64PrinterFactory.get_Item(String pServerName, String pPrinterName, String pRegKey, String pDevCode

When you install Qlik NPrinting, the Windows service “Print spooler” must be up and running. If it is disabled, the Qlik NPrinting Printer will not be added during the installation. Similarly, if a separate PDF-XChange driver is installed, the Qlik NPrinting engine install will not install the QlikView NPrinting PDF-Printer. 

Resolution

1. Verify if a separate PDF-XChange printer is listed in the Devices and Printers applet under  \Control Panel\Hardware and Sound\Devices and Printers 
2. If a separate PDF-XChange printer exists, manually uninstall it
3. Ensure the Spooler (Print Spooler) service is running via Windows Task Manager
4. Then re-Install Qlik NPrinting Engine
5. Confirm that the Qlik NPrinting printer driver appears in the Devices and Printers applet under  \Control Panel\Hardware and Sound\Devices and Printers

    

   

Related Content

NPrinting: PDF reports generation fails after disabling the Windows Spooler service

Environment

Qlik NPrinting 

Internal Investigation ID(s):

QB-14941