By calling the license APIs in the brower's URL, it's possible to get information about which features are included in the current license.

Environment

• Qlik Cloud 

Resolution

1.  access the tenant's URL and authenticate
2. append /api/v1/licenses/overview to the URL (e.g. https://mytenant.myrg.qlikcloud.com/api/v1/licenses/overview )
3. The information will be visualised in the browser
   1. most browsers will show the raw JSON format (if not using extensions that change that behaviour)
      
      

       

   2. some other browsers, like FireFox, have a built-in JSON visualizer
      
      
      
      
4. Search for the key that you are expecting to find and the values associated with it to confirm that the feature is enabled
   
   

   {"name":"qvNodes","valid":"./.","values":{"count":3}},
   
   {"name":"reportingService","valid":"./.","values":{"quantity":1000,"value":""}}]​

Testing the connection to an AWS RDS hosted Mariadb database fails with:

SYS-E-HTTPFAIL, No MySQL ODBC Unicode Driver version x.x or above is installed..
SYS,GENERAL_EXCEPTION, No MySQL ODBC Unicode Driver version x.x or above is installed.

Resolution

Verify that the Qlik Replicate documentation was followed correctly and that the /etc/odbcinst.ini file contains an entry for MySQL. See Driver prerequisites | Qlik Replicate Help.

If a new driver was installed, it may have overwritten a previous entry or wrongly added a new one.

Per Qlik documentation:

[MySQL ODBC 5.3.13 Unicode Driver]
Driver = /usr/lib64/libmyodbc5w.so
UsageCount = 1

Per the driver installation:

[MySQL ODBC 5.3 Unicode Driver]
Driver=/usr/lib64/libmyodbc5w.so
UsageCount=1

Remove .13 in the .ini file to resolve this.

Environment

Qlik Replicate 

Some script logs are not transferred to Archive log Folder.

Storage Locations:

Qlik Sense log folder:

C:\ProgramData\Qlik\Sense\Log\Script
Log names consist out of the AppID and a year, date, and timestamp.

Archived log Folder:

 \\SERVICECLUSTER-SHARE\ArchivedLogs\NodeName\Script
Log names consist out of the AppID and a year, date, and timestamp.

Environment:

• Qlik Sense Enterprise on Windows, all versions

Resolution:

By design, not all script logs are transferred to the Archived logs folder. Reloads executed from the hub are not transferred. 

1. When reloading data in app via hub, Script log will be left in Script log folder C:\ProgramData\Qlik\Sense\Log\Script

2. When reloading data via QMC/task, Script log will be transferred to Archived logs folder \\SERVICECLUSTER-SHARE\ArchivedLogs\NodeName\Script

Related Content:

• How to find the Script (Reload) logs in Qlik Sense Enterprise

Security Rules can be configured to allow end-users to reload tasks directly from the Qlik Sense Hub. This way the users who don't have access to Qlik Sense Management Console can run the tasks that are created for the app directly from the hub.

Environment

• Qlik Sense February 2021 & later (For "Manage reload tasks": start/stop existing tasks from the hub)
• Qlik Sense May 2021 & later (An addition to "Reload": create tasks from the hub)

Security Rule examples are provided as-is. Further customization cannot be supplied by Qlik Support and we recommend engaging with our active community in the Qlik Sense Management forums for further assistance. 

Requirements and Considerations

• By default, a user will have Read and Update permissions on Apps they own, whether they are Published or not. 
• If you intend to give users permission to reload a published App they do not own, a separate security rule will be necessary to enable Read and Update permissions on the App. 
• A Professional License. Analyzer licenses will only be able to view tasks, even with the security rules in place. 
• By default, all users have access to the required HubSection_Task. This is the starting point to allow users to reload from the hub. If custom rules have been implemented that block access to HubSection_Task, this needs to be lifted or customised.

Note: Too many tasks run in parallel by users can cause a performance impact. 

Set up the Security Rules:

1. Open the Qlik Sense Management Console
2. Go to Security Rules in the left side menu
3. Click Create New 
4. Set up either a for only managing existing tasks only or b for creating new tasks and managing existing tasks:
   
   
   1. Start and Stop existing Reload Tasks only
      
      Name: Reload Access from Hub (example) 
      Description: Allows users to reload apps from the hub  (example) 
      Resource filter: ReloadTask_* (Can be chosen from the dropdown menu next to the box)
      Actions: Read, Update 
      Conditions: Specify your user or group or role.
      
      
   2. Create a new Reload Task and launch it as well as manage existing tasks
      
      Name: Reload Access from Hub (example) 
      Description: Allows users to create reloads from the hub  (example) 
      Resource filter: ReloadTask_* (Can be chosen from the dropdown menu next to the box)
      Actions: Read, Update, Create 
      Conditions: Specify your user or group or role.
      
      
5. Apply both rules

The specified users can now right-click on the hub to access the Management Reload Task (rule a) or/and Reload option (rule b). See Fig 1.

Fig 1

Clicking Reload to initiate the reload and open the App in a new tab.

Clicking Manage reload tasks to access all available reload tasks for this App. Tasks can then be started (Fig 2) or stopped (Fig 3)from this menu.

Fig 2

Fig 3

Additional Notes:

• Giving access to the Apps will always expose the Manage Properties option.
  
  This can be mitigated by modifying the existing ReadCustomProperties rule to fit your needs. However, this will not hide the Manage Properties option. 
  
  
• Giving Update permissions to users allows them to edit the App's name, description, and thumbnail. This is working as expected. 

The distribution of QlikView apps from QlikView Distribution Service to Qlik Cloud fails with the error "TestCloudLicense: Failed - Unauthorized. Failed to retrieve qv_nodes tag."

Environment

• QlikView 
• Qlik Cloud 

Resolution

• The license used in Qlik Cloud is missing the QV_NODES tag, which allows the access to QlikView apps from Sense Deployments. For QlikView to distribute to a Qlik Cloud tenant,  the tag must be present in the tenant license.

Please, reach out to your account manager to have that fixed.

• This issue could also occur if there are missing configuration entries in he QVDistributionService.exe.config  file, refer to the following article QlikView Distribute to cloud fails: The type initializer for System.IdentityModel.Tokens.Jwt.JsonExtensions threw an exception.

When trying to use Qlik Data Transfer for the first time after a correct installation, you might have the below white screen:

Having a white screen basically means one of the Qlik DataTransfer micro service is not started and/or not working. This can be checked in the Windows Event Application (Source: Qlik Sense Service Dispatcher) or in the Qlik DataTransfer logs (C:\ProgramData\Qlik\DataTransfer\Log).

In the \DataUploadService logs you will see this error message:

23	20211115T131155.794+0100	localhost	6.0.0	Global	INFO	Responding certificates after reading it from defined location.

24	20211115T131155.844+0100	localhost	6.0.0	Global	ERROR	Unable to unlock the ReloadService, cannot continue.

25	20211115T131155.845+0100	localhost	6.0.0	Global	ERROR	Exception: Error: Unable to unlock the reload service.

Environment

• Qlik Data Transfer any release

Resolution

This error message is happening directly after the step of reading certificate:

INFO	Responding certificates after reading it from defined location.

• So you would need to check what certificate do you have in the MMC console. If you see some Qlik DataTransfer duplicate certificates like below, it's an issue.
  
  

It will be needed to delete the 2 duplicates (where there is no Friendly Name), and then restart the Qlik DataTransfer service.

• We had another case where the issue was present in a Windows server where Qlik Sense and Qlik Data Transfer was installed.
  
  Same error logs which means something is wrong at the certificate level. In that specific case, in the MMC console --> Certificates (Local Computer) --> Personal -- Certificates:
  There was a QlikClient certificate which shouldn't be here. 
  
  To find that I had to install the same Qlik Sense version and check what certificate I do have at this location. 
  
  To solve the issue I've deleted the wrong certificate and I've done the Proposed action.
  
  
• Another possible cause could be a Windows variable with a proxy, example:
  https_proxy=http://PROXY:80
  
  Qlik Data Transfer does not support Proxy. Solution would be to delete this Windows variable.
  
  
• You also could be in the below situation where there is no certificates at all.
  
  

In that situation it would be needed to install the certificate and/or check why your system prohibit the certificates installation.
It could be a group policy preventing the installation of the needed SSL certificates. Or some missing rights for the Windows account.

Proposed action  

If only Qlik DataTransfer is installed on the machine, do the following:

1. In Windows Services, stop the Qlik DataTransfer service.

2. Navigate to C:\ProgramData\Qlik\DataTransfer\DataUpload\certificate and delete the certificates there.

3. Navigate to C:\Program Files\Qlik\DataTransfer\DataUploadService\install-utils.

4. Run Remove-Certificate.ps1 in PowerShell.

5. Delete the following folder: C:\ProgramData\Qlik\DataTransfer\DataUpload\certificate
   

6. Start the Qlik DataTransfer service.

7. Navigate to C:\ProgramData\Qlik\DataTransfer\DataUpload\certificate and confirm the following files are present:

   • client.pfx

   • server.pfx

If Qlik DataTransfer and Qlik Sense Enterprise on Windows are installed on the machine, do the following:

1. In Windows, Services, stop the Qlik DataTransfer service.

2. Navigate to C:\ProgramData\Qlik\DataTransfer\DataUpload\certificate and delete the certificates in the folder.

3. In the Windows Certificate Store, delete the following certificates:

   • Local Computer > Personal > QdtClient

   • Local Computer > Trusted Root Certification Authority > <hostname.domain> or <hostname>

   • Current user > Personal > QdtClient

   Alternatively, you can run Remove-Certificate.ps1 in PowerShell as the administrator.
   
   

4. Run Configure-Service.ps1 in PowerShell as the Qlik Sense services user.

   1. For the first input dataTransferCertificatePath, enter C:\ProgramData\Qlik\DataTransfer\DataUpload\certificate.

   2. For the second input qsefwPresent, enter Yes.

5. Navigate to C:\ProgramData\Qlik\DataTransfer\DataUpload\certificate and confirm the following files are present:

   • root.pfx

   • client.pfx

   • server.pfx

6. Restart the Qlik DataTransfer service.

Related Content 

Installing and Using Qlik DataTransfer  

Troubleshooting Qlik DataTransfer 

With Qlik Application Automation, you can get data out of Qlik Cloud and distributing it to different users in formatted Excel. The workflow can be automated by leveraging the connectors for Office 365, specifically Microsoft SharePoint and Microsoft Excel. 

Here I share two example Qlik Application Automation workspaces that you can use and modify to suit your requirements. 

Content:

• Considerations
• Example 1: Scheduled Reports
• Example 2: On-Demand Reports
• Environment

Video:

Considerations

• This example is built on distributing a SharePoint link. It is also possible to use attachments with the Mail block (see Creating a Qlik Reporting Service report).
• Qlik Application Automation has a limit of 100,000 rows when getting data out of a Qlik Sense straight table object.
• The On-Demand example uses an extension in QSE SaaS to send data to the Automation. An update to the Qlik Sense Button object is expected soon, which will provide a native way to pass selections to an Automation.

Example 1: Scheduled Reports

1. Download the 'Scheduled Report.json' file attached to this document.
2. Create a new Automation in QSE SaaS, give it a name, and then upload the workspace you just downloaded by right clicking in the editor canvas, and selecting 'Upload workspace'.
3. Select the 'Create Binary File (Personal One Drive)' block, select 'Connection' in the block configurator to the right, and then create your connection to Microsoft SharePoint.
4. Select the 'Get Straight Table Data' block. Under 'Inputs' in the block configurator, lookup your the App Id, Sheet Id, and Object Id for the relevant QSE SaaS table you wish to output.
5. Select the 'Create Excel Table With Headers' block, select 'Connection' in the block configurator, and then create your connection to Microsoft Excel.
6. Select the 'Send Mail' block. Under 'Inputs' in the block configurator update the 'To' to reflect the addresses you wish to deliver to.
7. With the 'Send Mail' block still selected, select 'Connection' in the block configurator and add your Sender details.
8. To test, Save and then Run the Automation
9. If you receive any warnings or errors, navigate to the relevant blocks and ensure your Connection is selected in the block configurator. 
10. Select the 'Start' block. Under 'Inputs' in the block configurator, change Run Mode to Scheduled and define your required schedule. 

Example 2: On-Demand Reports

Note - These instructions assume you have already created connections as required in Example 1.

1. Download the 'On-Demand Report v3.json' file attached to this document.
2. Download and install the 'qlik-blends' extension. See: 
   https://github.com/rileymd88/qlik-blends/files/6378232/qlik-blends.zip
3. Create a new Automation in QSE SaaS, give it a name, and then upload the workspace you just downloaded by right-clicking in the editor canvas, and selecting 'Upload workspace'.
4. Ensure your Connections are selected in the block configurator for each of the following blocks, 'Create Binary File (Personal One Drive)', 'Create Excel Table With Headers', 'Add Rows To Excel Worksheet Table (Batch)', 'Create Sharing Link', and 'Send Mail'.  Save the Automation.
5. Select the 'Start' block and ensure Run Mode is set to Triggered. Make note of the of URL and Execution Token shown in the POST example. 
6. Open your chosen QSE SaaS application, and Edit the Sheet where you wish to add a Button to trigger an On-Demand report. 
7. Under 'Custom Objects' look for 'qlik-blends' from the Extensions menu and drag this into your Sheet.
8. Under the 'Blend' properties to the right, add-in your POST webhook URL and Token as noted in Step 5.
9. We will now add three measures to the 'qlik-blends' object. It is important you add them in the order described.  Add the first measure, using the following function in the expression editor: GetCurrentSelections()
10. Add the second measure, using the following function DocumentName()
11. The final measure will be the Object ID of the table you wish to use. To find the Object ID, select 'Done Editing'.  Then right click on the table, select 'Share', select 'Embed', then look for the Object ID under the preview. Copy this value, go back into Editing mode and paste this as your third measure value. 
12. With the 'qlik-blends' object selected, under Form select 'Add items'. For 'Item type' select Text. Under default value you can choose to add a default email address. For 'Label' and 'Reference'  type 'Email'. It is critical that Reference is updated to 'Email'.  Turn 'Required input' on. 
13. You can change the Appearance properties to suit your preferences, such as updating the Button label and message, enabling Dialog, and changing the Color under Theme.
14. Back in the Automation, under the Start Block. Set  'Run asynchronously' = yes to allow multiple requests to run at the same time (This will also increase the max run time from 1min to 60min)
15. Once happy, test the On-Demand report by entering an email and clicking the button.

This On-Demand Report Automation can be used across multiple apps and tables.  Simply copy the extension object between apps & sheets, and update the Object ID (Measure 3) for each instance. 

Environment

• Qlik Application Automation 
• Qlik Cloud 
• Microsoft Office 365

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 is meant to get users starting on the Oauth connector in Qlik Application Automation.
This connector is meant for advanced users to platforms where we did not (yet) build a dedicated connector for.
The connector makes use of the authorization code flow of Oauth2. The authorization code flow is well explained on the following link: https://oauth.net/2/grant-types/authorization-code/

Limitations

• This connector will work for most OAuth2 flows. In cases where there's additional fields to be sent such as additional query parameters in the authorize request, this connector will not work.
• For the list endpoints other than those listed below The solution is to implement paging at the automation level. This is well explained in the following article.: https://community.qlik.com/t5/Knowledge/How-to-implement-pagination-using-Call-Url-block-in-an/ta-p/1879458
  • raw API list request - offset based paging
  • raw API list request - page number based paging.
  • raw API list request - cursor token based paging
  • raw API list request - cursor URL based paging
• If the cursor token or cursor URL is returned in the response header instead of the response body, the cursor URL and cursor token endpoints do not work.
• The list blocks won't work if the business data in the API response is not enclosed in a JSON key.

How to connect

First start by looking for the connector "OAuth2". When you click on connect it requests the following input parameters:

• Base URL - This should be the base of the URL you will be making REST calls to. We recommend providing this as much as possible as it will prevent making calls to other URL's.
• Authorize URL - Can usually be obtained from the documentation of the platform you're attempting to connect to.
• Access Token URL - Can usually be obtained from the documentation of the platform you're attempting to connect to.
• Refresh Token URL - Can usually be obtained from the documentation of the platform you're attempting to connect to. Most of the time this is the exact same as the Access Token URL.
• OAuth Client ID - Will have to be created in the platform you're connecting to.
• OAuth Client Secret - Will have to be created in the platform you're connecting to.
• OAuth Scopes - Obtain from documentation.

When all of this is provided and you click the Save button, it will open a popup taking you to the login screen where you will be prompted to provide consent. Your connection is now ready to be used.

How to use in an automation

When selecting the connector in an automation you will notice Raw API Request block. This block has the following input variables:

• Path - This needs to be relative to the Base URL you provided when connecting.
• Method - Enum choice between GET, POST, DELETE, PATCH or PUT.
• Query Parameters - Here you can provide a JSON object and the key/values will be added to the path as query parameters.
• Request Body - A JSON object can be provided for POST, PATCH or PUT operations that will be sent as request body.
• Headers - JSON object and these key/values will be added as headers to the request.

Apart from the above input variables, RAW API LIST blocks have the following input variables

• Page Size Field Name - Name of the page size query parameter.
• Page Size - The desired page size. The maximum value allowed is 200.
• Offset Field Name - Name of the offset query parameter.
• Page Start - The initial value of the Offset Field Name or Page Number Field Name query parameter. It should be either 0 or 1.
  • For offset based pagination: Depending on the page size and the page start the value of the offset parameter for the second page will be calculated. 
  • For Page number based pagination: The page number is either a 1-based or zero-based value. The value will be incremented by 1 to fetch the next page's data.
• Json Path For Output Records - The JSON key's name in the response will be used to parse a portion of the overall JSON returned by the API endpoint. This only returns a parsed VALUE.
• Json Path For Cursor - The JSON key in the API response whose cursor/token value can be used to request the next page of resources. 
• Output Name For Cursor Token - Name of the cursor token query parameter.
• Page Number Field Name - Name of the page number query parameter. 
• Json Path For Cursor Url - The JSON key in the API response, which is effectively a URL, can be used to retrieve the next page of resources. 

An example

The following example will obtain tasks from the Microsoft Todo application. We do not yet have a connector to do this and Oauth is used to obtain the value for the Authorization header.
We first start by obtaining the Authorize URL, Access Ttoken URL and Refresh Token URL. From the Microsoft documentation we can find these at https://docs.microsoft.com/en-us/graph/auth-v2-user. We use the following values:

• Authorize URL: https://login.microsoftonline.com/common/oauth2/v2.0/authorize
• Access Token URL: https://login.microsoftonline.com/common/oauth2/v2.0/token
• Refresh Token URL: https://login.microsoftonline.com/common/oauth2/v2.0/token

The documentation of the Microsoft Graph API  can be found at https://docs.microsoft.com/en-us/graph/use-the-api?context=graph%2Fapi%2F1.0&view=graph-rest-1.0.
We obtain the Base URL: https://graph.microsoft.com/v1.0.

We will have to obtain a client ID and client secret. We can do the registration for an app at https://portal.azure.com/#blade/Microsoft_AAD_RegisteredApps/ApplicationsListBlade. We register a new application, give it the name "Todo App" and set it to only allow accounts from this organizational directory only. For the redirect url the following value needs to be provided: https://connector.qlikcloud.com/callback. Set the platform to Web.

On the next screen you can obtain your Client ID. Copy this value. Click on Add a certificate or Secret and it will take you to a page where you can create a new client secret.
Create one and copy the value that is now visible.

In the left sidebar click on the button API Permissions. Click on Add a Permission and provide the scope Tasks.ReadWrite.

Back to your connector configuration - Provide the client ID and client secret that were obtained and for the scopes provide the following: User.Read Tasks.ReadWrite offline_access openid
Now click on Save and you should be presented with a screen to login.

Using it in an automation

1) Raw API Request block:

Create a new automation and drag the Raw API Request block on to the canvas. Set the path to /me and the method to GET. When you now execute it, you should receive a JSON result containing the keys response, headers and status. Most relevant information should be part of the response.

Next let's obtain a list of tasks. Drag another Raw API request block and put it below the earlier one. Set the path to /users/{$.rawApiRequest.response.id}/todo/lists and method should remain at GET. Now when you execute this, it will return you the lists you keep in Todo.

2) List Blocks: Depending on the type of pagination, we have 4 different List blocks. Therefore, depending on the type of pagination in API, any one of the List blocks should be used for a certain API.

We tested the blocks using four distinct APIs, each with a different pagination implementation.

• raw API list request - page number based paging: Zoho
• raw API list request - offset based paging: JIRA
• raw API list request - cursor token based paging : Google People
• raw API list request - cursor URL based paging : Bitbucket

1. raw API list request - page number based paging: 
   Create new automation and drag the raw API list request - page number based paging block onto the canvas.
   Create a new connection. We used the Zoho API. Please visit the API documentation link for your API to obtain Oauth information and the pagination documentation to obtain pagination information.
   
   
   Below are the input parameter
   
   1. path: If the baseUrl in the connection parameter ends with '/', do not prefix the same character at the beginning of the path in the endpoint input parameter. We are using "Leads" to list the lead in Zoho.
   2. Query Parameters: We haven't used them in the example. Similarly, we can try a variety of other query parameters listed in the API documentation.
   3. Headers: We don't use it because we don't require any further header information.
   4. Page Size Field Name: per_page is the query parameter name used to reduce the amount of data returned in a single API call.
   5. Page Size: The query parameter per_page's value. We made use of 8. If the entered value exceeds 200. We'll set it to 200 in the backend.
   6. Page Number Field Name: Because this is page-based pagination, the page query parameter is 'page'.
   7. Page Start: This is the number that indicates where the page begins. The value should be either 0 or 1. This value will only be used in the first API request; in subsequent calls, it will be increased by one in the backend and used in the above page query parameter name. We have used 1.
   8. Json Path For Output Records: Parse a portion of the JSON returned by the API endpoint. This only returns a parsed VALUE. We have used 'data' in our example.
      
      
2. raw API list request - offset based paging : 
   Create new automation and drag the raw API list request - offset based paging block onto the canvas.
   Create a new connection. We used the JIRA API. Please visit the API documentation link for your API to obtain Oauth information and the pagination documentation to obtain pagination information.
   
   
   Below are the input parameter
   
   1. path: If the baseUrl in the connection parameter ends with '/', do not prefix the same character at the beginning of the path in the endpoint input parameter. We are using "Search" to search the issues
   2. Query Parameters: We utilized the fields query parameter to limit the data returned by JSON Object. Similarly, we can experiment with many additional query parameters listed in the API documentation.
   3. Headers: We don't use it because we don't require any further header information.
   4. Page Size Field Name: maxResults is the query parameter name used to reduce the amount of data returned in a single API call.
   5. Page Size: The query parameter maxResults's value. We made use of 50. If the entered value exceeds 200. We'll set it to 200 in the backend.
   6. Offset Field Name: Because this is offset pagination, the offset query parameter is startAt.
   7. Page Start: This is the offset's starting number. The value should be either 0 or 1 
   8. Json Path For Output Records: Parse a portion of the JSON returned by the API endpoint. This only returns a parsed VALUE. According to the JIRA API docs, we're getting a list of issues from the "issues" key.
      
3. raw API list request - cursor token based paging:
   Create new automation and drag the raw API list request - token based paging block onto the canvas.
   Create a new connection. We used the Google People API. Please visit the API documentation link for your API to obtain Oauth information and the pagination documentation to obtain pagination information.
   
   
   Below are the input parameter
   
   1. path: If the baseUrl in the connection parameter ends with '/', do not prefix the same character at the beginning of the path in the endpoint input parameter. We are trying to list the contactGroups in Google People.
   2. Query parameters: We haven't used them in the example. Similarly, we can try a variety of other query parameters listed in the API documentation.
   3. Headers: We don't use it because we don't require any further header information.
   4. Page Size Field Name: pageSize is the query parameter name used to reduce the amount of data returned in a single API call.
   5. Page Size: The query parameter maxResults's value. We made use of 20. If the entered value exceeds 200. We'll set it to 200 in the backend.
   6. Json Path For Cursor: The JSON key in the API response, the cursor value will be used to request the next page of resources. Use '.' to denote objects within an array. This endpoint block will not work if the cursor URL is returned in the header.
   7. Output Name For Cursor Token: The name of the cursor Query parameter, which will be used to fetch resources for the following page. We used pageToken in this case.
   8. Json Path For Output Records: Parse a portion of the JSON returned by the API endpoint. This only returns a parsed VALUE.
      
      
4. raw API list request - cursor URL based paging
   Create new automation and drag the raw API list request - URL based paging block onto the canvas.
   Create a new connection. We used the Bitbucket API. Please visit the API documentation link for your API to obtain Oauth information and the pagination documentation to obtain pagination information.
   
   
   Below are the input parameter
   
   1. path: If the baseUrl in the connection parameter ends with '/', do not prefix the same character at the beginning of the path in the endpoint input parameter. We are trying to list the commits.
   2. Query parameters: We haven't used them in the example. Similarly, we can try a variety of other query parameters listed in the API documentation.
   3. Headers: We don't use it because we don't require any further header information.
   4. Page Size Field Name: maxResults is the query parameter name used to reduce the amount of data returned in a single API call.
   5. Page Size: The query parameter maxResults's value. We made use of 50. If the entered value exceeds 200. We'll set it to 200 in the backend.
   6. Json Path For Cursor Url: The JSON key in the API response, which is effectively a URL, can be used to retrieve the next page of resources. Use '.' to denote objects within an array. This endpoint block will not work if the cursor URL is returned in the header.
   7. Json Path For Output Records: Parse a portion of the JSON returned by the API endpoint. This only returns a parsed VALUE.

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.

Beginning with QlikView May 2021, the QlikView Publisher no longer installs PDF-XChange and will default to Microsoft Print To PDF. 

How to generate PDF reports on a Windows 2012 server, since Print To PDF is not installed there?

Resolution

Have the PDF X-Change 3.0 installed with a previous version of QlikView. If you begin with 12.60, follow this article.

After PDF X-Change 3.0 has been installed, follow these steps:

1. Stop the QlikView Management Service
   
   
2. Using a administrator Notepad, edit:
     
   C:\Program Files\QlikView\Management Service\QVManagementService.exe.config
   
   
3. Locate the <appSettings> section
    
4. Add the line
   
   <add key="ShowPdfPrinters" value="true" />
   
   
   

    

5. Save the file and start the QlikView Management Service
   
   
6. Open the QlikView Management Console:
   1. Go to System
   2. Go to Setup
   3. Expand the Distribution Services
   4. Choose your Distribution Service
   5. Switch to the General tab
   6. Scroll to the Settings for QDS section
   7. You can now configure the PDF Printer using a dropdown 
      
      
      
      

      Additional third party printers have not been tested. In case of performance or stability issues, install one of the two supported printers (PDF X-Change or Microsoft Print to PDF).

Environment

QlikView May 2021 (12.60) on a Windows 2012 server

Currently, in Qlik Application Automation it is not possible to export more than 100,000 cells using the Get Straight Table Data block.

Content:

• Full Automation
• Steps to set up the variables
• Automation structure

To overcome this limit, the workaround is to export records in batches from the Qlik Sense straight table to the cloud storage platform of your choice. The prerequisite is to have a unique numerical field in your dataset. If you don't have the unique field in your dataset, you can add it using RowNo() function in the load script as shown below. This counts the rows in the dataset.

In this example, we will export data from the Qlik Sense straight table to Dropbox as a CSV file.

You can also find an exported version of this automation and application attached to this article. More information on importing automation can be found here.

Full Automation

Automation Part 1

Automation Part 2

Automation Part 3

Steps to set up the variables

1. vAppId: Variable used to define your app ID.
2. vSheetId: Variable used to define your sheet ID.
3. vTableId: Variable used to define your straight table ID.
4. vFieldName: Variable used to define the field name on which the straight table data should be filtered based on the selection applied using the Select Field Value By Query block.

Automation structure

1. Add the Create Measure block to the automation and configure the Measure Expression parameter by providing an expression that calculates the number of rows in the dataset. In this example, I have added RowNo( ) as a unique field to the dataset, so the expression used is count([RowNo( )]).
2. Add the Get Measure block to get the number of rows in the dataset.
3. Store the number of rows obtained from the previous step in the vNRecords variable.
4. Delete the measure created using the Delete Measure block.
5. Add the vNLoops variable to calculate the number of loops that can be used to export straight table data in batches. Please note the formula used in the variable. Edit it to suit your needs. Change the 10000 value as you want but keep in mind that too many records could exceed the Get Straight Table Data block's 100,000 cells limit.
   
   
   
   
6. The Custom Code block creates a list with items 1 to n where n is the number of loops calculated in the previous step.
7. Add Create File on Dropbox block to create a new CSV file in Dropbox. You can select any other cloud storage connector, just remember to configure the relevant value in the Path parameter. The Qlik Sense straight table data will be exported to this file.
8. Add the Select Field Values By Query block to apply selection to the unique identifier created in the dataset to fetch only one record from the Get Straight Table Data block.
9. Add Write Line To File on Dropbox block to add straight table keys as column headers to the CSV file.
10. Add Loop block to loop over the list created using the Custom Code block.
11. Add Condition block to check if it's iterating over the 1st item, if yes set vRecordsStart variable value to 1, else set vRecordsStart variable value to vRecordsEnd variable. The vRecordsEnd variable represents the last record that needs to be fetched from the straight table during each iteration. Please note the formula used in the variable. The value under the Value 1 parameter in the Multiply formula should match the value provided in the Divide formula of the vNLoops variable.
    
    
    
    
12. Add the Select Field Values By Query block to apply selection to the unique identifier created in the dataset to fetch the records between vRecordsStart and vRecordsEnd variables from the Get Straight Table Data block.
13. Add Write Line To File on Dropbox block within the Get Straight Table Data block which writes each straight table data into the CSV file. Configure it to use the output from the Get Straight Table Data block as the Data parameter.
14. Add Save And Close File on Dropbox block and configure the file parameter to use the CSV file we have created.

Environment

• Qlik Cloud
• Qlik Application Automation 

Before reading this, please refer to the official QlikView Authentication - Qlik Online Help and your specific version for details. 

QlikView allows for a number of 3rd party, custom authentication methods to be implemented, which may leverage an Identity Provider (IdP) of choice. However, many aspects of this integration is limited to best-effort support from Qlik Technical Support. See How and When to Contact the Consulting Team?

By default. QlikView only interacts with Windows using the appropriate APIs in the background, allowing Windows to carry out any authentication attempts and passing that information to the QlikView system.  Custom Users in the Directory Service connector are a second option, which allows QlikView itself to do authentication.

Any other user repository will require external, 3rd party implementations.
 

Environment:

• QlikView 

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

This document includes some possible examples in a QlikView 11 implementation of WebTickets and Header authentication, with examples from .NET, PHP, and LDAP integrations. The examples are provided as is. 

! The document references http://localhost:4730/qtds.asmx, which was a valid URL used in QlikView 10 (no longer supported).
This function has been removed in QlikView 11.

! The document references a WebTicket implementation limitation:
A current limitation is that the QVWS or IIS used in steps 2 and 3 (i.e. the server you collect the ticker from and the server you redirect the user to) must be the same machine.
This limitation no longer applies in the current QlikView 12 and 11.20 versions. 
 

SQL server table partitions will be excluded from CDC if the partitions are not found to be uniform. Partitions will not be uniform if only some of the partitions are compressed/uncompressed.

In some occurrences, you may also find that partitions can become non-uniform even when all partitions are uncompressed or compressed. The offset position values of the partitions no longer match with each other which will lead Qlik Replicate to log the following warning message.

Table 'schema.table' is not uniformly mapped across partitions. Therefore - it is excluded from CDC

Resolution

The indexes of the partitions will need to be completely rebuilt if the offset position values have changed and the warnings are received. This fix is only intended for scenarios where all partitions are either uncompressed or compressed. 

Cause 

Limitation:

CDC is not supported from tables which have both compressed and uncompressed partitions.

If only some of the partitions are compressed, the following message will be shown: Table 'xxx' is not uniformly mapped across partitions. Therefore it is excluded from CDC.

SQL Server Limitations 

For instances where all partitions are either compressed or uncompressed, the same warning message will be displayed if partitions are still found to be non-uniform.

The following query can be used to check the offset position values of the partitions. The values are expected to be the same for all partitions for them to be considered uniform. Partition id and schema will need to be changed to reflect your own values.

select
c.colid as col_id,
c.length as col_len,
pc.leaf_offset as xoffset,
pc.leaf_bit_position as bitpos,
pc.leaf_null_bit as null_bit_pos
from syscolumns c ,sysobjects o, sys.system_internals_partition_columns pc
where
(c.colid = pc.partition_column_id) and
(c.id=o.id and o.uid = schema_id(N'dbo')) and
o.name = N'dm_pa' and
o.xtype = N'U' and
pc.partition_id = 72057596702621696 and
pc.is_uniqueifier = 0 and
pc.is_dropped = 0 and
c.iscomputed = 0
order by c.colid;

Internal Investigation ID(s) 

RECOB-6727 

Environment

Qlik Replicate 
SQL Server source endpoint

Datetime2 datatypes should be used in place of the datetime datatype. Qlik Replicate tasks will default to datetime2 datatypes although the datetime datatype can still be used. The datetime datatype can have a loss of precision for milliseconds when in use through the ODBC layer for update operations. This may cause updates to not apply as the loss of precision for the timestamp will cause a mismatch with the original record. The Replicate task will proceed to move the update to the apply_exceptions_table and treat the updates as '0 rows affected'.

This scenario will affect tables that do not have Primary Keys or Unique indexes and MS-CDC is used for replication. In the event that MS-CDC is used, the before images of the datetime columns are also used to check for changes. The before image will not be found due to mismatches from loss of precision.

Resolution

In the table settings of the table, change datetime datatypes to datetime2 datatypes. The tables will need to be reloaded.

Cause 

The design of the datatime datatype goes through some calculations that lead to inaccurate millisecond values for updates when ODBC is involved. Millisecond values are only accurate if they end with 0,3,7 values. Other values are off and will cause a mismatch between the original record and the new copy of the before image.

Datetime2 is the redesigned datatype of datetime and does not exhibit the same behavior with the use of ODBC.

Internal Investigation ID(s) 

RECOB-7133 

Environment

Qlik Replicate 
Microsoft Azure SQL Database target endpoint

You can access Qlik Sense demo apps from https://demos.qlik.com/.

If you are looking for real-life business examples, our Qlik Gallery hosts a platform meant for sharing apps, outcomes, and ideas. It is not restricted to Qlik-provided demos and mostly consists of customer examples.

If you are looking to download demo apps:

1. Go to https://demos.qlik.com/ 
2. Select Downloadable from the available Tags
   
3. Open the App
4. A Download button will be available (if logged in)
   
   

This article provides a step-by-step guide on building a write back solution with only native Qlik components and automations. 

Content:

• Environment
• Write back use cases
• 1. Ticket creation
• 2. Data annotation
• 3. Update records
• Tutorial: ticket creation
• 1. Configuring the button in the app
• 2. Configuring the automation
• Configuring the toast notification
• Reloads
• Limitations

Disclaimer for reporting use cases: this solution could produce inconsistent results in reports produced with automations; when using the button to pass through selections, the intended report composition and associated data reduction for the report may not be achieved. This is due to the fact that the session state of Qlik Application Automation cannot be transferred to the report composition definition that is passed to the Qlik Reporting Service.

Environment

• Qlik Sense app
• Qlik Application Automation
• A business tool that's used as the data source (we'll use HubSpot in this example)

Write back use cases

When analyzing results in a Qlik Sense app, it could happen you spot a mistake in your data or something that seems odd. To address this, you may want someone from your team to investigate this or you may want to update data in your source systems directly without leaving Qlik. Or maybe your data is just fine but you want to add a new record from within Qlik without having to open your business application. These scenarios fit in the following use cases:

1. Ticket creation: create a ticket to ensure a data review or update task will get followed up by your data team
2. Data annotation: add a comment or tag to one or more records in your source data 
3. Data change: add new records or update existing ones
   
   

1. Ticket creation

This is the least intrusive form of writing back that delegates the change to someone in your data team. The idea is that you create a ticket in a task management system like Jira or ServiceNow. Someone from your team will then pick up the ticket, investigate your comment, and review the data. The difference with sending an alert or email is that the ticketing system guarantees that the request is tracked.

2. Data annotation

Another option to communicate changes is to write a comment or a tag for one or more records directly to the source system. This could be a comment on a deal record in your CRM or it could be stored in a separate database table if you're loading data from a database.

3. Update records

The final use case allows for updating records directly from within the sheet. Make sure you know who has access to the button before setting this up since this will allow users to change records directly.

Tutorial: ticket creation

All the above use cases can be realized in the same way: by configuring a native Qlik Sense button in your sheet to run an automation. Before you start this tutorial, make sure you already have an app and a new, empty automation. The tutorial has 2 parts:

1. Configuring the button in the app
2. Configuring the automation

1. Configuring the button in the app

To configure the app, we'll use the following native Qlik Sense components:

• Button: Used to trigger the automation.
• Variable input: Used to allow users to specify parameters to use in the automation (for example, a variable to store a comment).
• Container: Used to group the variable inputs and the button for a better user experience.
• Table: Used to easily select records for the data annotation or ticket creation use case.

Steps:

1. Open your app and add the Container component to a sheet that already contains a Table with records. Then add (a) Button to the Container component. To configure the button, click the (b) Container and go to (c) Edit properties on the button. Feel free to give the button a proper label, in this tutorial, we'll use "Create Jira ticket". 
   

   
   

2. Configure the button under the 'Actions and navigation' tab by adding a new action and setting it to 'Execute automation' and configure the automation parameter to use the empty automation you created earlier. You can only configure an automation that you own. It is not possible to configure someone else's automation. Tip: you can use the 'View this automation' link to open the automation editor in a new tab.
   

   
   

3. Check the "Include selections" option. This way, on each click, the button will create a temporary bookmark and send its ID to the automation. The temporary bookmark contains the selections that were active when the button was clicked and also the variable state which contains any values the user has supplied for variables through the Variable Input components.
   
   

   

4. Check the "Run mode: triggered" option as well. This will allow everyone who has access to the sheet to run the automation by clicking the button. If you don't select this checkbox, the automation will be executed over an API call, and only users with permission to run the automation will be able to run it through the button. Currently, only the automation owner has this permission.
   
   
   
   

5. Enable the "Show notifications" toggle, this will send a toast notification back to the user in the sheet after the automation completes. Feel free to increase the duration.

6. Add new variables to your app. Do not use load script variables for this; instead, create the variable through the Edit Sheet page. Create the following variables, only give them a name, and you can leave the other fields blank.
   
   1. title_for_jira
   2. comment_for_jira
   3. assignee_for_jira
      
      
7. Add a Variable Input component for each variable to the Container component that already has the Button from earlier. Re-arrange the components in the Container so the Button is last. Set the 'Show as' parameter for the Variable input for the ticket title and comment to the 'Input box'. For the ticket assignee, set it to 'Drop down'.
   
   Note: you'll find the Variable Input under the 'Custom objects' category.
   
   

   

8. To configure the dropdown, you'll need the user IDs of the possible assignees for the Jira ticket. One way of getting those is to use the List Users block in the Jira connector in an automation. Once you have the IDs, configure the dropdown 'Values' parameter by adding alternative values. The Value should be the user's ID and the Label should be the user's name.
   
   

    

9. Your sheet should now look like this:
   
   

   Tip: using a Container component will allow your variable inputs & button to scale better for smaller screens. 
   
   
   
   

2. Configuring the automation

1. Click the 'Copy input block' button in the Button's configuration section. This will copy a preconfigured Inputs block and Apply Bookmark block to your clipboard to get you started with the automation. Next, click the 'View this automation' link to open the automation editor in a new tab.
   

   
   

2. Right-click in the automation canvas and select 'Paste Block(s)'. Connect the new blocks to the Start block. The Apply Bookmark block will ensure that any selections applied by the user, together with the variables state (whatever the user configured for the variables before clicking the button), are available in the automation session.
   
   

   

3. Add a List Current Selections block, and configure it to use the App parameter provided through the Inputs block. This block will return all records the user selected (because they're sent over through the temporary bookmark).
   

   
   

4. Add the Get Expression block for each variable. Configure it to use the App parameter provided through the Inputs block. Use the following format for the expression, =variablename. For example: =title_for_jira. Do the same for the comment and assignee variables.
   
   

   

5. If you haven't already, this is a good point to make a selection, configure the Variable Inputs and click the button from within the app and make sure that each block is returning the correct selections/variable values. Doing this will make it easier to configure the other blocks.
   
   
6. Add a Create Issue block from the Jira connector and configure the following parameters by using the do lookup functionality. Project, Issue Type & Priority. These will be hard-coded for each new issue.
   
   Tip: use a staging or test project in Jira while building the automation.
   
   
7. Map the expression value for the Summary parameter to the output of the Get Expression Value block that's retrieving the ticket title. Do the same for the assignee and set the Comment variable as the ticket's description.
   
   

   
   
   

8. The deal IDs should be added as a comma-separated list to the issue's Description:
   
   1. Click the description input field and select the output from the List Current Selections block. Click the (a) values list and chose the 'Select first item from list' option on the next screen:
      

      
      

      
      
      

   2. Upon automation run, this will resolve to the first text value selected for the field hs_object_id (which corresponds to the deal ID from HubSpot). To update this to a comma-separated list of IDs, the mapping must first be changed to output a list of all values for hs_object_id. To do this, toggle the formula parsing:

      

   3. Change the final 0 in the formula to an asterisk '*', and turn the formula parsing back on.
      
      
      
      
   4. Click the mapping and choose 'Add formula' from the context menu. Then, search for the Implode formula. This formula will turn a list into a comma-separated list. Configure it to use a comma as a separator:
      
      
      
      
      
      
   5. Do a new test run by selecting multiple deals in your sheet and then clicking the button, this should create a new ticket in Jira with the deal IDs as a comma-separated list.
      

      
      
      
      

9. Add an Update Run Title block to the end of the automation. This will show a toast notification to the user who clicked the button after the automation is completed. Learn more about configuring this and who can see the notification in the next part of this article: "Configuring the toast notification".
   

   
   

   The resulting toast notification in the Qlik Sense app:
   
   

   

Bonus: add a link to the toast notification

Instead of showing a plain message in the toast notification, it's also possible to include a link to point the user to a certain resource. This can be done by configuring the Update Run Title block with the following snippet:

{"message":"Ticket created", "url": "https://<link to jira ticket>"}

Configuring the toast notification

Depending on the button's configuration and the automation run mode, use either the Update Run Title block or the Output block to show the toast notification.

See the below table for each option:

The run mode in the button can be configured by toggling the 'Include Selections' option in the button's settings: 

The run mode in the automation can be configured here in the Start block:

Reloads

After writing back to your source systems, you'll want to do a reload to see your changes reflected in the app. Be mindful of the impact of doing these reloads. If multiple people are using this button at the same time, you don't want to do a reload for each update. 

Problems: 

1. Reload concurrency: the same app can not be reloaded in parallel. If multiple people are using the button at the same time, it won't be possible to do reloads for every click on the button.

Improvements:

1. Partial reloads: use partial reloads to only load new data into the app. This way the reload takes less time.
2. Separate reloads: do not use the automation triggered by the button for reloads. Instead, use a separate process for this. For example:
   1. An additional button on the app that triggers reloads.
   2. A scheduled reload.
   3. Another automation with a "List Incremental" block that runs on a schedule and reads changes from the source. It will trigger a reload only if new records are available in the source. Note: not every connector in automations has an incremental block.

Limitations

1. Public access in triggered mode: when the button is set to run the automation in triggered mode, everyone who can access the sheet and click the button will be able to execute the automation.
2. No impersonation: when the automation runs, it will use the automation owner's Qlik account for the Qlik Cloud & Qlik Reporting connectors. It's not possible to impersonate the user who clicked the button. This means that any form of section access is not applied to the user who clicked the button. 
3. No parallel runs: the automation can not run in parallel. New runs get queued with a maximum queue size of 200 runs.
4. Execution token is visible: when the button is set to run the automation in triggered mode, clicking the button exposes the automation's Execution Token from the network traffic in the developer console. This token can only be used to trigger that automation and has no use for other automations or other APIs. Generating a new token can be done by copying the automation.
5. Toast notification timeout: the button will wait on the automation for a maximum of 10 minutes to return the toast notification. If the automation takes longer, it will still run to completion, but no toast notification will be shown.
6. General limits: more information on limitations can be found here: 
   
   1. Button limits
   2. Automation limits

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.

Most Qlik Web Storage Provider Connectors require an encryption key. If no key exists, the following error will be displayed when authenticating:

Error retrieving the URL to authenticate: ENCRYPTION_KEY_MISSING - you must manually set an encryption key before creating new connections.

Resolution

General information, as well as a list of which connectors require an encryption key, can be found in Setting an encryption key | Qlik Connectors Help.

The actual generation of a key depends on your organization's best practices, but we can provide you with an example on how to go about it with OpenSSL:

The below example is not supported by Qlik Support. Review it with your local security office to see what method your organization follows.

1. Download and install OpenSSL.
   See https://slproweb.com/products/Win32OpenSSL.html
2. Open the OpenSSL folder (default C:\Program Files\OpenSSL - Win64\bin).
   
   
   
   
3.  Open the command prompt as an administrator and run these commands:
   1. cd C:\Program Files\OpenSSL -Wind64\bin
   2. openssl rand -base64 256
      
4. Copy and save the successfully generated key. 
   
   
   
   

5. Follow the instructions on how to set the encryption key in Qlik Sense Enterprise on Windows and/or Qlik Sense Desktop:
   
   Setting an encryption key on Qlik Sense Enterprise on Windows 
   Setting an encryption key on Qlik Sense Desktop 

6. Create the data connection. 

Related Content 

Setting an encryption key | Qlik Connectors Help
Setting an encryption key on Qlik Sense Enterprise on Windows 
Setting an encryption key on Qlik Sense Desktop 

Environment

Qlik Sense Enterprise on Windows 
Qlik Sense Desktop