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.

...View More

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. 

...View More

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

An overview on how to text and use expressions with Master Items.

...View More

Unlock the power of data with our new, redesigned Pivot Table available in the Visualization Bundle.

...View More

This week Qlik Cloud customers can now address common operational report and distribution requirements with our new Tabular Reporting capabilities.

...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. 

...View More

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