Scenario :

• Single node
• All services installed, up and running

Possible Error messages :

• Unhandled exception: Failed to access performance counter data. Make sure that performance counters are enabled or try rebuilding them with lodctr /R.    
• Category does not exist.
• Could not reserve an executor for task: No node found for task(name/id)
• Failed to send alive notification to manager.    
• Could not obtain the current CPU load.
• Worker-node XXXX is not alive and cannot run task YYYYYY

Environment:

• Qlik Sense Enterprise on Windows , all versions
• Windows 2012 R2 datacenter edition

Resolution:

Steps for resolution :

1. Rebuild the performance counters (lodctr /R)

Tasks do not reload after upgrade to Qlik Sense June 2018
 

2. Disable the loopback check

Reopen Registry and navigate to HKEY_LM\System\CCS\Control\LSA\MSV1.0 and create the following DWORD key 
DisableLoopbackCheck
Value : 1

3. Then, reboot the box

Related Content:

• DisableLoopbackCheck. Lets do it the right way
  
  
  
   

Executive Summary 

A security issue in Qlik Sense Enterprise for Windows has been identified, and patches have been made available. If successfully exploited, this vulnerability could lead to a compromise of the server running the Qlik Sense software, including remote code execution (RCE). 

This issue was responsibly disclosed to Qlik and no reports of it being maliciously exploited have been received. 

Affected Software 

All versions of Qlik Sense Enterprise for Windows prior to and including these releases are impacted: 

• February 2024 Patch 3 
• November 2023 Patch 8 
• August 2023 Patch 13 
• May 2023 Patch 15 
• February 2023 Patch 13 
• November 2022 Patch 13 
• August 2022 Patch 16 
• May 2022 Patch 17

Severity Rating 

Using the CVSS V3.1 scoring system (https://nvd.nist.gov/vuln-metrics/cvss), Qlik rates this severity as high.   

Vulnerability Details

CVE-2024-xxxx (QB-26216) Privilege escalation for authenticated/anonymous user 

Severity: CVSS:3.1/AV:N/AC:L/PR:L/UI:N/S:U/C:H/I:H/A:H 8.8 (High) 

Due to improper input validation, a remote attacker with existing privileges is able to elevate them to the internal system role, which in turns allows them to execute commands on the server.  

Resolution 

Recommendation 

Customers should upgrade Qlik Sense Enterprise for Windows to a version containing fixes for these issues. Fixes are available for the following versions: 

• May 2024 Initial Release 
• February 2024 Patch 4 
• November 2023 Patch 9 
• August 2023 Patch 14 
• May 2023 Patch 16 
• February 2023 Patch 14 
• November 2022 Patch 14 
• August 2022 Patch 17 
• May 2022 Patch 18 

All Qlik software can be downloaded from our official Qlik Download page (customer login required).

Credit

This issue was identified and responsibly reported to Qlik by Daniel Zajork. 

The max concurrent reloads can be configured in the Qlik Sense Management Console.

1. Open the Qlik Sense Management Console
2. Navigate to Schedulers 
3. Enable the Advanced section (if not already on by default)
4. Set the required max concurrent reloads
   
   
   

    

IMPORTANT TO REMEMBER

• A reload node could run NumberOfCores - 2
• If for example, the reload node has 8 CPU Cores then it could possibly run 6 concurrent reloads at its maximum.
• A reload task typically consumes at least one core, sometimes more
• With more concurrent tasks running than there are number of cores, the total time to complete a number of tasks that is larger than the number of cores does not improve with more parallel tasks running due to starvation of the server
• Each reload running is loaded into RAM. This means that the average RAM footprint grows with more parallel reloads
• With more parallel reloads than cores the probability for total starvation increases since the QIX engine is CPU hungry and other processes (for example, the operating system) might not get enough CPU cycles to perform their duties
• Use the guideline, NumberOfCPUCores - 2 in performing capacity planning on the possible amount of concurrent scheduled reloads.
• Scheduled reloads are queued, however, if not enough resources in the system
• To validate that environment needs proper capacity planning, go to C:\ProgramData\Qlik\Sense\Log\Scheduler\Trace\<Server>_System_Scheduler.txt, an example log shows below:
  
  

  <ServerName>_System_Scheduler.txt
  Domain\qvservice    Engine connection released. 5 of 4 used 
  Domain\qvservice    Engine connection 6 of 4 established 
  Domain\qvservice    Request for engine-connection dequeued. Total in queue: 25

Concurrent settings

Use the "Max concurrent reloads" to limit the maximal concurrent tasks can be run at same time on current node. By default, it's set to 4, which means only 4 tasks can be run at same time on this node.
When the 5th task comes in:

1. It will be queued by sequence. 
2. The queue has a time setting, which will eliminate (erase) a queued task if the timeout limit reaches. By default, it's set to 30 mins, in this case, if none of the first 4 tasks finishes in 30 minutes after the 5th task comes in, then the 5th task will be cancelled due to timeout. It has to be triggered again (manually or next scheduled time slot).
3. Once one of the 4 running tasks finishes, the 5th task will be executed on this node if it's not timed out.
4. If you set timeout to 0, this will make the queue never timeout.
5. It's suggested not to run more than 10 concurrent reloads at once, unless the schedular node has an extreme amount of resources dedicated to it.
6. If there is low memory or cores available on the node, then some tasks may kick off however take several hours to complete. Adding resources to the node running the reloads is suggested.

Multi-node deployment

On a multi-node deployment, tasks will be balanced from the manager node to any node(s) designated as workers.

It's highly advised to check if the central node configured is set to Manager and Worker or Manager. When set to Manager, it will send all reload jobs to the reload/scheduler nodes, as it should. However if a central node is set to Manager and Worker, this means the Central node will also be involved in performing reloads. This is not recommended. 

The work flow looks as follows:

1. Manager node receives a new task execution request.
2. Manager node checks the resource availability on each of the worker nodes. 
3. Manager node assigns this task to the node with the lowest number of running tasks per "Max concurrent reloads" setting.

The improvement to track the Max concurrent reloads can, if desired, be disabled. This reverts Sense to an older load balancing method that relies only on CPU usage. 

To disable the setting:

1. Open the Scheduler.exe.config, which by default is located in: C:\Program Files\Qlik\Sense\Scheduler\Scheduler.exe.config
2. Set DisableLegacyLoadBalancingBehavior setting to false
3. Restart Qlik Sense Scheduler Service
4. Repeat these actions on each node of the cluster running the Qlik Sense Scheduler Service

Example

In our example, we allow one concurrent reload, but we assume that two reloads are executed at the same time.

• If Task A is executed first, Task B is queued.
• If Task A is executed first and then fails, Task B is executed after the failure.
• After Task A's failure, Task A is queued and will execute after Task B has finished.

Reload fails in QMC even though script part is successfull in Qlik Sense Enterprise on Windows November 2023 and above.

When you are using a NetApp based storage you might see an error when trying to publish and replace or reloading a published app.

In the QMC you will see that the script load itself finished successfully, but the task failed after that.

ERROR QlikServer1 System.Engine.Engine 228 43384f67-ce24-47b1-8d12-810fca589657
 Domain\serviceuser QF: CopyRename exception:
 Rename from \\fileserver\share\Apps\e8d5b2d8-cf7d-4406-903e-a249528b160c.new 
to \\fileserver\share\Apps\ae763791-8131-4118-b8df-35650f29e6f6 
failed: RenameFile failed in CopyRename

ExtendedException: Type '9010' thrown in file
 'C:\Jws\engine-common-ws\src\ServerPlugin\Plugins\PluginApiSupport\PluginHelpers.cpp'
 in function 'ServerPlugin::PluginHelpers::ConvertAndThrow'
 on line '149'. Message: 'Unknown error' and additional debug info:
 'Could not replace collection 
\\fileserver\share\Apps\8fa5536b-f45f-4262-842a-884936cf119c] with
[\\fileserver\share\Apps\Transactions\Qlikserver1\829A26D1-49D2-413B-AFB1-739261AA1A5E],
 (genericException)'

<<< {"jsonrpc":"2.0","id":1578431,"error":{"code":9010,"parameter":
"Object move failed.","message":"Unknown error"}}

ERROR  Qlikserver1  06c3ab76-226a-4e25-990f-6655a965c8f3
  20240218T040613.891-0500  12.1581.19.0  
Command=Doc::DoSave;Result=9010;ResultText=Error: Unknown error
  0  0  298317  INTERNAL&
emsp; sa_scheduler  b3712cae-ff20-4443-b15b-c3e4d33ec7b4
  9c1f1450-3341-4deb-bc9b-92bf9b6861cf  Taskname  Engine  Not available
  Doc::DoSave  Doc::DoSave  9010  Object move failed.
  06c3ab76-226a-4e25-990f-6655a965c8f3

Resolution

Potential workarounds

• Roll back your upgrade to a version earlier than November 2023
• Change the storage to a file share on a Windows server

Cause 

The most plausible cause currently is that the specific engine version has issues releasing File Lock operations. We are actively investigating the root cause, but there is no fix available yet.

An update will be provided as soon as there is more information to share.

Internal Investigation ID(s) 

QB-25096
QB-26125

Environment

• Qlik Sense Enterprise on Windows November 2023 and above

When installing Qlik Replicate v2023.5 on RHEL/CentOS v8.x, you may encounter a warning message from rpm as follows:

warning: areplicate-2023.5.0-213.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 05d7eace: NOKEY

This warning message indicates that rpm is unable to verify the package signature due to the absence of the public key.

Resolution

Please perform the following commands before trying to install the RPM package:

1. $ rpm -q gpg-pubkey --qf '%{version}-%{release} %{summary}\n' | sed '/qlik.com/!d;s/ .*$//' | xargs -n 1 -I {} sudo rpm -e gpg-pubkey-{}

2. $ curl https://qlikcloud.com/.well-known/qlik-codesign-public-keys.asc > qlik-codesign-public-keys.asc

3. $ sudo rpm --import qlik-codesign-public-keys.asc

4. $ rpm --checksig <qlik-rpm-package>

Where the commands do the following: 

1. Removes previously installed Qlik code signing public keys (optional but recommended to allow expired keys to be removed)
2. Fetches the current Qlik code-signing public keys
3. Imports the current public keys into RPM
4. Verifies the authenticity of the desired Qlik RPM package

Here is an example of an RPM package that passed the authenticity check:

$ rpm --checksig areplicate-2023.5.0-152.x86_64.rpm
areplicate-2023.5.0-152.x86_64.rpm: digests SIGNATURES OK

An example of failed check is:

$ rpm --checksig areplicate-2023.5.0-152.x86_64.rpm
areplicate-2023.5.0-152.x86_64.rpm: digests SIGNATURES NOT OK

Then proceed with the installation or upgrade as per the instructions in the user guide.

Environment

Qlik Replicate  v2023.5, v2023.11 or after on Linux  8.x (64-bit)

Issue

NPrinting report is hanging on preview or in a publish task preventing the report from generating as expected. In many cases with 'Carded' Qlik Sense themes, we are seeing this problem.

This is also seen when the extended sheet feature is used

Related NP log error

"error=task SENSE_JS_PAINT_COVER_POINTS_TIMEOUT"

Resolution

There is currently a fix coming to resolve 'carded' custom themes however this fix is still under investigation.

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 QB-24997 for reference.

Workaround:

Possible workarounds for this are:

1. Disable "Apply Sense app theme" in NPrinting connection definition.
2. Reduce the size of any Qlik Sense extended sheets so that they are within default sheet size dimensions (In this point, you may test with and without the "Apply Sense app theme" inside the NPrinting connection settings).

NOTE: Extended or Custom sheet sizes are not fully supported. See Qlik Help page below for details

https://help.qlik.com/en-US/nprinting/February2024/Content/NPrinting/ReportsDevelopment/Qlik-objects-supported-NP.htm

Qlik Sense custom and extended sheets

• Qlik Sense custom size sheets and extended sheet features will not be maintained on export.

Cause

• Product Defect ID: QB-24997
• Carded Themes
• Extended Sheets

Environment

• Qlik NPrinting Supported Versions
• Qlik Sense versions affected to be determined and resolved as per QB-24997

A Job fails to run on a remote JobServer with the following error message:

Unrecognized option: --add-opens=java.base/java.lang=ALL-UNNAMED
Error: Could not create the Java Virtual Machine.
Error: A fatal exception has occurred. Program will exit.

Cause

The issue occurs with Talend Remote Engine or Talend JobServer using Java 8, but Talend Studio has JDK 17 enabled. The --add-opens option is only supported in java 11 and above, Java 8 does not support it.

If the 'Enable Java 17 compatibility' option is enabled, the Jobs built by Talend Studio will have the --add-opens parameters in the job.sh or job.bat script files, such as:

JAVA_OPTS=-Dfile.encoding=UTF-8 -Dsun.stdout.encoding=UTF-8 -Dsun.stderr.encoding=UTF-8 -Dconsole.encoding=UTF-8 --add-opens=java.base/java.lang=ALL-UNNAMED

The Jobs built this way cannot be executed with Java 8.

Resolution

Option 1:

1. Click  on the toolbar of Talend Studio main window, or click File > Edit Project Properties from the menu bar to open the Project Settings dialog box.
2. Expand the Build node and click Java Version to open the corresponding view.
3. Deactivate the 'Enable Java 17 compatibility' option.
4. Re-build the Job and re-publish it to Talend Remote Engine or Talend JobServer.

Option 2:

From R2024-05, Java 17 will become the only supported version to start most Talend modules, If you want to keep the "Enable JAVA 17 Compatibility" option enabled, make sure that Talend Remote Engine or Talend JobServer also uses JDK 17 to execute jobs.

Related Content

Setting-compiler-compliance-level

Java 17 Migration Guide

Environment

• Talend Studio 
• Talend Data Integration 

A Qlik app may show an Incomplete Visualization or Invalid Visualization error. This article covers the most common root causes. 

Resolution

• Check for and fix incomplete chart dimensions or measures and also check for NULL values.
• Update your visualization bundles:
  • These bundles must be the same version as the installed Qlik Sense version. A bundle can be copied from a different Qlik Sense server so long as it is the same Qlik version and patch of the Target Qlik Server which needs its bundle updated
  • If supported 3rd party bundles, check with your vendor and upload a supported and current bundle version
• Use supported Native Qlik Bundles

Cause

Incomplete Visualizations:

• The chart may have invalid dimensions or measures due to recent changes by a developer

Invalid Visualizations:

• Wrong bundle version uploaded to the current Qlik Sense server, perhaps from an older Qlik Sense server
• Unsupported or outdated 3rd party bundles
• Incorrectly developed custom visualization bundle

Related Content

• Visualization bundles
• Dashboard bundles
• Installing, importing and exporting your visualizations
• Creating and editing visualizations
• Troubleshooting - Creating visualizations
• Null values in visualizations

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

This article describes how to resolve the OAUTH Error Status 400 error preventing successful configuration and download capability of the MS Office addin manifest necessary for Tabular Reporting.

{"errors":[{"title":"Invalid redirect_uri","detail":"redirect_uri is not registered","code":"OAUTH-1","status":"400"}],"traceId":"0000000000000000xxxxxxxxxxxxxxxx"}

Resolution

An OAuth client configuration is required to install the Qlik add-in for Microsoft Excel. The add-in is used by report developers to prepare report templates which control output of tabular reports  from the Qlik Sense app.

Review or re-do the steps documented in Creating an OAuth client for the Qlik add-in for Microsoft Excel. 

Verify you have included the user_default scope.

Example of a working OAUTH configuration:

Cause 

The OAUTH configuration was not set correctly, such as missing the user_default scope, or not setting up the redirect link.

Related Content

Preparing and obtaining the add-in manifest

Qlik Tabular Reporting General Troubleshooting and Best Practices

Tabular Reporting available in Qlik Cloud Enterprise and Premium editions. See for Product Description for Qlik Cloud® Subscriptions for details. *Not included Qlik Cloud Standard and  Government Editions.

Environment

Qlik Cloud 

When replicating a CDS View the Updates are being processed as Inserts not Updates. In SAP the CDS views delta with UPSERT and SAP capture INSERT and UPDATE both as one operation which is treated as INSERT.

If you have a SAP login you can look up SAP Note 3300238 for more information as shown below:

SAP Note 3300238 - ABAP CDS CDC: ODQ_CHANGEMODE not showing proper status forcreation
Component: BW-WHM-DBA-ODA (SAP Business Warehouse > Data Warehouse Management > Data Basis >Operational Data Provider for ABAP CDS, HANA & BW), Version: 4, Released On: 19.01.2024

Resolution

This is working as expected. It is the designed behavior of the CDC logic. For both insert and update, ODQ_CHANGEMODE = U and ODQ_ENTITYCNTR = 1.

The CDC-delta logic is designed as UPSERT-logic. This means a DB-INSERT (or create) or a DB-UPDATE both get the ODQ_CHANGEMODE = U and ODQ_ENTITYCNTR = 1. It's not possible to distinguish in CDC-delta between Create and Update.

Environment

Qlik Replicate 
SAP S/4HANA
SAP BW/4HANA

The problem occurs when running a reload from Data Gateway to a Cloudera DB (Cloudera Hive).

The reload is completed correctly, but the fields are imported with aliases. This means that the reload imports the fields as 'TableName.FieldName' instead of importing them as just 'FieldName'.

For example, with this script:

LOAD meter_id,
Field1,
Field2,
Field3;

[meter_attribute]:
SELECT "meter_id",
"Field1",
"Field2",
"Field3"
FROM internaldb."meter_attribute";

Fields are imported as: meter_attribute.Field1, meter_attribute.Field2, meter_attribute.Field3.

Resolution

The problem can be fixed adding the row EnableUniqueColumnName=0 to the connection string.

1. Edit your Cloudera ODBC connection
2. Locate Connection string
3. Add EnableUniqueColumnName=0, separating it with a semicolon (;) from the previous parameters: 
   
   
   

Additional Notes:

• It is not necessary to select the  "Allow non-SELECT queries" option in the connector.
• In order to make Data Preview work reliably with any new Cloudera connections, we recommend adding the following syntax to its syntax.json file:
  
  
  1. Open C:\ProgramData\Qlik\Gateway\genericodbc_database_syntax.json in a text editor with elevated permissions (administrator)
     
     
  2. Add:
     

       {
         "SyntaxId": "ClouderaHive",
         "DisplayName": "Cloudera Hive syntax",
         "DelimiterStart": "`",
         "DelimiterEnd": "`",
         "DataPreviewSelectTemplate": "SELECT ${COLUMN_LIST} FROM ${TABLE_NAME} ${FILTER} LIMIT ${LIMIT_VALUE}",
         "DatabaseTerm": "catalog",
         "OwnerTerm": "schema"
       }

  3. Restart the Data Gateway service.
     
     This snippet re-configures all the custom syntax settings for the driver.
     
     Please, notice that the block must have a comma (,) either immediately before or after it as shown in the screenshot below.
     
     
     
     
  4. Once restarted, open the data connection editor again and the "Cloudera Hive syntax" option should appear, as in this image:
     
     

Internal Investigation ID

QB-26342

Environment

• Qlik Cloud
• Qlik Data Gateway Direct Access all versions

This article explains how the Amazon SNS connector in Qlik Application Automations can be used to set up webhooks that trigger when a object creation event occurs in Amazon S3. This connector only has webhooks available.

Content:

• How to connect
• Using this in an automation

How to connect

Search for the "Amazon SNS" connector in Qlik Application Automations. When you click connect, you will be prompted for the following input parameters:

• Aws_access_key_id: This is the access key you obtain from AWS.
• Aws_secret_access_key: This is the secret key you obtain from AWS.
• Region: Your AWS region where your resources are situated.

You must obtain the AWS Access Key from IAM in your AWS console. This can be obtained by going to the IAM section in AWS and in the left side panel choose users.

Here you can choose either for an already existing user or creating a new one by clicking the "Add Users" button in the topright.

When you create a new user, you must provide a user name and click next. You do not need to give this user access to the AWS console. In the next step, you will give permissions to do this IAM user.

The following policy needs to be created and attached to the IAM user, replace the account-id with your account ID:

Other permissions that are suggested to add are:

• s3:ListBucket
• s3:ListAllMyBuckets
• sns:ListTopics

Furthermore, the IAM user must be made an owner of a S3 bucket when creating a notification configuration.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "s3:PutBucketNotification",
                "iam:PassRole",
                "sns:Publish",
                "sns:CreateTopic",
                "sns:Subscribe"
            ],
            "Resource": [
                "arn:aws:s3:::*",
                "arn:aws:iam::account-id:role/*",
                "arn:aws:sns:*:account-id:*"
            ]
        },
        {
            "Sid": "VisualEditor1",
            "Effect": "Allow",
            "Action": "sns:Unsubscribe",
            "Resource": "*"
        }
    ]
}

You will have to create an access key for the IAM user.

This can be done in the (a) Users menu and in the (b) Security credentials tab. Click (c) Create access key.

Choose Third-party service and choose to understand the above recommendation, click next:

You will now have your access key and secret key and can finish creating the datasource in Qlik Application Automation:

Using this in an automation

You can use this in an automation, but only as a webhook. When you create a new automation, you will be presented with a blank canvas. Select the Start block and change the run mode to webhook.

Choose an event type next. These are currently limited to S3 object creation events. You will have lookup capabilities available to other parameters, such as bucket and topic selection:

After saving the automation, you can test the webhook by uploading objects in your S3 bucket and see in your automation run history that it is triggering this automation.

The use of this is that you can now trigger tasks after a object is uploaded to S3. Common tasks will be to reload a Qlik Sense app or trigger a data pipeline in any of our other connectors:

If a tenant previously had an incomplete SMTP configuration with their Qlik Cloud, an error message will now be shown to the Tenant Administrator:

• Your email providers is not working – Go to Settings 

Resolution

To resolve this error a Tenant Admin can enter valid SMTP credentials. 

At the moment, it is not possible to delete/clear the previous credential entry in the authentication.  An option to clear the credentials is being prepared to support a return to a default (non-configured/empty) state and is expected to be available in the coming weeks.

We will update this article when the ability to clear becomes available. 

Example SMTP Error

Cause 

With the release of the SMTP service connectivity for Microsoft O365 from the Management Console, more stringent error checking was added to the basic authentication configuration. 

Related Content 

If interested, Admins can still successfully connect to Microsoft 0365 SMTP with this error showing. More details on the new available 0Auth2 authentication can be found here: Qlik Cloud: Introducing OAuth2 authentication for ... - Qlik Community - 2444243

Internal Investigation ID

• QB-26792

Environment

• Qlik Cloud

Many of the GeoAnalytics can be executed with a split input of the indata table. This article explains which and how to modify the code that the connector produces. Operations that cannot be Splittable are mostly the aggregating and hence not Splittable. When loadable tables are used for input, inline tables are created in loops and can be used for a quick way to split. Of course it's possible to write custom code to do the splitting instead.

Making calls with large indata tables often causes time outs on the server side, splitting is a way around that. 

Environment:

Qlik GeoAnalytics 

 

Binning and SpatialIndex

Bining and SpatialIndex differs from other operations, they are not placing any call to the server if the indata are internal geometries, ie lat ,ong points. The operations als produce the same type of results so the resulting tables can be concatenated.
 

Resolution:

Example, a Within operation

Before the edit

The code as the connector produces it:

/* Generated by Idevio GeoAnalytics for operation Within ---------------------- */
Let [EnclosedInlineTable] = 'POSTCODE' & Chr(9) & 'Postal.Latitude' & Chr(9) & 'Postal.Longitude';
Let numRows = NoOfRows('PostalData');
Let chunkSize = 1000;
Let chunks = numRows/chunkSize;
For n = 0 to chunks
   Let chunkText = '';
   Let chunk = n*chunkSize;
   For i = 0 To chunkSize-1
      Let row = '';
      Let rowNr = chunk+i;
      Exit for when rowNr >= numRows;
      For Each f In 'POSTCODE', 'Postal.Latitude', 'Postal.Longitude'
         row = row & Chr(9) & Replace(Replace(Replace(Replace(Replace(Replace(Peek('$(f)', $(rowNr), 'PostalData'), Chr(39), '\u0027'), Chr(34), '\u0022'), Chr(91), '\u005b'), Chr(47), '\u002f'), Chr(42), '\u002a'), Chr(59), '\u003b');
      Next
      chunkText = chunkText & Chr(10) & Mid('$(row)', 2);
   Next
   [EnclosedInlineTable] = [EnclosedInlineTable] & chunkText;
Next
chunkText=''


Let [EnclosingInlineTable] = 'ClubCode' & Chr(9) & 'Car5mins_TravelArea';
Let numRows = NoOfRows('TravelAreas5');
Let chunkSize = 1000;
Let chunks = numRows/chunkSize;
For n = 0 to chunks
   Let chunkText = '';
   Let chunk = n*chunkSize;
   For i = 0 To chunkSize-1
      Let row = '';
      Let rowNr = chunk+i;
      Exit for when rowNr >= numRows;
      For Each f In 'ClubCode', 'Car5mins_TravelArea'
         row = row & Chr(9) & Replace(Replace(Replace(Replace(Replace(Replace(Peek('$(f)', $(rowNr), 'TravelAreas5'), Chr(39), '\u0027'), Chr(34), '\u0022'), Chr(91), '\u005b'), Chr(47), '\u002f'), Chr(42), '\u002a'), Chr(59), '\u003b');
      Next
      chunkText = chunkText & Chr(10) & Mid('$(row)', 2);
   Next
[EnclosingInlineTable] = [EnclosingInlineTable] & chunkText;
Next
chunkText=''


[WithinAssociations]:
SQL SELECT [POSTCODE], [ClubCode] FROM Within(enclosed='Enclosed', enclosing='Enclosing')
DATASOURCE Enclosed INLINE tableName='PostalData', tableFields='POSTCODE,Postal.Latitude,Postal.Longitude', geometryType='POINTLATLON', loadDistinct='NO', suffix='', crs='Auto' {$(EnclosedInlineTable)}
DATASOURCE Enclosing INLINE tableName='TravelAreas5', tableFields='ClubCode,Car5mins_TravelArea', geometryType='POLYGON', loadDistinct='NO', suffix='', crs='Auto' {$(EnclosingInlineTable)}
SELECT [POSTCODE], [Enclosed_Geometry] FROM Enclosed
SELECT [ClubCode], [Car5mins_TravelArea] FROM Enclosing;
[EnclosedInlineTable] = '';
[EnclosingInlineTable] = '';

/* End Idevio GeoAnalytics operation Within ----------------------------------- */

After edit

The header and the call is moved inside of the loop. chunkSize decides how big each split is.

Note that the first inline table now comes after the first one, this to get the call inside of the iteration.

/* Generated by Idevio GeoAnalytics for operation Within ---------------------- */

Let [EnclosingInlineTable] = 'ClubCode' & Chr(9) & 'Car5mins_TravelArea';
Let numRows = NoOfRows('TravelAreas5');
Let chunkSize = 1000;
Let chunks = numRows/chunkSize;
For n = 0 to chunks
   Let chunkText = '';
   Let chunk = n*chunkSize;
   For i = 0 To chunkSize-1
      Let row = '';
      Let rowNr = chunk+i;
      Exit for when rowNr >= numRows;
      For Each f In 'ClubCode', 'Car5mins_TravelArea'
         row = row & Chr(9) & Replace(Replace(Replace(Replace(Replace(Replace(Peek('$(f)', $(rowNr), 'TravelAreas5'), Chr(39), '\u0027'), Chr(34), '\u0022'), Chr(91), '\u005b'), Chr(47), '\u002f'), Chr(42), '\u002a'), Chr(59), '\u003b');
      Next
      chunkText = chunkText & Chr(10) & Mid('$(row)', 2);
   Next
   [EnclosingInlineTable] = [EnclosingInlineTable] & chunkText;
Next
chunkText=''

Let numRows = NoOfRows('PostalData');

Let chunkSize = 1000;
Let chunks = numRows/chunkSize;
For n = 0 to chunks

   Let [EnclosedInlineTable] = 'POSTCODE' & Chr(9) & 'Postal.Latitude' & Chr(9) & 'Postal.Longitude';

   Let chunkText = '';
   Let chunk = n*chunkSize;
   For i = 0 To chunkSize-1
      Let row = '';
      Let rowNr = chunk+i;
      Exit for when rowNr >= numRows;
      For Each f In 'POSTCODE', 'Postal.Latitude', 'Postal.Longitude'
         row = row & Chr(9) & Replace(Replace(Replace(Replace(Replace(Replace(Peek('$(f)', $(rowNr), 'PostalData'), Chr(39), '\u0027'), Chr(34), '\u0022'), Chr(91), '\u005b'), Chr(47), '\u002f'), Chr(42), '\u002a'), Chr(59), '\u003b');
      Next
      chunkText = chunkText & Chr(10) & Mid('$(row)', 2);
   Next
   [EnclosedInlineTable] = [EnclosedInlineTable] & chunkText;

   [WithinAssociations]:
   SQL SELECT [POSTCODE], [ClubCode] FROM Within(enclosed='Enclosed', enclosing='Enclosing')
   DATASOURCE Enclosed INLINE tableName='PostalData', tableFields='POSTCODE,Postal.Latitude,Postal.Longitude', geometryType='POINTLATLON', loadDistinct='NO', suffix='', crs='Auto' {$(EnclosedInlineTable)}
   DATASOURCE Enclosing INLINE tableName='TravelAreas5', tableFields='ClubCode,Car5mins_TravelArea', geometryType='POLYGON', loadDistinct='NO', suffix='', crs='Auto' {$(EnclosingInlineTable)}
   SELECT [POSTCODE], [Enclosed_Geometry] FROM Enclosed
   SELECT [ClubCode], [Car5mins_TravelArea] FROM Enclosing;
   [EnclosedInlineTable] = '';
   [EnclosingInlineTable] = '';

Next
chunkText=''
/* End Idevio GeoAnalytics operation Within ----------------------------------- */

Example, a AddressPointLookup operation

Before the edit

The code as the connector produces it:

/* Generated by GeoAnalytics for operation AddressPointLookup ---------------------- */
Let [DatasetInlineTable] = 'id' & Chr(9) & 'STREET_NAME' & Chr(9) & 'STREET_NUMBER';
Let numRows = NoOfRows('data');
Let chunkSize = 1000;
Let chunks = numRows/chunkSize;
For n = 0 to chunks
   Let chunkText = '';
   Let chunk = n*chunkSize;
   For i = 0 To chunkSize-1
      Let row = '';
      Let rowNr = chunk+i;
      Exit for when rowNr >= numRows;
      For Each f In 'id', 'STREET_NAME', 'STREET_NUMBER'
         row = row & Chr(9) & Replace(Replace(Replace(Replace(Replace(Replace(Peek('$(f)', $(rowNr), 'data'), Chr(39), '\u0027'), Chr(34), '\u0022'), Chr(91), '\u005b'), Chr(47), '\u002f'), Chr(42), '\u002a'), Chr(59), '\u003b');
      Next
      chunkText = chunkText & Chr(10) & Mid('$(row)', 2);
   Next 
   [DatasetInlineTable] = [DatasetInlineTable] & chunkText; 
Next
chunkText=''
[AddressPointLookupResult]:
SQL SELECT [id], [Dataset_Address], [Dataset_Geometry], [CountryIso2], [Dataset_Adm1Code], [Dataset_City], [Dataset_PostalCode], [Dataset_Street], [Dataset_HouseNumber], [Dataset_Match] 
FROM AddressPointLookup(searchTextField='', country='"Canada"', stateField='', cityField='"Toronto"', postalCodeField='', streetField='STREET_NAME', houseNumberField='STREET_NUMBER', matchThreshold='0.5', service='default', dataset='Dataset')
DATASOURCE Dataset INLINE tableName='data', tableFields='id,STREET_NAME,STREET_NUMBER', geometryType='NONE', loadDistinct='NO', suffix='', crs='Auto' {$(DatasetInlineTable)}
;
[DatasetInlineTable] = '';
/* End GeoAnalytics operation AddressPointLookup ----------------------------------- */

After edit

The header and the call is moved inside of the loop. chunkSize decides how big each split is.

/* Generated by GeoAnalytics for operation AddressPointLookup ---------------------- */
Let numRows = NoOfRows('data');
Let chunkSize = 1000;
Let chunks = numRows/chunkSize;
For n = 0 to chunks

   Let [DatasetInlineTable] = 'id' & Chr(9) & 'STREET_NAME' & Chr(9) & 'STREET_NUMBER';

   Let chunkText = '';
   Let chunk = n*chunkSize;
   For i = 0 To chunkSize-1
      Let row = '';
      Let rowNr = chunk+i;
      Exit for when rowNr >= numRows;
      For Each f In 'id', 'STREET_NAME', 'STREET_NUMBER'
         row = row & Chr(9) & Replace(Replace(Replace(Replace(Replace(Replace(Peek('$(f)', $(rowNr), 'data'), Chr(39), '\u0027'), Chr(34), '\u0022'), Chr(91), '\u005b'), Chr(47), '\u002f'), Chr(42), '\u002a'), Chr(59), '\u003b');
      Next
     chunkText = chunkText & Chr(10) & Mid('$(row)', 2);

   Next 
   [DatasetInlineTable] = [DatasetInlineTable] & chunkText;

   [AddressPointLookupResult]:
   SQL SELECT [id], [Dataset_Address], [Dataset_Geometry], [CountryIso2], [Dataset_Adm1Code], [Dataset_City], [Dataset_PostalCode], [Dataset_Street], [Dataset_HouseNumber], [Dataset_Match] 
   FROM AddressPointLookup(searchTextField='', country='"Canada"', stateField='', cityField='"Toronto"', postalCodeField='', streetField='STREET_NAME', houseNumberField='STREET_NUMBER', matchThreshold='0.5', service='default', dataset='Dataset')
   DATASOURCE Dataset INLINE tableName='data', tableFields='id,STREET_NAME,STREET_NUMBER', geometryType='NONE', loadDistinct='NO', suffix='', crs='Auto' {$(DatasetInlineTable)}
   ;
   [DatasetInlineTable] = '';

Next
chunkText=''
/* End GeoAnalytics operation AddressPointLookup ----------------------------------- */

Question

• It has become necessary to use an MS Excel Tabular Report in a different tenant.

Solution

1. Make a copy of the original MS Excel Tabular Report template file
2. Open the MS Excel Tabular Report template file
3. Click on the MS Excel Office Addin in the Excel Ribbon (Click on the 'alternate', 'new' tenant MS Office Excel Addin)
4. Log into the tenant (ensure all popup blockers are disabled)

5. In the add-in window, click the 'Home'  tab in the toolbar

6. Next to Source app,  click ... 

7. From the menu, select Change template connection

8. In the Set the new Qlik Sense app ID field, enter the new app ID

   (to find the new app ID, open the app in the 'alternate' or 'new' tenant hub and copy the app ID from the address bar

9. To change the tenant, enable the Change the Qlik Cloud tenant switch (disabled by default) and insert the new tenant address.

10. To save, click the Change Template Connection button
11. To use the changes, simply Click on the 'alternate', 'new' tenant MS Office Excel Addin again. This will re-log you in thus enabling the changes
12. Preview the report (select xlsx or pdf output format)

Environment

• Qlik Cloud 

Related Content

• Qlik Tabular Reporting General Troubleshooting and... - Qlik Community - 2160224
• Changing the connected Qlik Sense app

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

The Qlik Sense Hub connection times out faster / sooner than configured in the Virtual Proxy.

When working with an app in the hub, the connection to the app times out faster than configured in virtual proxy (for example, after 5 minutes), and the error message below is displayed:

Connection lost. Make sure that Qlik Sense is running properly. If your session has timed out due to inactivity, refresh to continue working.

This issue occurs when accessing Qlik Sense through a reverse proxy or load balance, but does not occur when accessing Qlik Sense on the same server (https://localhost/hub).
The user is also being active on the sheet, i.e. they are making selections etc. to interact with sheet objects.

Here is an example log line that may be seen in the AuditActivity_Proxy logs:

76 12.20.4.0 20200115T101930.708-0500 QLIKSERVER 8cd6aa7c-fab5-4e82-a2cd-0681943e7d58 Command=Close connection;Result=0;ResultText=Success d62cfb0a-3355-44a5-9e34-5eaada84be67 1ba723cc-e754-4e24-a42b-0732b24ad924 0 DOMAINNAME user.name 5c5e98cf-3f1f-45ed-96f0-8e76d7c44473 Not available Proxy AppAccess /app/5c5e98cf-3f1f-45ed-96f0-8e76d7c44473?reloaduri=https%3a%2f%2domain.com%2fsense%2fapp%2f5c5e98cf-3f1f-45ed-96f0-8e76d7c44473%2foverview Close connection 0 Backend web socket connection Closed for session 'd62cfb0a-3355-44a5-9e34-5eaada84be67'.

Environment:

• Qlik Sense Enterprise on Windows 
• Load balancer or Reverse Proxy in front of Qlik Sense

Resolution:

Qlik Sense should not terminate a session earlier than configured in the Virtual Proxy (default is 30 minutes), and when it does the error message would be different: 

Your session has timed out. Log back in to Qlik Sense to continue.

In this case, if the connection is being lost and it is occurring sooner than configured in the virtual proxy then this indicates that either:

1. The client is terminating the WebSocket connection prematurely, or
2. A network appliance acting on behalf of the client (load-balancer or reverse proxy) is terminating the WebSocket connection prematurely

Is WebSocket traffic possible?

Qlik Sense requires WebSockets. To verify WebSocket traffic, see Qlik Sense Websocket Connectivity Tester.

Enable TCP WebSocket Keep-Alive and HTTP keep-alive timeout.

Check to see if this issue occurs when using Internet Explorer. Internet Explorer by default adds additional unnecessary traffic to the WebSocket which can help in these situations. If you do not receive early timeouts when using IE, you can try Enabling TCP Keep Alive Functionality In Qlik Sense.

The HTTP keep-alive timeout may be increased here which helps in environments exhibiting the behavior mentioned above. See the Keep-alive timeout under the Proxy configuration. See Editing Proxies - Advanced > Keep-alive timeout (seconds)

If the above does not work, then you will need to make changes to your network infrastructure. Sniffing the network traffic to confirm things further can be performed. As sniffer, Wireshark can be installed on both Qlik Sense Proxy server and Hub client PC to run captures on both ends simultaneously. This may assist confirming the following:

1. Whether Qlik Sense is in fact receiving the TCP FIN packet, closing the TCP connection as expected.
2. Whether it was the client's PC or another network device who is sending the TCP FIN packet to terminate the session.
3. Whether the connection is becoming inactive and from which side (Sense / Client).

Qlik Support may assist in a best-effort basis with the above.  Please check out the video below for additional guidance. If further assistance is required correcting the environmental behavior, unfortunately the the task falls outside the scope of Qlik Support as this often is specific to the network involved. If further assistance is needed please reach out to your Account Owner to arrange Consulting Services (or see How to Contact the Consulting Team?)
 

Click here for video transcript

Cause:

Network appliances (e.g. Load balancers, reverse proxies, VPN solutions, firewalls, web accelerators, etc) may not see the websocket connection as an "alive" TCP connection, depending on the vendor, and therefore terminate the connection on behalf of the end-user.

The "Backend web socket connection Closed for session" string seen in the logs is registered when a WebSocket TCP layer connection is closed by the transmission of a TCP Packet containing the FIN flag set. This happens when either the browser window or tab is closed or when a network device in the environment drops the TCP connection sessions in a controlled fashion (sends the TCP FIN packet to the Sense server). This can also be sent by the client's computer if there is(are) any setting(s) preventing inactive TCP connections from staying open, as required by the Websocket protocol.

Windows Firewall blocking port 4747. Disable Firewall or apply all ports inbound and outbound

When working with Data Gateway - Direct Access, odbc drivers can have unexpected behaviors that can lead to crashes. 

Note: For more information about how to enable dump file creation and Isolation capability for Data Gateway - Direct Access, please review the links included in the "Related Content" at the end of this article. 

One known reason to cause crashes is the use of malformed SQL query statement.  

Resolution

After configuring the dump file generation and Process Isolation, dump files will be created under the folder configured during the setup. 

Once a crash occurs the file will include the process id associated to the reload:

dotnet.exe.XXXXX.dmp

Where XXXX will be the process id. 

With that process id and looking for the date and time when the file was created, in the odbc connector log, is possible to find the ProcessId associated to the Id of the dump file and get the reload ID.

Once the reload id was identified, from the QMC get the reload script associated to the reload id to verify the query that was triggered.

If the query is executed from a third party tool (outside of Qlik products) and doesn't work, the problem is a malformed SELECT statement. 

If the issue is not related to a malformed SELECT query, collect the dump file and contact Qlik Support for further analysis.

Cause 

Malformed SQL query statement.

Related Content 

Mitigating connector crashes during reload

How to collect dump files for the Qlik Data Gateway - Direct Access ODBC connector crashes.

Environment

• Data Gateway - Direct Access 

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

Opening an application to visualize charts through the hub, the visualization may fail and the error "Out of calculation memory" will be printed.

Environment:

• Qlik Sense Enterprise on Windows, all versions

Resolution

The issue is often related to having an app that is too large or requires a re-design or general optimization. A poorly designed application can lead to memory leaks.

Application optimization

Review the application and optimize as needed to prevent performance problems for the engine service.  

Verify resource availability

Verify that the servers hosting the Qlik Sense engine service have sufficient resources (CPU / Memory) to proceed with the calculation. It is highly recommended to have at least 16 cores and 64 GB of memory.

Modify the Hypercube memory limit 

You can otherwise change the timeout.

• Open the Qlik Sense Management Console. Default: https://<QPS server name>/qmc.
• Open Engines for each node
• Select the Engine to modify and click Modify 
• Open the Advanced menu in menu to the right
• Modify the Hypercube memory limits (bytes). This is by default set to 0, which allows the engine to apply a global heuristic ensuring that we do not run more than 1 big calculation in parallel. Setting this value to -1 will disable the limit. 
• Restart the Qlik Sense Engine and Qlik Sense Service Dispatcher on all nodes after the setting was changed. 

Cause:

The "Hypercube memory limits" limits how much memory a hypercube evaluation can allocate during a request. If multiple hypercubes are calculated during the request, the limit is applied to each hypercube calculation separately.

If it sets to 0, the engine applies a global heuristic which basically ensures that it doesn't run more than 1 "big" calculation in parallel. 
If you set this value to -1 then you will disable the limit and allow the Engine to keep trying to load the application.

The negative value disables the limit, but it wouldn't exceed the limit set under the Max memory usage (%) or Memory usage mode.

Related Content:

Qlik Sense: "Calculation timed out" while loading a chart into an application