ChatGPT in Qlik Sense

ChatGPT , developed by OpenAI, is the subject of the moment and is already the 4th most used "search engine" in the world, surpassing Bing and Du... Show More

ChatGPT , developed by OpenAI, is the subject of the moment and is already the 4th most used "search engine" in the world, surpassing Bing and DuckDuckGo according to data from the traffic analysis site Similar Web (2023Abr04 - https:// www .similarweb.com/blog/insights/ai-news/chatgpt-bing-duckduckgo ). ChatGPT is a deep learning chatbot that comes with a set of advanced artificial intelligence features to help users solve complex problems. It uses natural language processing techniques to understand users conversations and provide intelligent responses. It can also learn and adjust responses based on user experience. ChatGPT is a great tool for anyone who wants to build custom chat apps.

I was recently asked if Qlik Sense can integrate with ChatGPT. And the answer is YES !

Access to ChatGPT can be done through an API provided by OpenAI. To do so, you need to have an OpenAI account, generate an access token and register a credit card. The use of the ChatGPT API is paid, varying according to the model used, each with different capacities and prices. Prices are per 1000 tokens. You can think of tokens as chunks of words, where 1000 tokens is about 750 words. There is how to configure usage limits. So be careful when using it so you don't have a surprise on your credit card statement.

Using ChatGPT in Qlik Sense SaaS

Let's show a simple example of integrating Qlik Sense in a SaaS environment with ChatGPT , where we built a screen to ask questions to ChatGPT. Initially you need an OpenIA API access token.

Let's create a screen with the following elements:

A text input, to write the desired search;

A Text Box To display the ChatGPT response;

A button to reload the application whenever you do a new search

The screen looked like this:

Next, let's create a load script, with a REST connector, to make requests to ChatGPT.

- Initially declare the variables needed to perform the search:
[TextChatGPT]:

LIB CONNECT TO 'OpenAI';

Let vOpenApiToken='<SEU TOKEN>';
Let vOpenAIUrlApi='https://api.openai.com/v1/completions';

Let vBody='{
 "model": "text-davinci-003",
 "prompt": "$(vPergunta)",
 "max_tokens": 250,
 "temperature": 0.7
}';

Let vBody = replace(vBody,'"', chr(34)&chr(34));
We've created a REST connector called OpenAI. Let's use the text-davinci-003 template (I put ChatGPT3 here since currently few have access to the ChatGPT4 beta). The vOpenApiToken variable should contain your token. The vQuestion variable will be associated with the screen's text Input object.

Then we will make the API request:
RestMasterTable:
SQL SELECT 
	"id",
	"object",
	"created",
	"model",
	"__KEY_root",
	(SELECT 
		"text",
		"index",
		"logprobs",
		"finish_reason",
		"__FK_choices"
	FROM "choices" FK "__FK_choices"),
	(SELECT 
		"prompt_tokens",
		"completion_tokens",
		"total_tokens",
		"__FK_usage"
	FROM "usage" FK "__FK_usage")
FROM JSON (wrap on) "root" PK "__KEY_root"
WITH CONNECTION(URL "$(vOpenAIUrlApi)", 
		BODY "$(vBody)",
 		HTTPHEADER "Authorization" "Bearer $(vOpenApiToken)",
 		HTTPHEADER "Content-Type" "application/json");

So just collect the data in a table, and delete the RestMasterTable:

[choices]:
LOAD	
	[text],
	[index],
	[logprobs],
	[finish_reason]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_choices]);

DROP TABLE RestMasterTable;

And ready. Just load and the answer to your search in ChatGPT will be in the text field.

Going to our screen, just fill in the question text and press the button, wait for the reload and that's it. Your answer will be on the screen!

As an example, I'll ask ChatGPT to " List 5 typical dishes from Alagoas " (for those who know the State of Alagoas in the Northeast of Brazil, you know that we have a rich cuisine).

And the answer was:

1. Carne de sol com macaxeira
2. Moqueca de peixe;
3. Arroz de caranguejo;
4. Baião-de-dois;
5. Cuscuz de tapioca.;

And so the ChatGPT oracle is built into Qlik Sense.

Soon I will publish a second part generating images dynamically in Qlik Sense via ChatGPT.

🖖🏻 See you next time!!!

PS: This article was originally published in Portuguese on linkedin. see in https://www.linkedin.com/pulse/chatgpt-qlik-sense-jean-paul-neumann/

#QlikPartnerAmbassador

#NTechTI

#AnalyticsLab

#Qlik

Show Less

3

4

3040

Qlik Sense Documents

Multiple lines in Pivot and Straight table cells in QlikSense

[Paragraph format. Descriptive text here. See Content Guidelines for details. You can remove sections that are not needed for your article. If subhead... Show More

[Paragraph format. Descriptive text here. See Content Guidelines for details. You can remove sections that are not needed for your article. If subheaders are needed, use heading 3 or heading 4 formats.]

Description:

By default Straight tables only allows maximum of 3 lines in a cell to be visible and Pivot tables are limited to one line only

Resolution

By using a Multi-KPI object we can inject custom CSS onto the sheet to override the default limitations on pivot & straight tables

Step1:

Drag a Multi-KPI object onto the sheet and add a dummy measure example = 1;

Next, on the Properties panel of the Multi-Kpi object navigate to Appearance >> Styles

Add CSS here

Step2:

Paste the custom below

For Straight Tables add the below CSS snippet in the Styles(CSS) box

.qv-st .qv-st-value .qv-st-value-overflow {
max-height: none;
}

For Pivot Tables add the below CSS snippet in the Styles(CSS) box

.qv-pt .cell .value {
max-height: none;
white-space:pre
}

As shown below now each cell will fit to content of the cell based on line breaks in the content

Output Output

Cheers

Vineeth P

Show Less

4

5

1724

Qlik Sense Documents

Qlik Sense SaaS - Automating Apps Export

Developing apps in the Qlik Sense SaaS environment may require us to make a full local copy of apps from a given Development Space , either for ba... Show More

Developing apps in the Qlik Sense SaaS environment may require us to make a full local copy of apps from a given Development Space , either for backup or even version control.

In general, projects developed in the SaaS environment are separated by Shared Spaces and may involve several applications. Making an OnPremise (local) backup of these applications can be an important security practice, as well as we can use it for version control, via git for example, to ensure application version control.

Looking for a solution to automate this operation, we turned to Qlik Sense APIs, which are powerful and complete, and provide many opportunities for integration and automation of activities.

To facilitate operations, we need to find a simple way to use these APIs, preferably through the use of scripts related to the operating system (Windows, Linux or MacOS), which facilitates DevOps activities as well as integration with other complementary tools. So the solution is to use Qlik-CLI .

The Qlik-CLI (command Qlik) is a CLI generated directly from the specifications of the API's Qlik Sense. It allows you to easily do any task in relation to Qlik Sense SaaS, taking advantage of its public APIs.

For more information about Qlik-CLI, see:

https://qlik.dev/tutorials/get-started-with-qlik-cli

QLIK-CLI

To use qlik-cli (by the qlik command ) it is necessary to have it installed and the context configured preferably via an API-key . For download and more information go to qlik-cli .

If we like, for example, to have a list of existing Spaces in Qlik Sense SaaS, we can use the command:

qlik space ls

It will then return a list of existing Spaces and their properties such as name, description, id , in json format.

 [
  {
    "createdAt": "2020-10-21T20:52:51.434Z",
    "createdBy": "Jg43p8kNVPEe6b6lenrG3rMYVfWc4gA9",
    "description": "",
    "id": "5f909fa35746dd0001289a96",
    "meta": {
      "actions": [
        "read",
        "create",
        "update",
        "delete",
        "publish"
      ],
      "assignableRoles": [
        "consumer",
        "contributor",
        "dataconsumer",
        "facilitator",
        "publisher"
      ],
      "roles": []
    },
    "name": "Clientes",
    "ownerId": "Jg43p8kNVPEe6b6lenrG3rMYVfWc4gA9",
    "tenantId": "y5XzebQnCkGBiEv5g-EG3_vMe0ZEJJ9d",
    "type": "managed",
    "updatedAt": "2020-10-21T21:35:01.104Z"
  }
]

To then handle the responses in json, we use jq . The JQ is a JSON processor light and flexible. JQ is written in portable C, and has zero runtime dependencies. You can download a single binary. For download and more information go to jq

To then list the Spaces and collect just the Name and Type, we can do:

qlik space ls|jq -r ".[]|.name + \" - \" + .type"

The result will then be something like:

Clientes - managed
Demo - shared
NTech - managed
Comercial NTech - shared
Demo01 - managed
DemoGov - shared
Comercio Data Drive NE - managed
CDDNE_QVDS - data
CDDNE_SOURCES - data

So we developed QlikSaaSExportSpaceApps , which is a script for windows environment (the same principles can be used in other scripting languages for Mac or Linux platforms. QlikSaaSExportSpaceApps allows to export all the apps from a given Space, to your local machine. Its syntax is:

QlikSaaSExportSpaceApps.bat [SpaceName] [--nodata] [--help]

Where:

Space Name - Name of the Space where the apps for export reside. Not informed will use default script 
--nodata - perform app export without data 
--help - Help screen

The export process via QlikSaaSExportSpaceApps is as follows:

If you like to export the applications without the data loaded (focusing on the application and reducing occupied space), you can use the --nodata parameter .

The script is available for anyone who wants to use or learn about GitHub:

https://github.com/jptneumann/QlikSaaSExportSpaceApps

active intelligence

The process described above has to do with the concept of Active Intelligence from Qlik. It provides a state of continuous intelligence from up-to-date, real-time information to take immediate action, enabling Automation processes to respond more quickly to changing business requirements, eliminating manual resource-intensive tasks of creating a pipeline. End-to-end analytics: Moving data continuously, creating analytics-ready frameworks like warehouses and cloud lakes, enhanced analytics to drive new insights, API integration and applications to act immediately.

That's it for today, See you next time!!!

Show Less

7

1

1970

Qlik Sense Documents

Opening the same Apps in multiple tabs with different selections

Have you ever noticed, when you have the same app open twice and make selections, those selections are reflected in all your open tabs? This is becaus... Show More

Have you ever noticed, when you have the same app open twice and make selections, those selections are reflected in all your open tabs? This is because they are all open using the same session/identity.

This has been one of the most requested features by our users as they like to compare things side by side, without changing the selections every time, or having to build charts with alternate states.

Luckily, there is a way to bypass this using the Qlik Single integration API.

The Single Integration API provides parameters that can be used to create an URL that returns a complete HTML page containing for example an embedded Qlik Sense visualization. This URL can be embedded in a web page, for example by including it in an iframe.

Here's how :

Open your desired app multiple times
In the first tab, add this parameter at the end of the URL: /identity/1 (The 1 is alphanumerical and can be longer than 1)
In the second tab, add this parameter /identity/2
Repeat this step for every other tab you have opened

Voilà! You are now able to make different selections for the same app!

Show Less

2

8

1908

Qlik Sense Documents

Acquired Amount within overlapping dates

Insurance is one of the most complicated sectors in data analytics, developers struggle when it's required to calculated the acquired amount within... Show More

Insurance is one of the most complicated sectors in data analytics, developers struggle when it's required to calculated the acquired amount within the year, and especially those which overlap, here is an example to solve such kind of issues with Load Script:

The picture below shows some subscribed amount in different periods, but the customer needs the acquired amount within the year, if we take policy N° 004 as an example, we notice that we have a 7000,00 $ for a period of three years (2021 --> 2023), but how to proceed if the customer has to analyse year by year ? in my opinion it's usefull to create as many lines as we have in the covered period (EndYear-StartYear), i have used some dummy data to demonstrate the solution discussed above:

then i try to load the whole row as one field in order to load variables separately:

it's time to call our generator, for each row we should create as many line as we have in the whole covered period:

finally here's the generator of the result table:

here is the output:

expressions used:

DiffDays: Round((EffectiveEndDate-EffectiveStartDate)+1)

DaysOfYear: Round(YearEnd(EffectiveEndDate)-YearStart(EffectiveStartDate))

CoveredPeriod %: Round((EffectiveEndDate-EffectiveStartDate)+1)/Round(YearEnd(EffectiveEndDate)-YearStart(EffectiveStartDate))

AcquiredAmount: Sum((Amount*((EffectiveEndDate-EffectiveStartDate)+1))/((EndDate-StartDate)+1))

Note: this solution can impact performance when used with huge fact, i remain open for any suggestion or alternative.

Best ragards:

JM;

Show Less

1

2

360

Qlik Sense Documents

Introducing the Sense Field Formatter

Introducing Sense Field Formatter Version 1.7 - A new feature for the front end! With the latest version, you can now quickly copy the output in expre... Show More

Introducing Sense Field Formatter

Version 1.7 - A new feature for the front end!

With the latest version, you can now quickly copy the output in expression editor windows using a button.

Check it out on YouTube : https://www.youtube.com/watch?v=CXPMbQSD9mQ

It is very likely that many of you build your data models so that you can easily reuse certain parts, such as dimensions.

Although some development methods such as the Qlik Deployment Framework (QDF) help speed things up, the task of renaming fields when reusing a QVD is still time-consuming.

Let's imagine that a fact table contains 3 customer dimensions and 2 warehouse dimensions and that for each of these dimensions you add a prefix, or that your fields are written in a multilingual way ([Customer/Customer/Cliente]) and you want to get the middle value. You could also use it to rename fields from an SQL database and format everything for a nice front-end result in Qlik.

It's now easier than ever with Sense Field Formatter.

What it does

Sense Field Formatter 1.png

Sense Field Formatter 2.png

(https://youtu.be/k3HQpJivyZM)

Allows a user to format a list of fields, delimited by commas, into an alias and standardized format.

The user has multiple configuration options to choose from.

When the extension icon is clicked, a popup menu is displayed where the formatting takes place and is pasted into the clipboard.

User Settings

Use Alias as the field's source name

Will use the alias name (at the right of "AS") from your source fields as your new source fields.

e.g. [SourceField] AS [SourceAlias] -> [SourceAlias] AS [OutputAlias]

Key Prefix

The character(s) used to identify the key fields in Qlik. Formatting will be handled differently here as the Prefixing will be done AFTER (the right) the key prefix to avoid breaking the key formatting.

e.g. Prefixing a key field with "Customer." will generate this: [%MyKeyField] AS [%Customer.MyKeyField]

Ignore formatting on Key Fields?

Fields starting with the identified key will NOT be formatted.

Comma Position

Places the comma either at the start or end of the line.

Field Delimiter

Should the field delimiters be double quotes or square brackets.

Replace characters

Find what: The character(s) you are looking to replace. Multiple characters can be replaced at once.

Replace with: Replace all occurrences of the character(s) in the fields.

Insert a space before capitals

Will insert a space before each capital letter in the fields, except for the first character.

e.g. [MyNewField] -> [My New Field]

Change Case

Applies a case change to all the fields.

Uppercase: Replaces each lowercase letter with a corresponding uppercase char.

Lowercase: Replaces each uppercase letter with a corresponding lowercase char.

Capitalize: The first letter of each word will be in uppercase.

Subfield the field names?

Extract substring components from a parent string field, where the original record fields consist of two or more parts separated by a delimiter.

The function can be used, for example, to extract the column name in a specific language if the field is multilingual.

*e.g. Applying a subfield of 3 with a delimiter of '/' [Client/Cliente/Customer] -> [Customer]

Delimiter: A character within the input text that divides the string into component parts.

Field no: Specifies which of the substrings of the parent string text is to be returned. Use the value 1 to return the first substring, 2 to return the second substring, and so on.

Add Suffix/Prefix?

Adds an affix, either placed before the field (prefix) or at the end (suffix). Useful when reusing the same dimension over and over for multiple scenarios.

e.g. Using the customer dimension where a customer may be a Ship To or Bill To customer.

Prefix : [Customer Name] -> [ShipTo.Customer Name]

Suffix : [Customer Name] -> [Customer Name.ShipTo]

Affix: The text that will be used as an affix. Make sure to add a space to your affix if you'd like the new string to remain spaced out.

Installing the plugin

Visit the Store:

Firefox

Chomre

Microsoft

Visit my GitHub: https://github.com/MichelLalancette/SenseFieldFormatter

Show Less

1

4

793

Qlik Sense Documents

Security access to Community objects

Hello, I'm trying to limit the access and visibility of community objects on a QLIK SENSE application. Users all have access to the application, bu... Show More

Hello,

I'm trying to limit the access and visibility of community objects on a QLIK SENSE application.
Users all have access to the application, but I would like access to objects published by the community to be limited only to objects created and published by members of the same group.
I have the notion of group which is brought back from my UserDirectory but can't find how to limit the right to read objects.

I have test with Security Rules but there are the default rules in read only who blocked. 

I have a QLIK SENSE Enterprise in Windows November 2021 Patch 3.

Thanks in advance.

Regards,
Bruno

Show Less

0

115

Qlik Sense Documents

Qlik Explorer for Developers

Qlik Explorer for Developers is a light weight tool designed to provide a simple way for developers to examine the contents of a Qlik Sense server. Ty... Show More

Qlik Explorer for Developers is a light weight tool designed to provide a simple way for developers to examine the contents of a Qlik Sense server. Typical use case include examining the properties or layout of a particular object, or to simply retrieve the ID of that object for use in an API based solution. The tool is attached as a zip-file to this post and requires no installation.

The tool provides a number of "templates" that are used to choose what type of information to display for the different nodes. Some examples are "id" which simply prints the ID of the node:

The ID template provides convenient access to the ID of objects.

Another commonly used template is "json" which allows you to navigate to particular sections of the json structure of properties and layouts:

The json template makes it possible to navigate properties and layout definitions.

Qlik Explorer for Developers also exposes the new progress information provided by the engine for app open operations. The feature requires Qlik Sense Enterprise for Windows release May 2022 or later, and provides a breakdown of the time spent by the engine in different steps of the app open flow. This information can be very useful for instance when investigating app open performance issues:

The info template exposes app open progress details.Please use the Community forum "Integration, Extension & APIs" for any questions relating to this tool:

https://community.qlik.com/t5/Integration-Extension-APIs/bd-p/qlik-sense-integration-extension-api

Release notes:

## v2.3.2
* **BUG FIX:** Fixed issue preventing connections using header authentication.

## v2.3.1
* **BUG FIX:** Fixed issue with collecting streams when connecting to client managed through proxy.
* **BUG FIX:** Fixed issue when accessing app using section access for which you are not assigned access privileges.

## v2.3.0
* **NEW FEATURE:** Added options for connecting using certificates and header authentication.
* **NEW FEATURE:** It is now possible to delete connections from the connection history in the settings dialog.
* **NEW FEATURE:** Added experimental option to connect to client session.
* **BUG FIX:** Fixed problem listing apps when there are no streams or spaces.

## v2.2.1
* **NEW FEATURE:** Replaced app filter with functionality for choosing to filter on either title or ID.
* **NEW FEATURE:** Added button for clearing filter.
* **NEW FEATURE:** Added dialog for configuring settings.
* **IMPROVEMENT:** Nodes that are considered matched by filter are now highlighted in the tree.
* **IMPROVEMENT:** Added option to revert to default settings if settings file has been corrupted.
* **BUG FIX:** An app without a space ID would previously cause an exception to be throw and force a disconnect from QCS.
* **BUG FIX:** Fixed issue with listing apps in QCS spaces.

## v2.1.2
* **NEW FEATURE:** App open progress is now loaded dynamically while the open process is running.
* **NEW FEATURE:** The info template can now be used to display information about field and dimension nodes.
* **NEW FEATURE:** Added option to store key used for connection mode Api Key to QCS.
* **NEW FEATURE:** Added new connection type JWT to QCS.
* **IMPROVEMENT:** The connection type "API key to QCS" is now correctly denoted `ApiKeyToQcs` instead of `JwtToQcs`.
* **IMPROVEMENT:** Improved connection error messages.
* **COSMETIC:** Implemented use of black circle as password character.
* **DISABLED:** The preview tab has been disabled as IE11 is no longer supported by Qlik Sense.
* **BUG FIX:** Fixed tool tip for input field for connection type "Api key to QCS" and "JWT Key".

## v2.0.2
* **NEW FEATURE:** It is now possible to filter the set of apps that is displayed in the tree view.
* **NEW FEATURE:** It is now possible to display the ID of nodes in the tree view along with the title of the node.
* **NEW FEATURE:** The new info template makes it possible to view a detailed breakdown of how long each step of the app open process takes.
* **IMPROVEMENT:** The tab panel is now position next to the tree view instead of below.

Show Less

0

5

2020

Qlik Sense Documents

Change the theme in application

I have always wondered how to change the theme in application with native qlik sense extensions. I am aware that there are extensions on the market fo... Show More

I have always wondered how to change the theme in application with native qlik sense extensions.
I am aware that there are extensions on the market for the same purpose but the challenge was how to do it with the objects in the application itself?

My solution is to chenge background layer but this can be apply on theme too, you just need to add your css for theme in multy kpi object :

1. Use Multy KPI extension and add some css. I add 9 multy kpi object with different css for background and add to master items.

2. Create variable vBackgroundLayer and for definition use number 1 or any other number what will be default background layer in app.

3. Add Container and insert all 9 multy kpi objects and add condition
=if(vBackgroundLayer=1,1,0)
=if(vBackgroundLayer=2,1,0)
=if(vBackgroundLayer=3,1,0)
.............................
.............................
=if(vBackgroundLayer=9,1,0)

4. Using 9 buttons we can change the background lyers by color and position
Button settings:
Action: Set Variable value
Variable: vBackgroundLayer
Value: 1

Action: Set Variable value
Variable: vBackgroundLayer
Value: 2

Action: Set Variable value
Variable: vBackgroundLayer
Value: 3

.........................
.........................

Action: Set Variable value
Variable: vBackgroundLayer
Value: 9

5. I don't need any more container to be visible for that purpose i hide the container adding css in my multy KPIs objects

[tid="vkbDYYG"].qs-container
{
display:none!important;

}

Show Less

0

490

Qlik Sense Documents

How to create MTD,QTD ,YTD flags using as of calendar

Hi all, I have prepared a script that will calculate the MTD, YTD, QTD, WTD without using complex set analysis using the As-Of table concept. One mo... Show More

Hi all,

I have prepared a script that will calculate the MTD, YTD, QTD, WTD without using complex set analysis using the As-Of table concept.

One more App is attached : Which can create MTD/QTD/MTD in filter & we can use this in set analysis to display values like MTD/QTD/MTD.

Thanks

Vikas

Show Less

1

1307

Qlik Sense Documents

link table

Link Table : Please find simple link table concept implemented with multiple fact tables. Hope definitely help new comers will help to clear concept o... Show More

Link Table :

Please find simple link table concept implemented with multiple fact tables. Hope definitely help new comers will help to clear concept of link tables.

Data Modelling is technique in qlik for linking tables we can use link tables concept please refer ppt .

Qlik Tips: Rules for creating a Key/Link Table in QlikView and this post

Re: Advantages of Link Table;

ref: https://www.analyticsvidhya.com/blog/2014/12/remove-synthetic-key-concatenation-link-table-qlikview/

Please find attached excel & data model containing how to implement a link table.

Attached Solution Linktable2

Hope this help to new comers.

Thanks

Vikas

Show Less

0

3

9304

Qlik Sense Documents

Complete Guide to Custom Maps (+ Custom Area Shapes) in QlikSense

If you search after custom area or custom maps in Qlik you will find after a while some articles on how to use custom maps or how to visualize them. ... Show More

If you search after custom area or custom maps in Qlik you will find after a while some articles on how to use custom maps or how to visualize them. But most of them are lacking information about how to map points or even shapes into the datamodel.

So I wanted to fully understand how to build a custom map with custom points and shapes! And after some hours of research, creative thinking and a lot of trial and error, here is the Complete Guide to Custom Maps in Qlik Sense.

So we frist start with a custom picture, for example the floor plan of my apartment*

*not really my apartment

When you take a further look into the other articles you will always find some kind of existing mapping table for mapping points on the picture to coordinates in the map.

But How did they come to this mapping table?
1. Print out the image on your companies printer and then take your ruler from high school and measure the... No!
I am a lazy kind of person and don't want to leave my Qlik environment. So my picture has an aspect ratio of 5x3.5. Now I have to decide how small I want my slices. I decided to make 10 slices each so my grid should be 50 x 35.

Let's put these two magical numbers and a random name (e.g. 'Floorplan') for my grid into the following sub routine:

SUB CREATEMAPGRID(SUB_VAR_NAME,SUB_VAR_X, SUB_VAR_Y)

	[temp_]:
	Load 
	Iterno()-1 As [$(SUB_VAR_NAME).X]
	AutoGenerate 1 While IterNo() -1 <= $(SUB_VAR_X);  

	Join([temp_])
	Load
	Iterno()-1 AS [$(SUB_VAR_NAME).Y]
	AutoGenerate 1 While IterNo() -1 <= $(SUB_VAR_Y); 

	[$(SUB_VAR_NAME)_grid]:
	Load *,
		'('&[$(SUB_VAR_NAME).X]&'|'&[$(SUB_VAR_NAME).Y]&')'	as [$(SUB_VAR_NAME).Point]
	resident [temp_];

	drop tables [temp_];

END SUB;

Call CREATEMAPGRID('Floorplan',50,35);

Next: Upload your picture for example via the QMC (content libraries) and save the URL path (/content/Default/unnamed.jpg) for later.
Create a new sheet and drop in the Map-Chart. Add a new background layer:
Switch format option to "image", copy the URL path of the image in to the URL input field.
Now fill in the image position options:
top (width): 0
left (height): 0
bottom (width): 35
right (height): 50

Go to map settings and chose 'none' as your base map, deactivate automatic zoom.
Navigate to your apartment on the map and define the standard view.
Add a new point layer and choose 'Floorplan.Point' as your dimension, switch to location and check "Fields for Long/Lat". For the Latitude chose 'Floorplan.Y' and for Longitude chose 'Floorplan.X'
Go to "size and shape" and change shape to square and move the size slider to the left.
Congratulations we have now a grid map on our background image (see image above).

Thanks to our 'Floorplan.Point' field we can now read the exact location when we hover over a grid point.
We can now define our sweet spots by hovering over the desired grid point and typing those coordinates into a mapping table - either in Excel or just use an inline load, so you don't need to leave Qlik.

SweetSpots:
Load * Inline
[
Spot,		X,Y,	maxPerson		
table,		11,27, 	8	
kitchen,	31,25,	4
living room,11,10,	6	
stairs,		21,17, 	1	
];

As you can see I already added my measure of interest (analyzing bottle necks before starting my birthday party) to the sweet spots. In a real world scenario you could just link the sweat spots to a big fact table using Qlik's associative logic. I can now delete (or hide) the point layer with the grid information and replace it with a point layer for my SweetSpots. May use the measure =1/Sum(maxPerson) for the size of the bubbles to indicate the 'bottle necks' visually.

And that's it!?
Of course not. Now we come to the cool part: Adding custom areas (custom shapes)!
I hope you haven't deleted your Grid-Layer yet - if so please repeat the steps above to restore the grid.
To create your own areas or shapes we need some more information than X and Y.
We need to know which coordinates belong togehter, and the most important information: the order of the coords to define how they should be connected.
So I created a new inline table with all the information. Please pay attention that I enumerate the shapes.
This will be beneficial if we want to combine 2 shapes to a single entity (which we will do later).

RoomArea:
load *,
	//you have to switch X and Y using GeoMakePoint, don't ask why. i may answer in the comments
	GeoMakePoint(Y,X)	as geopoint,
;
Load * Inline
[
shape,AreaName,		X, Y,	pathorder
1,	technic room,	35,22,	1
1,	technic room,	44,22,	2			
1,	technic room,	44,31,	3			
1,	technic room,	35,31,	4			
2,	wc,				35,9,	1			
2,	wc,				33,5,	2			
2,	wc,				35,5,	3			
2,	wc,				44,5,	4			
2,	wc,				44,9,	5			
3,	kitchen,		24,31,	1			
3,	kitchen,		24,28, 	2			
3,	kitchen,		32,28, 	3			
3,	kitchen,		32,22, 	4			
3,	kitchen,		35,22, 	5			
3,	kitchen,		35,31,	6			
4,	dining room,	7,23,	1			
4,	dining room,	18,31,	2			
4,	dining room,	19,19,	3			
5,	kitchen,		25,26,	7			
5,	kitchen,		29,26, 	8			
5,	kitchen,		29,21, 	9		
5,	kitchen,		25,21, 	10
];

The next step is only needed, if you have multi-shaped areas, like 'kitchen' in this example.

//Helper to idnetifiy if feature consists of multiple shapes
idCount_MAP:
mapping load 
	AreaName,
    count(DISTINCT shape)
resident RoomArea
group by AreaName
;

Now we want to create or custom shapes. Most articles say you need .kml files and this is almost true.
But you can also fake an .kml file by immitate its output and tag the field correctly.
A .kml files will deliver a definition of an shape by giving you the coords in correct order.
in such a way that if you take a pen and follow each coord you will draw the outline.
.kml will output them in this kind of format: [[[53.452452,12.745464][53.322345],[12.543345],[53.37644,12.11224]]]
so each shape is encapsuled in [,] so building sub-shapes is done by: [[[a,b],[b,c],[c,d]],[[c,d],[d,e],[e,f]]]. With this in mind we are ready to fake our own kml shapes.
So in the frist steps lets create areas that only consists of one shape.

//First only Features with single shapes
RoomAreaMap:
Load
AreaName, 
//construct the path that makes the area; specify the order of the points along the path
// you need a pair of '[' and ']' for each level of possible sub-areas. in most cases 3 pairs holds.
'[[['&concat(geopoint,',',pathorder)&']]]' as area
Resident RoomArea
where Applymap('idCount_MAP',AreaName,0) = 1
group by AreaName
;
//IMPORTANT works only when this tag is applied to the area
Tag field area with $geomultipolygon;

With the Tag field statement we can define the field 'area' (which is in string format) to be interpreted as if it was loaded from an .kml file (= polygon definition).
Got to your Map-Chart and add a new area layer, choose 'AreaName' as dimension and 'area' as location field. Define color by dimension and change opacity to 50%.
Congrats, your first shapes are on the map! 🎉

Let's go to the last and most fun part: getting areas wih combined shapes into the custom map.
Here we use a stacked load to first create the single areas or sub-areas for each 'AreaName' - differentiated by the field 'shape'. With the stacked load we can now combine those sub-areas:

//Features with multiple shapes
Load
	AreaName,
    //2. concat the shapes for each area
    '[['& concat(sub_area,',',shape)&']]'	as area
group by AreaName;
Load
    AreaName, 
    shape,
    //1. construt path for each area per shape
    '['&concat(geopoint,',',pathorder)&']' as sub_area
Resident RoomArea
where Applymap('idCount_MAP',AreaName,0) > 1
group by AreaName,shape
;
//IMPORTANT works only when this tag is applied to the area
Tag field area with $geomultipolygon;

If we now came back to our Map-Chart, we see the updated area layer, with our 2 shaped kitchen area (the yellow one in the picture) appearing on the screen. Yay! 🤓

final result:

Some real world apllication hints: use association to attach several measures for each feature (RoomArea, Spot).
for example to monitor how long your colleagues are sitting in front of their screen by tracking desktop activity (admin needed). Often used are traffic light indicators (red, green, yellow) on spots: use the coloring expression to make changes of states visible. in combination with sensor-data in different rooms this Feautre allows much more use-cases, but this is a whole different story.

I hope you enjoyed the post and can get use of it.
best regards Chris!

Show Less

0

4

2645

Qlik Sense Documents

Qlik Sense App: iColor - Color variables generated from excel + tips&tricks

I client recently requested me to align the colors used in all their apps' objects to their corporate colorsTo avoid having to edit each app again in ... Show More

I client recently requested me to align the colors used in all their apps' objects to their corporate colors

To avoid having to edit each app again in the future if the color requirements change, I decided to create a method to populate the color variables from excel, thus next time I simply have to update the color codes in the excel file

To scale this approach out to other future implementations, I have created the attached .qvf file and excel files.

The app also includes examples of how to incorporate colors using certain functions / methods:

- ColorMix()

- Data Brushing

- Pick()

- Pick(Match())

I also included my list of useful color related websites to help you with color selection and design

Hope this will be useful to you 🙂

Show Less

0

1

462

Qlik Sense Documents

Qlik Sense App: Generic Data Profiler

This app has a number of objects that work together to allow you to see the frequency of values on any field in your own apps. The field is selected ... Show More

This app has a number of objects that work together to allow you to see the frequency of values on any field in your own apps. The field is selected from a Filter Pane and then a Bar Chart object shows the frequency of values within the selected field. There is also a Filter Pane on the selected field, allowing searching on that field.

The objects can be copied and pasted into any Sense document, allowing this functionality on any data set.

A full tutorial on how to build the components in this app, using only standard Sense features, is given in a blog post which you can find here:

https://www.quickintelligence.co.uk/qlik-sense-data-profiler/

This app is a Sense port of our popular QlikView app, which can be found here:

QlikView App - Generic Data Profiler

I hope that you find the application useful. You will find other applications that I have uploaded under my profile on QlikCommunity or on our Downloads page..

Steve

http://www.quickintelligence.co.uk/qlikview-blog/

Show Less

16

9

11594

Qlik Sense Documents

Differentiating Prod and Dev Environments (Changing the QMC and HUB header color...

Hi Qlikers,Differently from QlikView, Qlik Sense applications are developed on the browser which means that identifying applications based on the inte... Show More

Hi Qlikers,

Differently from QlikView, Qlik Sense applications are developed on the browser which means that identifying applications based on the interface becomes tricky. The configurations documented here were done to provide clear differentiation between Prod and Dev environments and do not affect the application, they have effect only on the environment. So, the application can be moved from Dev to Prod without any particular change on it.

Best regards,

Huberto Pereira Haidemann

Show Less

0

3

908

Qlik Sense Documents

Variabilizing your Connections

Connecting to data sources is easy right? You simply use data connection manager, or choose a table from an existing connection right. The "wizards" i... Show More

Connecting to data sources is easy right? You simply use data connection manager, or choose a table from an existing connection right. The "wizards" insert the Connection string into the Load Script and you are off to the races for your queries.

LIB CONNECT TO 'Snowflake_qlik.us-east-1.snowflakecomputing.com (qlikpe_qlikservice)';

Then some yahoo says "Hey Qlik Dork the customer actually has 2 server instances and they needed the data loaded from both." Now you could simply duplicate the section of code and do the same thing for the second instance. It's not that hard after all. A cut here, a paste there, a CONCATENATE before a LOAD there and bingo ... 2 different server instances.

But your Spidey senses start tingling because you know what it really means is "The next customer might have 4, and the next might have 7." And since they will actually be the ones changing the code, you don't know for sure how proficient they are with their cut, paste, concatenate or bingo skills. But what can you possibly do, the connections are hard coded? Right?

Wrong. The compiler needs a connection string but the reality is it doesn't care how it gets it. Which means ... you can use a Variable for it.

Set vInstance = 'Snowflake_qlik.us-east-1.snowflakecomputing.com (qlikpe_qlikservice)';

LIB CONNECT TO '$(vInstance )';

I hear you out there shouting "But that doesn't achieve anything other than making the code more complicated!" But you didn't let me finish. Of course I'm not going to do my Set vDesc on a line by itself, I'm going to LOOP through the connections and set the variable vDesc inside the loop like this.

SnowflakeInstances:
Load * Inline [
SFConx, SFType, SFInstance
SF_AT, NotLive, 'Snowflake_attunity_partner.xxxxx.snowflakecomputing.com '
SF_PE, Live, 'Snowflake_qlik.xxxxx.snowflakecomputing.com'
];

Let vNumRows = NoOfRows('SnowflakeInstances')-1;
For i = 0 to $(vNumRows)

Let vConx = peek ('SFConx',$(i),'SnowflakeInstances');
Let vInstance = peek ('SFInstance',$(i),'SnowflakeInstances');
Let vType = peek ('SFType', $(i), 'SnowflakeInstances');

LIB CONNECT TO '$(vInstance )';

Yeah the customer with 2 different Snowflake instances is covered.

Yeah the customer with 3 different Snowflake instances is covered.

Yeah the customer with ... you get the picture. All customers is covered. 1 to N instances is all the same to you.

Let's say I am reading a Table called SESSIONS. My table header takes advantage of that vConx variable:

[$(vConx)_SESSIONS]:

And when I want to Store the data into a QVD so I can handle incremental loads, that variable is handy again:

Store [$(vConx)_SESSIONS] into [$(vQVDLocation)/$(vConx)_SESSIONS.qvd] (qvd);

What in the world is the vType variable used for? Does Live or NotLive even make sense?

It does in my particular use case. I have some very large instances that I need to read from for "my" testing, that I only have limited access to. Thus I need to connect Live one time, but I want to read the data from my QVD file when it's flagged as NotLive the rest of the time. A simple little IF THEN ELSE and I'm all set. I either choose to read the data from the server or simply read it from the QVD with no incremental loading.

IF (vType = 'Live') Then

If you were observant you will have noticed that I used a variable for the storage location as well as the QVD filename. Because you know the customers that receive this application will all create different Connection paths for the QVD files so I simply let them define it in the beginning of the code rather than requiring them to change it in every single section of the load script. Plus I'm cooking with gas when I deploy to SaaS and my storage is a different space and not just a file location.

I like to variabilize as much of my code as possible. It's either because I'm lazy -OR- because I'm dedicated to easily modifiable code -OR- both. 😀

Show Less

0

366

Qlik Sense Documents

How to improve the lookup accuracy

Mapping Springfields or how to improve the lookup accuracy The on-the-fly location lookup is one of my favorite features of Qlik GeoAnalytics and the ... Show More

Mapping Springfields or how to improve the lookup accuracy

The on-the-fly location lookup is one of my favorite features of Qlik GeoAnalytics and the native map in Qlik Sense. Just add a field and the map automagically places the assets using the field content. This article explains how on-the fly lookup works and how you as user can improve the hit rate.

How it works

The on-the-fly lookup in Qlik is powered by a vast database populated with location entries. It holds currently approximately 7 million features of different types: countries, regions, municipalities, populated places, airport codes, zip codes etc.

Point density of the Qlik Location Database

In most cases each place has several aliases for the same location in local language or just a different spelling. When the database is queried it responds back with a geometry. Most of entries are points but the database also contains many area geometries for well-known regions. The database also keeps a hierarchy of the entries, a place belongs to country and a region, in order to distinguish places from each other.

How to improve

I will use 'Springfield' as an example to show how to get better matches. Springfield is one of the most common place name in the US. I pulled a list of “Springfields” from USGS, an organization that keeps records of all populated places in the US. The list included city name, state and county information. I loaded the list in to Qlik Sense as an inline table, at the bottom of the page you can find a link to my test app.

Just Springfield = 1 hit

My first attempt is just to make a point layer and add the city field. With that approach I will get one hit for a Springfield in Illinois, US.

That's not so surprising, the lookup service made a best effort given information and picked the largest city with that name. Also, the city field only have one distinct value as the dimension controls the number of objects on the map.

Add id dimension, country and location type

As a first improvement I switch to an id dimension that holds a unique id for each city to get a fair chance to placing more cities. The dimension is also used for selections, so I prefer an id that is easy to read. For this app id is the string with the city, county, state, country.

Looking location tab for the point layer I can see that can fill in more context for location. I start by adding my field ‘country’ and set the location type to ‘City, place’.

That helped, now I get more hits, one in US and one in Virgin Islands, because Virgin Islands has it’s own country code.

Adding state information

City and country are not enough in this case to pin point the locations, so I continue by providing the field ‘state’ to the location tab. Apparently it is common to have several cities named Springfield in the same state.

The hit rate increases, with city and state I get 30 hits, there are 29 states in the US with one or more cities called Springfield.

Adding county information

To get even more hits I need to provide more context, luckily for me the list of cities also contained information about county, the 2nd level of administrative boundaries in the US. I add that field also in the location tab.After that more places were found, now 66 cities of the 67 were properly located.

The edit mode of Sense lets the map provide error messages for the location service. In this case only one city was not located, apparently Clayton county in Georgia has two places called Springfield.

Advanced usage, using a location string

As an alternative, the user can put everything (location name and type information) in the location string instead of using the drop-down menus:

=city & if(len(county)>0, ',' & county) & if(len(state)>0, ',' & state) & if(len(country)>0, ',' & country) & ':P*'

This produces the same result but is gives more control and might be more convenient for the advanced user. The ':P*' is the short hand code for a 'City, Place'. Read more about location types in the "Location Service Description" document in QGA documentation.

Testing city names at load time

Qlik GeoAnalytics provides an operation "NamedPointLookup" that can check how good the matching will be. This is convenient especially for larger address databases. In this example city names were processed. We got 1 hit on city level, 2 hits on country level, 30 hits on state level and 66 on county level. Check the load script for details on how to perform the NamedPointLookup.

Knowing which name to use

Most entries in the Qlik location database has several aliases, same place different spelling. The best way to find out the main name is use the Qlik GeoAnalytics connector and the "Load" operation. Here's an example to find out all the correct county names in the US:

Summary

Basically, the lookup becomes better with more context such location type, location spelling, location hierarchy. City names are often ambiguous, to resolve a common city name like "Springfield" info about the type, country, state and county are needed.

Check out the test app below:

Show Less

0

5353

Qlik Sense Documents

Qlik Sense Documents

Recent Documents

Description:

Resolution

QLIK-CLI

Introducing Sense Field Formatter

What it does

User Settings

Use Alias as the field's source name

Key Prefix

Comma Position

Field Delimiter

Replace characters

Insert a space before capitals

Change Case

Subfield the field names?

Add Suffix/Prefix?

Release notes: