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.

Field Transformation

A very common use case for a target table is to have an Insert Date field that populates when the record is initially written. The problem is when the record is updated, the original value in the target is overwritten with null. On my Target table from SQL, I am using the target lookup function under Data Enrichment in order to retrieve the Insert_Date field value during an update operation.  When the task table has an Add Column field called Insert-Date and you want to preserve the original date value from when the record was inserted into the target you need to do a target lookup to fetch the original value. In the case of Insert_Date we will use a target lookup function to query the target table and retrieve the field value, putting it into the Add Column that we defined and called Insert_Date. This target lookup function is documented in the replicate user guide and the on-line help.

The general form of the function is as follows:

target_lookup(TTL,'SCHM','TBL','EXP','COND',COND_PARAMS)

For this example in am doing the lookup into the MLTest table:

target_lookup('NO_CACHING','dbo','MLTEST','Insert_Date','EMP_ID=?',$EMP_ID)

NO_CACHING is important to ensure it keeps changing for each value and doesn’t re-use values
dbo = target schema
MLTEST = target table name
Insert_Date = the field name
EMP_ID = the PK field name for the table in the database syntax
$EMP_ID is the replicate internal representation of the PK field for the table

NOTE: That the lookup may have a performance implication and will need to be tested to see if it meets all of your latency criteria. 

NOTE: You will want to check if the record is being inserted for the first time or updated and only do the lookup if the operation is an Update. The following code shows how to implement this.

The complete transformation code as follows:

CASE
WHEN $AR_H_OPERATION = 'INSERT' then
datetime('now')
ELSE
target_lookup (1,'dbo','MLTEST','Insert_Date','EMP_ID=?',$EMP_ID)
END

This expression is created for the Add Column transformation Insert_Date as seen in the screen shot below.

 NOTE: Unfortunately data enrichment functions can not be tested on the screen, they must be saved and the task run for the transformation to be tested.

 NOTE: Not all target endpoints support the target lookup function. Snowflake does not support this although there is a way to get the same functionality. Please check out the following article:  Qlik Replicate: Transformation column INSERT_DATETIME is overwritten with Null value when record is updated in Snowflake Target 

NOTE: The above target lookup uses the SQL server specific "?" 'EMP_ID=?'and if you are doing this same lookup into an Oracle database use must use the Oracle specific ":" 'EMP_ID=:'

Environment

Qlik Replicate 
Table/Field level transformation

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.

Environment

• Qlik Sense Enterprise on Windows 

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. 

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.

Environment

• Replicate
• Table/Field level transformation

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.

Field Transformation

On my Source  table from Oracle, I am using the source lookup function under Data Enrichment in order to retrieve the Oracle ROWID field value.  Not all fields are logged by oracle into the Redo Logs. In the case of ROWID we will use a source lookup function to query the source table and retrieve the field value, putting it  into the Add Column that we defined and called ROWID. This source lookup function is documented in the replicate user guide and the on-line help.

The general form of the function is as follows:

For this example in am doing the lookup into the Employees table:

NO_CACHING is important to ensure it keeps changing for each value and doesn’t re-use values
HR is the schema in oracle
EMPLOYEES is the table
ROWID is what I want returned
EMPLOYEE_ID=:1 is the predicate for the lookup 
$EMPLOYEE_ID is the value from the redo log that we are using in the predicate in place of :1

NOTE: If the source lookup needed more than one field in the key then: 
(Multiple variables would look like: 'EMPLOYEE_ID=:1 AND DEPARTMENT_ID=:2)
$EMPLOYEE_ID, $DEPARTMENT_ID (in this example we are using field values that the task has read from the redo log file.

NOTE: That the lookup may have a performance implication and will need to be tested to see if it meets all of your latency criteria. Please also note that the Oracle ROWID is not guaranteed to persist. Traditional Oracle ROWID's can change if a table is quiesced or rebuilt with dbms_redefinition.

This expression is created for the Add Column transformation ROWID as seen in the screen shot below.

Transformation Screen shot

NOTE: Unfortunately data enrichment function can not be tested on the screen, they must be saved and the task run for the transformation to be tested.

The screen shot below shows the target table with the ROWID field after the task has run.

Target Table with ROWID field

Related Content

When using the google drive & spreadsheet the table "GetWorksheet"  it might fail with one of the following messages depending on the platform used 

Qlik Web connectors on-premise error message:

<Exception>APIConnectorInterfaces.Exceptions.APIConnectorException: Failed on attempt 1 toGET. (The remote server returned an error: (400) Bad Request.) ---&gt; System.Net.WebException: Theremote server returned an error: (400) Bad Request.

Cloud error message:

HttpStatusCode: BadRequest, StatusCode: 400, StatusDescription: Bad Request, Message: The remote server returned an error: (400) Bad Request., ResponseBody: Error 400 (Bad Request)!!

Environment

• Qlik Web Connectors  December 2020
• Qlik Sense Business 
• Qlik Cloud 

Current Status

This issue has been resolved in a fix that was pushed to Qlik Sense Enterprise SaaS and Qlik Sense Business on Friday, May 7th. A patch is available for Qlik Web Connectors, December 2020 Patch 2. The patch can be found on the Qlik Download site.

GetWorksheet is now deprecated. GetWorksheetV2 or GetSheetValues should be used moving forward.

Workarounds

If the Gsheet has to be Private:

Alternatively, to "GetWorksheet" the "GetSheetValues" and the parameters shown in the attached PDF, should return the same data as the table "GetWorksheet".  Please, make sure to double-check that the data matches before doing any changes to the apps.

If the Gsheet can be Public:

Making the sheet public also solves the problem and allows you to keep using the table "GetWorksheet", but please note that we only suggest this workaround for very specific cases where the data is already public or doesn't contain confidential information.

Cause

Qlik Web Connectors used a legacy method for getWorksheet. A change to this API by Google caused the defect. This has been logged with Qlik as product Defect ID: QCWP-3096.

Qlik Application Automation has a "convert timezone" block that allows you to convert a given time from one timezone to another. This article explains how this block should be used.

In some use-cases, you want to ask a user to enter her timezone as a parameter in an inputs block. To do this you could use a dropdown that you populate with possible timezones. Use the "List timezones" formula in combination with the "implode" formula to populate the dropdown's options field.

The unparsed formula looks like this: {implode: '{listtimezones}', ','}

Then you can use the timezone the user selected in the "convert timezone" formula. The following example will convert a 13:00:00 time in timezone 'Europe/Brussels' to a time in a timezone specified by the user.

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.

Qlik Sense shows problems or delays in the saving process. This can happen when the QVF file is locked, for example because a user is opening it.

The most common issues are:

• Slowness or failures in saving apps changes . Changes made by users may be lost.
• Create, duplicate or import an app fails with an 'unknown error'
• App are not updated after a reload. The task looks like successful but the file on disk is not updated.

Environment

• Qlik Sense 2019
• Qlik Sense 2020

Resolution

An upgrade is required. The defect is fixed in the releases below and in the higher versions:

• November 2019 Patch 15
• February 2020 Patch 8
• April 2020 Patch 7
• June 2020 Patch 5
• September 2020

Cause

Product Defect ID: QB-2107

Executing tasks or modifying tasks (changing owner, renaming an app) in the Qlik Sense Management Console and refreshing the page does not update the correct task status. Issue affects Content Admin and Deployment Admin roles.

The behaviour began after an upgrade of Qlik Sense Enterprise on Windows. 

Fix version:

This issue can be mitigated beginning with August 2021 by enabling the QMCCachingSupport Security Rule.

Solution for August 2023 and above:

Enable QmcTaskTableCacheDisabled.

To do so:

1. Navigate to C:\Program Files\Qlik\Sense\CapabilityService\
2. Locate and open the capabilities.json
3. Modify or add the QmcTaskTableCacheDisabled flag for these values to true
   
   {"contentHash":"CONTENTHASHHERE","originalClassName":"FeatureToggle","flag":"QmcTaskTableCacheDisabled","enabled":true}
   
   Where CONTENTHASHHERE matches the number in all other features listed in the capabilities.json.
   
   Example:
   
   
   
   This will disable the caching on the tasks table only, leaving the overall QMC Cache intact to gain performance. If you had previously set QmcCacheEnabled, QmcDirtyChecking, QmcExtendedCaching to false, please set it to true again.
   
   
4. Restart the Qlik Sense services

Workaround for earlier versions:

Upgrade to the latest Service Release and disable the caching functionality: 

To do so:

1. Navigate to C:\Program Files\Qlik\Sense\CapabilityService\
2. Locate and open the capabilities.json
3. Modify the flag for these values to false
   • QmcCacheEnabled
   • QmcDirtyChecking
   • QmcExtendedCaching
4. Restart the Qlik Sense services
   
   

NOTE: Make sure to use lower case when setting values to true or false as capabilities.json file is case sensitive.

Should the issue persist after applying the workaround/fix, contact Qlik Support.

Internal Investigation ID(s): 

QB-2096
QB-5168
QB-7655
QB-17238

Environment

Qlik Sense Enterprise on Windows 

If Qlik Sense Enterprise on Windows is deployed using a standalone DB inside Azure you can't switch the minimum TLS version to 1.2. This will cause the repository service to fail to connect to this DB server to connect to QSR and QSMQ.

You will notice that you still can connect to the DB using the QlikSenseUtil, this is due to the fact that this one is using the Npgsql PostgreSQL library, while the Repository service is using the devart library.

Environment

• Qlik Sense Enterprise on Windows all versions

Resolution

This setup can only be performed on an existing installation. Current Qlik Sense installers do not support connecting to an encrypted database. Install Qlik Sense on an unencrypted database and enable encryption after the fact. See Database security for details.

To resolve this specific defect, upgrade to August 2021 IR or newer versions. See Qlik Sense Enterprise on Windows Release notes - August 2021 IR to Patch 13 and after that, encrypt your Azure PostgresDB.

Workaround*:

It is possible to harden the Qlik Sense Server on OS level, to only allow TLS 1.2 communication, this would open a TLS 1.2 connection to the database. Given that a minimum TLS version in the Azure portal is either not specified or below 1.2.

To verify the configuration from the server side you can develop a test application running this query

SELECT* FROM pg_stat_ssl WHERE pid = pg_backend_pid();

a result can look like this:

*Workaround can be used in case you are using a version where the fix was still not delivered.

Cause

Product Defect ID: QB-3185

Resolution

Cause

• Change in internal image rendering method in NPrinting 19 and higher versions

Related Content

• https://community.qlik.com/t5/Knowledge-Base/NPrinting-16-to-NPrinting-19-Migration-Playbook/ta-p/1780278

Internal Investigation

• Defect OP-9405

Not able to perform a successful connection verification in NPrinting Feb 2020 when making a QVP connection
QVP connection encounters a tunneling error resulting from the setting shown below:

Environment

NPrinting Feb. 2020

Resolution

• Verify that port 4747 is open for communication
• Remove any local security policy which restricts QlikView desktop communication.
• In this case, remove the checkbox show and switch the policy so that it is 'undefined'

Cause

• Change made to default active directory policy which cause inference with NPrinting Connection success via the QlikView desktop

Related Information:

• https://community.qlik.com/t5/Knowledge-Base/Disabling-Kerberos-encryption-method-RC4-HMAC-MD5-causes/ta-p/1715113

When trying to send straight table to excel using 'Send to Excel' option, the output screen crosses out and no excel file produced.

Environment:

• QlikView Desktop April 2019 (12.40)

Resolution

Fix Version:

• QlikView Desktop May 2021 (12.60)

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 at support.qlik.com with the ID QV-21725 for reference.

Cause

Product Defect ID: QV-21725

In very rare condition, when trying to export to excel on straight table which doesn't contain any hypercube data, the object will be corrupted.


Note: Attached qvw for reference.

QlikView Converter fails to convert with error:  "ENOENT: no such file or directory, mkdir  C:\TEMP\subfolder\username"  in the logs.   

Environment

• Qlik Sense Desktop February 2021 
• Qlik Sense for Windows February 2021 

Resolution

Be sure that the temp folder C:\TEMP\subfolder\username exist on file system, that folder is retrieved from the user environment variable %temp%, by default on Windows it is expected to be in  C:\Users\username\AppData\Local\Temp.

Cause 

Customized user variable %temp% folder  not existing; automatic "user" custom tasks are cleaning the C:\TEMP\subfolder at server restart.

Related Content

Help page for the converter https://help.qlik.com/en-US/sense-developer/February2021/Subsystems/Dev-Hub/Content/Sense_Dev-Hub/ConversionTool/conversion-tool.htm .

In order to get a list of all the connectors available on Blendr.io, we have an unauthenticated HTTPS call that can be used in the blendr platform.

Please check list of connectors Saas API call to get a brief understanding of this API. Using this API call in the call URL block of the automation editor gives output in HTML format. This output can be converted to JSON format by following the steps shown in below loom video

Video showing on how to get json output from list connectors saas API call 

The steps below allows for checking on the Qlik Sense signed license definition allotments using PowerShell.

Environment

• Qlik Sense Enterprise on Windows, April 2020 or higher

1. Export client certificate and root certificate to make API calls
2. Open Windows PowerShell ISE on the Qlik Sense server
3. Run the following lines:

$hdrs = @{
    "X-Qlik-Xrfkey"="examplexrfkey123"
    "X-Qlik-User"="UserDirectory=INTERNAL; UserId=sa_api"
}

$cert = Get-ChildItem -Path "Cert:\CurrentUser\My" | Where {$_.Subject -like '*QlikClient*'}

$FQDN = [System.Text.Encoding]::UTF8.GetString([System.Convert]::FromBase64String((Get-Content C:\ProgramData\Qlik\Sense\Host.cfg)))

$license = Invoke-RestMethod -Uri "https://$($FQDN):9200/v1/licenses/definition?xrfkey=examplexrfkey123" -Method Get -Headers $hdrs -ContentType 'application/json' -Certificate $cert

$license.allotments

 
4. An output similar to the following should display: