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
Bookmarks allow users or developers to save a selection state in an app. They are useful when a user would like to save their selections in an app to view later or to share with others. For developers, bookmarks are useful if you would like to redirect a user to a specific sheet, with selections, when they open an app.
Let's begin by looking at how to create a bookmark. First, go to the sheet that you would like to bookmark and make sure your selections are already made. Then click on Bookmarks in the toolbar.
The Bookmarks window will open showing any bookmarks that have already been created. There is also a Create new bookmark button that can be clicked to create a new bookmark. After clicking the Create new bookmark button, the Create bookmark dialog window opens.
Here is where you can give the bookmark a name, add a description, and set some optional settings. By default, the Save sheet location is checked. This will save the sheet you are on when you created the bookmark and will navigate the user to this sheet when the bookmark is applied. The Save layout option will save the layout of the sheet and will apply that layout when the bookmark is applied. This is useful if you have made any layout changes on the sheet, such as expanding a pivot table or sorting a chart and want the bookmark to maintain the layout. The last option is to Save variable state in bookmarks. This option will save the current state of any variables when this bookmark is created. Once the bookmark is created, the user can use it at any time to return to the bookmarked state.
Let’s look at some other options that are available when you right click on the name of a bookmark.
Apply bookmark – will apply bookmark selections and layout and clear any previous selections.
Apply selections only - will just apply the selections of the bookmark to the app, clearing any previous selections. It will not apply any layout settings if they were saved with the bookmark.
View details – will show the details of the bookmark.
Copy link - will copy the URL for the bookmark so that it can be shared with others. Note, the bookmark will need to be made public before it can be shared. If you make a bookmark public, anyone can see and use it and you are no longer the owner, unless it is made private again.
Set as default bookmark - will redirect users to the sheet, layout and selections that are saved in the bookmark when the app is opened (instead of the app overview).
Delete – will delete the bookmark.
There are also many ways to apply a bookmark. A bookmark can be applied from the app overview by clicking Bookmarks or the toolbar of a sheet. A bookmark can be indirectly applied by using a button, for instance, and setting the action to apply the bookmark.
Bookmarks can also be used in set analysis expressions and applied to a visualization. In the expression editor, the bookmark can be inserted as seen below or can be used by name.
Example expressions using the bookmark name:
These expressions will apply the bookmark to the chart, much like when you use set analysis.
A bookmark can be edited if the name, description, or selections in the bookmark need to change. There is no longer the need to delete and create a bookmark if a change needs to be made to the selections. To edit a bookmark, click on the Bookmarks button in the toolbar and then click the Details icon next to the bookmark you would like to edit or right click on the bookmark and select View details.
From the details view, you can select the edit icon to make changes to the name or description of the bookmark. Once done, you can click the check mark to stop editing or the update icon to also update the bookmark with the current selections.
Also note that possible alternate states are visible when editing a bookmark. If there were selections made in these states when the bookmark was created, they will be visible here as well. Users have the option to copy the set expression for the bookmark if they would like to use it elsewhere in the app.
Bookmarks are a great tool for your own personal use but also for collaboration. They are easy to create and edit and take the legwork out of remembering what selections were applied when you want to return to a previous viewed state or share an insight with a colleague. Check out Qlik Help to learn more.
Thanks,
Jennell
...View More
This week I’m kicking off the holiday season with a gift that gives you new ways to create content and design dashboards in Qlik Analytics, our new layout container object.
In previous posts on the Design blog, we've explored various ways for embedding Qlik Sense analytics. These have ranged from straightforward iFrames to more complex approaches like the Capabilities API, as well as more recent tools such as Nebula.js and Enigma.js.
Today, we’re going to be taking a quick look at a new library from Qlik called qlik-embed, but before diving into it, I would like to clarify that this library is currently in public preview and at the time of writing this blog, frequent updates as well as breaking changes are prone to happen (you can read more about that on qlik.dev or follow the Changelog for updated https://qlik.dev/changelog)
So what exactly is qlik-embed?
qlik-embed is a library for easily embedding data and analytics interfaces into your web apps while overcoming some of the concerns that usually arise when embedding content from one software application to another such as third-party cookies, cross-site request forgery, content security policy etc..
The library is designed to work with web apps from simple plain HTML ones to more modern frameworks like React etc.. That is in fact made easier since whichever qlik-embed flavor you use, the configuration options, the methods, and the properties will be similar.
If you are already embedding Qlik Sense content into your web applications, you can learn about the various reasons why qlik-embed would be a better solution on qlik.dev (https://qlik.dev/embed/qlik-embed/qlik-embed-introduction#why-qlik-embed-over-capability-api-or-nebulajs)
Web Components:
qlik-embed makes use of web components which are basically custom HTML elements in the form of <qlik-embed> </qlik-embed> HTML tags that allow you to configure properties of the content you’re embedding
You can find all supported web-components here:
analytics/chart
analytics/field
analytics/selections
analytics/sheet
classic/app
How to quickly get started?
Before getting started, it’s worth noting that there are several ways to connect qlik-embed web components to Qlik.
More information about Auth can be found here:
- Connect qlik-embed:https://qlik.dev/embed/qlik-embed/connect-qlik-embed
- Best Practices:https://qlik.dev/embed/qlik-embed/qlik-embed-auth-best-practice
You can connect to qlik-embed in these ways:
Qlik Cloud API keys (cookie-less)
Qlik Cloud OAuth2 clients (cookie-less)
Qlik Cloud interactive login (cookies)
Qlik Sense Enterprise Client Managed interactive login (cookies)
None (This is a more advanced option and requires handling authenticated requests using a custom authorization backend proxy - learn more about that here: https://qlik.dev/authenticate/jwt/jwt-proxy/)
In this post, we’re going to use OAuth2 Single-page-app from the Qlik Cloud tenant Management Console under oAuth:
Example using HTML Web Components: Reference page: https://qlik.dev/embed/qlik-embed/qlik-embed-webcomponent-quickstart
First thing we need to do is add a <script> element in the <head> tag to configure the call to the qlik-embed library and set up the attributes relevant to the connection we choose.
<script
crossorigin="anonymous"
type="application/javascript"
src="https://cdn.jsdelivr.net/npm/@qlik/embed-web-components"
data-host="<QLIK_TENANT_URL>"
data-client-id="<QLIK_OAUTH2_CLIENT_ID>"
data-redirect-uri="<WEB_APP_CALLBACK_URI>"
data-access-token-storage="session"
>
</script>
data-host is the URL to your Qlik Cloud tenant. For example https://example.us.qlikcloud.com/
data-client-id is the client ID for the single-page application OAuth2 client registered for this web application.
data-redirect-uri is the location of the web page the OAuth2 client will call back to when authorization requests are made from your web application to your Qlik tenant. This web page should be added to your web application.
web-component:
<qlik-embed ui="classic/app" app-id="<APP_ID>"></qlik-embed>
oauth-callback.html:
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8" />
<script
crossorigin="anonymous"
type="application/javascript"
data-host="<QLIK_TENANT_URL>"
src="https://cdn.jsdelivr.net/npm/@qlik/embed-web-components/dist/oauth-callback.js"
></script>
</head>
</html>
You can fork the full example here and change the “Tenant URL” and the rest of the attributes to your own tenant after creating the OAuth SPA config:https://replit.com/@ouadielim/qlik-embed-HTML-Web-Components#index.html
result:
Example using React:
In React, you can use qlik’s embed-react library package: npm install @qlik/embed-react (https://www.npmjs.com/package/@qlik/embed-react)
Then, you can import QlikEmbed and QlikEmbedConfig from @qlik/embed-react.React’s context is used to pass in the hostConfig that you configure to point to your Qlik Cloud Tenant (host) and use the OAuth 2 config (clientId). The redirect URI needs to point to a page which is similar to what we did above in HTML web components.
import { QlikEmbed, QlikEmbedConfig } from "@qlik/embed-react";
const hostConfig = {
host: "<QLIK_CLOUD_TENANT>",
clientId: "<CLIENT_ID>",
redirectUri: "https://localhost:5173/oauth-callback.html",
accessTokenStorage: "session",
authType: "Oauth2",
};
const appId = "<APP_ID>";
const sheetId = ""; // sheet id or empty string
export default () => (
<QlikEmbedConfig.Provider value={hostConfig}>
<div className="container">
<h1>Qlik Embed with React</h1>
<div className="selections-bar">
<QlikEmbed ui="analytics/selections" appId={appId} />
</div>
<div className="viz">
<QlikEmbed ui="classic/app" app={appId} sheet={sheetId} />
</div>
<div className="viz">
<QlikEmbed ui="analytics/chart" appId={appId} objectId="hRZaKk" />
</div>
</div>
</QlikEmbedConfig.Provider>
);
You can clone the full React example here:https://github.com/ouadie-limouni/qlik-embed-react
result:
Limitations ?
There are a few limitations to qlik-embed as it continues to develop into a more stable and robust library - you can read more about those on qlik.dev:https://qlik.dev/embed/qlik-embed/qlik-embed-limitations
Like I mentioned at the very beginning, qlik-embed is new and evolving quickly, I invite you to test it to get familiar with it early and stay tuned for more updates and bug fixes as they come out using the Changelog page.
I hope you found this post helpful, please let me know in the comments below if you have any questions!
Thanks- Ouadie
...View More
What if you could prepare data (join, transform, STORE, etc.) using Qlik script - WITHOUT creating a standalone app - without sheets? Would you like that? Well, would you? Well now you can - introducing our new script object and script editor - with data preview and more! Watch this #qlik#SaaSin60 to learn more!
We have now added the ability to create Qlik scripts directly from the hub, ana managed in the catalog - using a new and improved script editor. Users can now build and deploy Qlik scripts upstream of analytics apps, which can be executed and result in output files in formats such as QVD and PARQUET that can be stored in the hub and used for analytics and AutoML applications. The script editor also now includes a data preview feature, allowing users to get better insight when developing scripts. This provides our customers with an improved workflow for preparing data, while setting the stage for future enhancements to analytics data prep capabilities.