Watch how I am serving our #QlikNation Community members, by not only analyzing their video suggestions BUT responding to them immediately from within this Qlik Cloud Analytics App. #mikedrop
We all know and love using Insight Advisor right within the Qlik Sense hub or inside Analytics apps, helping us analyze data, create visualizations or build data models.
In this post, we will tap into the Insight Advisor API to leverage its power within a separate web application.
We will create a simple web app that allows to ask natural language questions against our Qlik Sense app and get a recommended visualization as a response that we will then render using nebula.js
Pre-requisites:You will need to grab the following before starting:
Qlik Cloud tenant URL
Web Integration ID (you can get this from the Management console under Web, make sure to whitelist our localhost’s origin: http://localhost:1234)
App Id
InstallationRun npm install to install the content of package.json
Folder structure:
src
index.html (UI)
index.js (main file)
and cloud.engine.js (enigma.js library for engine session handling)
The following sections discuss the main parts of building the web app and calling the API, they are not in any particular order. I will provide the complete code for the project at the end of the post so you can see where everything fits.
1. Connecting to Qlik Cloud
First things first, we need to handle the authentication to Qlik Cloud.
Interactive login process:
async function getQCSHeaders() {
await qlikLogin(); // enforce tenant login
const response = await fetch(`${tenantUrl}/api/v1/csrf-token`, {
mode: 'cors',
credentials: 'include',
headers: {
'qlik-web-integration-id': webIntegrationId,
},
});
const csrfToken = new Map(response.headers).get('qlik-csrf-token');
return {
'qlik-web-integration-id': webIntegrationId,
'qlik-csrf-token': csrfToken,
};
}
async function qlikLogin() {
const loggedIn = await fetch(`${tenantUrl}/api/v1/users/me`, {
mode: 'cors',
credentials: 'include',
headers: {
'qlik-web-integration-id': webIntegrationId,
},
});
if (loggedIn.status !== 200) {
if (sessionStorage.getItem('tryQlikAuth') === null) {
sessionStorage.setItem('tryQlikAuth', 1);
window.location = `${tenantUrl}/login?qlik-web-integration-id=${webIntegrationId}&returnto=${location.href}`;
return await new Promise((resolve) => setTimeout(resolve, 10000)); // prevents further code execution
} else {
sessionStorage.removeItem('tryQlikAuth');
const message = 'Third-party cookies are not enabled in your browser settings and/or browser mode.';
alert(message);
throw new Error(message);
}
}
sessionStorage.removeItem('tryQlikAuth');
console.log('Logged in!');
return true;
}
2.Communicating with the Qlik Cloud Engine
(content of the cloud.engine.js file)
We need to open a session using enigma.js to communicate with the Qlik QIX engine.
import enigma from "enigma.js";
const schema = require("enigma.js/schemas/12.1306.0.json");
export default class EngineService {
constructor(engineUri) {
this.engineUri = engineUri;
}
openEngineSession(headers) {
const params = Object.keys(headers)
.map((key) => `${key}=${headers[key]}`)
.join("&");
const session = enigma.create({
schema,
url: `${this.engineUri}?${params}`,
});
session.on("traffic:sent", (data) => console.log("sent:", data));
session.on("traffic:received", (data) => console.log("received:", data));
return session;
}
async closeEngineSession(session) {
if (session) {
await session.close();
console.log("session closed");
}
}
async getOpenDoc(appId, headers) {
let session = this.openEngineSession(headers);
let global = await session.open();
let doc = await global.openDoc(appId);
return doc;
}
}
3. Including the Nebula Charts needed and rendering the recommended viz from Insight Advisor
When we eventually get back a recommendation from Insight Advisor, we will use a nebula object to embed it in our web app.
For a full list of available Nebula objects, visit: https://qlik.dev/embed/foundational-knowledge/visualizations/
We need to install “stardust” that contains the main embed function and all the nebula objects we need:
npm install @nebula.js/stardust
then install all objects needed
npm install @nebula/sn-scatter-plot
npm install @nebula/sn-bar-chart
etc...import { embed } from '@nebula.js/stardust';
import scatterplot from '@nebula/sn-scatter-plot';
etc...
Inside the rendering function, we will use stardust’s embed method to render the recommended chart type we get from Insight Advisor.
async function fetchRecommendationAndRenderChart(requestPayload) {
// fetch recommendations for text or metadata
const recommendations = await getRecommendation(requestPayload);
const engineUrl = `${tenantUrl.replace('https', 'wss')}/app/${appId}`;
// fetch rec options which has hypercubeDef
const recommendation = recommendations.data.recAnalyses[0];
// get csrf token
const qcsHeaders = await getQCSHeaders();
const engineService = new EngineService(engineUrl);
// get openDoc handle
const app = await engineService.getOpenDoc(appId, qcsHeaders);
await renderHypercubeDef(app, recommendation);
}
async function renderHypercubeDef(app, recommendation) {
const type = recommendation.chartType;
const nebbie = embed(app, {
types: [
{
name: type,
load: async () => charts[type],
},
],
});
document.querySelector('.curr-selections').innerHTML = '';
(await nebbie.selections()).mount(document.querySelector('.curr-selections'));
await nebbie.render({
type: type,
element: document.getElementById('chart'),
properties: { ...recommendation.options }
});
4. Calling the Insight Advisor API for recommendations
You can either call the API with a natural language question or a set of fields and master items with an optional target analysis.
Insight Advisor API endpoints that can be called:
api/v1/apps/{appId}/insight-analyses Returns information about supported analyses for the app's data model. Lists available analysis types, along with minimum and maximum number of dimensions, measures, and fields.
api/v1/apps/{appId}/insight-analyses/model Returns information about model used to make analysis recommendations. Lists all fields and master items in the logical model, along with an indication of the validity of the logical model if the default is not used.
api/v1/apps/{appId}/insight-analyses/actions/recommend Returns analysis recommendations in response to a natural language question, a set of fields and master items, or a set of fields and master items with an optional target analysis.
// Getting the recommendation
async function getRecommendation(requestPayload) {
await qlikLogin(); // make sure you are logged in to your tenant
// build url to execute recommendation call
const endpointUrl = `${tenantUrl}/api/v1/apps/${appId}/insight-analyses/actions/recommend`;
let data = {};
// generate request payload
if (requestPayload.text) {
data = JSON.stringify({
text: requestPayload.text,
});
} else if (requestPayload.fields || requestPayload.libItems) {
data = JSON.stringify({
fields: requestPayload.fields,
libItems: requestPayload.libItems,
targetAnalysis: { id: requestPayload.id },
});
}
const response = await fetch(endpointUrl, {
credentials: "include",
mode: "cors",
method: 'POST',
headers,
body: data,
});
const recommendationResponse = await response.json();
return recommendationResponse;
}
Results:
For the complete example that includes calling the API with fields, master items, and a target analysis type, visit qlik.dev post:https://qlik.dev/embed/gen-ai/build-insight-advisor-web-app/
The full code for this post can be found here:https://github.com/ouadie-limouni/insight-advisor-apiMake sure to change the variables in index.js.
I hope you find this post helpful, please let me know if you have any question in the comment section below!Ouadie
...View More
The purpose of this blog is to provide users with a few potential use cases for the ‘Alternative States’ feature within Qlik sense. For a full introduction and explanation of the feature please see Ouadie Limouni’s blog on the subject here.
Concatenate is a prefix that can be used when loading a table in the script. Using concatenate explicitly states that you want the data that is currently being loaded to be appended to the end of a specified table. According to Qlik Help, the syntax looks like this:
Concatenate is often used when different sets of data, often from different data sources, need to be added to the same table such as a fact table. I often use concatenate when adding new data to a link table in my data model. This is an example of explicitly using concatenate to append rows to an existing table. If the data sets do not have the same data structure, the concatenate prefix must be used, otherwise a synthetic table will be created and the data sets will be store in separate tables. In the script below, the People table is loaded with two fields, Name and Title. The second data set, starting on line 8, is loaded using the concatenate prefix because the field Department does not already exist in the People table. By concatenating the table, the Department field will be added to the People table and the data in the second table will be appended to the end of the People table. The third table, starting on line 15, is implicitly added to the People table and does not require the concatenate prefix because it has the same three fields, Name, Title, and Department) as the new People table.
Below is the resulting table. Notice that the first two rows have null in the Department field because this data is from the initial data set that did not include the Department field.
Let’s look at another example of implicit concatenation. If a table is being loaded with the same fields as an existing table, the data will be appended to the end of the existing table even though the concatenate prefix is not used. For example, below in the script on the left, the data from the table starting on line 8 would be appended to the end of the People table because the second table has the same fields as the People table. This can happen even if these two data sets are loaded in different parts of the script. They do not need to be loaded sequentially. The script below, on the right, using the concatenate prefix will produce the same results explicitly. I prefer to explicitly concatenate to avoid any confusion.
The resulting table will look like this:
In preparing this blog, I learned another way I could load multiple files with the same data structure taking advantage of implicit concatenation. The script below shows how I use the wildcard (*) to load several files with the same data structure.
What I learned is that I can also use a loop and implicit concatenation to do the same thing. After the script below runs, the TempData table will have all the data from the CSV files. The first time through the loop, the TempData table is created and subsequent times through the loop, the data is appended to the end of the now existing TempData table.
After 14 years with Qlik, I am still picking up new things. That is what makes my job so much fun!
Thanks,
Jennell
...View More
When we were considering how to best support our customers with managing their content in Qlik Cloud, we took a deep look at what we had and what it could become. We quickly realized that if we separated the needs customers have around security, organization, and process into distinct solutions, we could make the system more flexible and better suited to our customers.
With key drivers, users can better understand the reasons for historical results, not just the what but the why, providing deeper insight and supporting more meaningful action.
You might be familiar with the concept of Window functions from Excel or SQL and know just how convenient and powerful they can be. Well, Qlik has one that you can use right in your Load Script!
Simply put, the Window function performs calculations over multiple rows producing a value for each row separately, unlike aggregate functions that will give a single value for the group of rows aggregated together.
You can think of it as looking through a window at your dataset and only seeing a subset based on different parameters you set which we will go over in a minute.
If you wanted to calculate the average transaction_amount by customer, you could of course do this in the chart expression with something like this: aggr(avg(transaction_amount), customer_id), or if you’re in the Load Script, perform another load and use Group By as follows:
Temp:
//inline load here
Transactions:
NoConcatenate Load
transaction_id,
transaction_date,
transaction_amount,
transaction_quantity,
customer_id,
size,
color_code
Resident Temp;
Load customer_id,
Avg(transaction_amount) AS AvgAmount
Resident Transactions
Group By customer_id;
But this requires a separate load and can’t just be done on the same loaded table, and it might not be ideal for more complex use cases.
This is where the Window function comes in, and the above can be re-written as follows:
Temp:
//inline load here
Transactions:
NoConcatenate Load
transaction_id,
transaction_date,
transaction_amount,
transaction_quantity,
customer_id,
size,
color_code,
Window(Avg(transaction_amount),customer_id) as AvgCustTransaction
Resident Temp;
Much easier!
Syntax:
Let’s take a closer look at the function syntax to understand it a little more and see what other capabilities it has:
Window(input_expr, [partition1, partition2, ...],[sort_type, [sort_expr]],[filter_expr],[start_expr,end_expr])
input_expr
Refers to the input expression calculated and returned by the function. It must be any expression based on an aggregation, such asMedian(Salary). For example:
Window(Median(Salary)) as MedianSalary
The input can also be a field name with no aggregation applied and in that case Qlik treats it like theOnly()function. For example:
Window(Salary,Department) as WSalary
Partition: [partition1, partition2, ...]
After input_expr, you can define any number of partitions. Partitions are fields that define which combinations to apply the aggregations with. The aggregation is applied separately with each partition. (Think of it as the Group By clause). Multiple partitions can be defined. For example:
Window(Avg(Salary), Unit, Department, Country) as AvgSalary
sort_type, sort_expr
The sort type and the sort expression can be specified optionally.sort_typecan have one of two values ASC(Ascending sorting) or DESC (Descending sort)
If sort_type is defined, then the sorting expression must also be defined. This is an expression that decides the order of the rows within a partition.
For example:
Window(RecNo(), Department, 'ASC', Year)
// results within the partition are sorted Ascendingly by year
filter_expr
The optional Filter Expression is a Boolean expression that decides whether the record should be included in the calculation or not.
This parameter can be omitted completely, and the result should be that there is no filter.
For example:
Window(avg(Salary), Department, 'ASC', Age, EmployeeID=3 Or EmployeeID=7) as wAvgSalaryIfEmpIs3or7
Sliding Window with start_expr,end_expr
Optionally, you can set the argument for sliding window functionality.A sliding window requires two arguments:
start_expr:The number of rows prior to the current row to include in the window.
end_expr:The number of rows after the current row to include in the window.
For example, if you want to include the 3 preceding rows, the current row, and the 2 following row:
Window(concat(Text(Salary),'-'), Department, 'ASC', Age, Year>0, -3, 2) as WSalaryDepartment
Examples:
Let’s take a look at different use case examples:
1- Adding a field containing an aggregation
Transactions:
Load
*,
Window(Avg(transaction_amount),customer_id) as AvgCustTransaction;
Load * Inline [
transaction_id, transaction_date, transaction_amount, transaction_quantity, customer_id, size, color_code
3750, 20180830, 23.56, 2, 2038593, L, Red
3751, 20180907, 556.31, 6, 203521, M, Orange
3752, 20180916, 5.75, 1, 5646471, S, Blue
3753, 20180922, 125.00, 7, 3036491, L, Black
3754, 20180922, 484.21, 13, 049681, XS, Red
3756, 20180922, 59.18, 2, 2038593, M, Blue
3757, 20180923, 177.42, 21, 203521, XL, Black
3758, 20180924, 153.42, 14, 2038593, L, Red
3759, 20180925, 7.42, 5, 203521, M, Orange
3760, 20180925, 80.12, 18, 5646471, M, Blue
3761, 20180926, 3.42, 7, 3036491, XS, Black
3763, 20180926, 63.55, 12, 049681, S, Red
3763, 20180927, 177.56, 10, 2038593, L, Blue
3764, 20180927, 325.95, 8, 203521, XL, Black
];
2- Adding a field containing an aggregation filtered for specific values
Transactions:
Load
*,
Window(Avg(transaction_amount),customer_id, color_code = 'Blue') as AvgCustTransaction;
Load * Inline [
// Table goes here
];
3- Adding a field with a sliding window
Transactions:
Load
*,
Window(Avg(transaction_amount),customer_id, 'ASC', -1, 1, 0, 1) as AvgCustTransaction;
Load * Inline [
// Table goes here
];
This concludes this post,I hope you found it helpful! A qvf with all the scripts is attached for reference.
- Thanks
...View More