Data Integration
Forums for Qlik Data Integration solutions. Ask questions, join discussions, find solutions, and access documentation and resources
Qlik Gallery
Share your best Qlik apps and discuss impacts with peers! Show your work and get recognized for innovative uses of Qlik technologies.
About Qlik Community
Get started on Qlik Community, find How-To documents, and join general non-product related discussions.
Qlik Resources
Direct links to other resources within the Qlik ecosystem. We suggest you bookmark this page.
Qlik Academic Program
Qlik gives qualified university students, educators, and researchers free Qlik software and resources to prepare students for the data-driven workplace.
Recent Blog Posts
-
On Demand App Generation - Sooooooo much easier to implement! (VIDEO and samples...
Let's face it - it usually takes a bit longer for features and capabilities of any product to gain traction in an organization. We released On Deman... Show MoreLet's face it - it usually takes a bit longer for features and capabilities of any product to gain traction in an organization. We released On Demand App Generation in 2018 with our Qlik Sense client-managed edition. Frankly I don't have much insight into whom has or has not implemented it. BUT, I can tell you from those that I have spoken with over the years, many were surprised to even see this awesome feature in the product when I brought it up.
However, in older versions, in order to enable it - there were a number of requirements which involved copying data load script along with inserting bindings and variables - which at first glance could be perceived as cumbersome. Even the first time I worked with it, I was a bit overwhelmed. This was true for others as well, so much so, that some Qlik enthusiast even developed web app add-ons and extensions to simplify the process and generate the template for you.BUT....... since the release of ODAG, just like anything else, it has evolved and is now extremely simple to enable and implement. I show you this process in my latest Do More with Qlik (archive link below) session and summarize the ODAG concept in the latest Qlik Sense in 60 video embedded in this post - so please be sure to check them out. Let me know what you think in the comments below. Stay tuned to my next post where I build on what we learned about ODAG to introduce you to Dynamic Views!
On Demand App Generation - (ODAG - concept)
In summary, ODAG was originally developed to meet the need of analysis of very large data sets. The concept is quite simple:
- One Qlik Sense app displays summarized data with filters from the big data source.
- You select the filter values and reduce your answer set to meet a defined row count constraint.
- You click the app navigation link along the bottom to open the ODAG panel
- You click a button and a new more detailed app, dynamically built from a template, is generated directly from the source data with applied filters and defined metrics at a much more detailed level. That's it!
ODAG Requirements Summarized
- Qlik Sense base app (contains summarized measures as KPIS and desired dimension values as filters)
- Summarized data
- App Navigation link - linked to 2nd Qlik Sense template app
- Defined row count constraint
- App retention setting
- Qlik Sense template app (contains detail KPIs and detailed analysis metrics)
- Activation script with binding syntax
- WHERE conditions with odag_ binding variables added to query
Qlik Sense in 60 - On Demand App Generation (video)
(Video transcript attached)
Help Topics
Source data:
https://www1.nyc.gov/site/tlc/about/tlc-trip-record-data.page
Presentation:
Do More with Qlik Session - you may need to register to access it:
https://gateway.on24.com/wcc/experience/eliteqliktech/1910644/2395144/do-more-with-qlik-for-beginners-and-beyond
Register:https://pages.qlik.com/21Q3_QDEV_DA_GBL_DoMorewithQlikTargetpage_Registration-LP.html
Sample Apps attached - ODAG - Apps - Taxi Trips.zip - (Note you need to add your data connection and access SQL etc to your data sources)
Can't see the video? YouTube blocked by your region or organization? Download the .mp4 attached in this post to view this on your computer or mobile device.
-
Connector Factory – October and November 2023 Releases
Qlik Cloud Data Integration Additional SaaS application connectors We’re continuing to expand the connectivity of Qlik Cloud Data Integration with a n... Show MoreQlik Cloud Data Integration
Additional SaaS application connectors
We’re continuing to expand the connectivity of Qlik Cloud Data Integration with a new group of connectors that just got released. These connectors will enable you to gather business data from additional applications that focus on the supply chain, ERP, CRM, and finance:
- Acumatica ERP
- GitHub
- QuickBooks
- SAP Ariba Analytical
- SAP Ariba Event Management
- SAP Ariba Operational Procurement
- SAP Ariba Operational Sourcing
- SAP Ariba Supplier Data
- SAP Ariba Supplier Risk Engagements
- SAP Cloud for Customer
- SAP Fieldglass
- SAP SuccessFactors Payroll
- Stripe
- Zoho Books
- Zoho CRM
- Zoho Expense
- Zoho Inventory
We also updated the existing Oracle OPERA connector to support change data capture.
Similar to our previous connector releases, these connectors are Generally Available (GA), covered by our standard support policies and are tagged as Preview. If you haven’t already done so, you will need to contact your local support representative to activate the feature flag that enables all recently released connectors in your tenant. The preview tag and feature flag are there because these connectors do not cover all use cases and we want to make sure you have the best experience possible.
Private connections - A private connection (sometimes called a PrivateLink) can be used to ensure your data traffic remains secure and compliant. Qlik Cloud Data Integration already supports private connections if Snowflake, Microsoft SQL Server or AWS S3 is the target of a data pipeline. We now additionally support private connections when moving data to Databricks, Microsoft Azure Synapse, Google BigQuery or AWS Redshift.
Qlik Data Integration
The recent release of Qlik Replicate November 2023 includes a number of security enhancements related to connectivity:
- Snowflake targets (on Google, AWS, or Azure) now support OAuth2 authentication
- The Kafka target now supports certificate + username and password authentication
- Amazon S3 and Amazon Redshift Targets now support IAM Roles Anywhere
- MySQL-based targets now support SSL
In addition, Qlik Replicate now supports two new target endpoints (Microsoft Fabric Synapse Data Warehouse & Confluent Cloud), a new source endpoint (Google Cloud SQL for SQL Server) and an enhanced Google BigQuery target endpoint.
You can find more details about the Qlik Replicate November 2023 release in this blog or the release notes.
Qlik Analytics Cloud
New Connectors
Amazon Bedrock – This is a platform that hosts a number of generative AI models (or LLMs), including models not just from Amazon but also leading AI startups, so you can choose which LLM is best suited for your use case. Qlik now offers the following connectors within the Amazon Bedrock platform:
- AI21 Labs
- Amazon Titan
- Anthropic
- Cohere
With these real-time analytics connectors, users can interact with apps and refine context, sending relevant subsets of data to generative AI for a variety of use-cases such as sentiment analysis, few shot learning and classification, data synthesis, summarization, and enrichment, and more. And this is only the beginning – customers can build whatever new use cases they want using generative AI in combination with our engine, and fully controlling the relationship with the third-party models.
More details on these integrations can be found here.
Integration with Microsoft Teams
We recently released a new app within Microsoft Teams which allows you to explore analytics using natural language. You can ask a question within Teams and Qlik will respond with AI-generated visualizations and insights, using data from across your Qlik apps. And because you’re in Microsoft Teams, you can easily bring others into the conversation to share and collaborate, as well as access Insight Advisor on mobile devices through the Teams app.
You can find out more about this integration in this blog.
Qlik Application Automation
We continue to add more connectors and capabilities to our no-code cloud service that streamlines workflows between SaaS applications and Qlik Cloud.
New Connector
Amazon Bedrock - enables your automation to interact with any of the text and image models from Bedrock and send the model's output to any other automation connector or use it as input for another Bedrock model.
Updated Connectors
OpenAI - A new Chat Completion block enables you to run prompts against OpenAI's powerful GPT models.
Qlik Cloud Services - blocks have been added to enable Qlik Application Automation to interact with the Qlik Business Glossary
And finally, be sure to check out the Connector Factory page on qlik.com for a complete list of which data repositories and applications are accessible by any of Qlik’s solutions (including Talend and Stitch). We will soon have connectivity to over 500 different data sources, targets, and applications!
-
The App Analyzer for Qlik Cloud Customers
How can I track app RAM usage over time? How do I optimize my applications so that they can stay under the limit? I am happy to introduce our App ... Show MoreHow can I track app RAM usage over time?How do I optimize my applications so that they can stay under the limit?I am happy to introduce our App Analyzer for Qlik Cloud, that can help answer these questions and more.
The app provides insights on:
- App, Table and Field memory footprints
- Synthetic keys and island tables to help improve app development
- Threshold analysis for fields, tables, rows and more
- Reload times and peak RAM utilization by app
- And much more!
The App Analyzer uses a single REST connection to iterate over application metadata endpoints within a tenant. The data retrieve can then be measured against tenant quotas and user-defined thresholds to empower Admins to act on the insights that the app reveals. To see the app in action, check out this demo:
A few things to note:
- This app is provided as-is and is not supported by Qlik Support.
- It is recommended to always use the latest app.
- Information is not collected by Qlik when using this app.
Check out Optimizing Qlik Sense SaaS Apps with App Analyzer for an in-depth dive into the App Analyzer.
The app as well as the configuration guide are available via GitHub, linked below.
- QVF: https://github.com/qlik-oss/qlik-cloud-app-analyzer/releases/latest/download/app-analyzer.qvf
- Release Notes: https://github.com/qlik-oss/qlik-cloud-app-analyzer/releases/latest
- Installation Guide: https://github.com/qlik-oss/qlik-cloud-monitoring-apps/releases/latest/download/qlik-cloud-monitoring-applications-installation-guide.pdf
Any issues or enhancement requests should be opened on the Issues page within the app’s GitHub repository.
Be sure to subscribe to the Qlik Support Updates Blog by clicking the green Subscribe button to stay up-to-date with the latest Qlik Support announcements. Please give this post a like if you found it helpful!
Kind regards,
Qlik Platform Architects
Additional Resources:
Techspert Talk: Optimizing Qlik Sense SaaS Apps with App Analyzer
Our other monitoring apps for Qlik Cloud can be found below.
- Reload Analyzer
- Entitlement Analyzer
- Access Evaluator
- OEM Dashboard (for OEM Partners and multi-cloud tenants)
-
Data Science among high-demand upskilling courses
In today's tough job market, landing the job you want is a challenge. Many have the same degree and some may have better scores. How can you stand you... Show MoreIn today's tough job market, landing the job you want is a challenge. Many have the same degree and some may have better scores. How can you stand you? Is there a way you can enhance your career prospects by doing something more than others?
In the competitive job market, standing out requires more than just a degree. Some cutting edge courses for upskilling for engineers to enhance their careers includes data science. To read more on this, visit:
http://timesofindia.indiatimes.com/articleshow/102936706.cms?utm_source=contentofinterest&utm_medium=text&utm_campaign=cppstIn order to enhance your data analytics skills, if you are a student or educator, for free training and certifications, visit: qlik.com/academicprogram
-
Discovering qlik-embed: Qlik’s new library for Embedding Qlik Sense in web apps
In previous posts on the Design blog, we've explored various ways for embedding Qlik Sense analytics. These have ranged from straightforward iFrames t... Show MoreIn 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:
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-quickstartFirst 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 -
The Reload Analyzer for Qlik Cloud Customers
“How can I monitor reloads across the tenant?” “How can I see what data connections and files are being used?” “How can I view reload concurrency ... Show More- “How can I monitor reloads across the tenant?”
- “How can I see what data connections and files are being used?”
- “How can I view reload concurrency and peak reload RAM over time?”
To help answer these questions, we are happy to share with you the capabilities of our Reload Analyzer for Qlik Sense SaaS!
The Reload Analyzer app provides insights on:
- Number of reloads by type (Scheduled, Hub, In App, API) and by user
- Data connections and used files of each app’s most recent reload
- Reload concurrency and peak reload RAM
- Reload tasks and their respective statuses
- And much more!
(Available sheets)
The Reload Analyzer uses Qlik’s RESTful APIs to fetch all the required data and stores the history in QVD files, allowing for efficient reloads and historical analysis.
A few things to note:
- This app is provided as-is and is not supported by Qlik Support.
- It is recommended to always use the latest app.
- Information is not collected by Qlik when using this app.
The app as well as the configuration guide are available via GitHub, linked below.
- QVF: https://github.com/qlik-oss/qlik-cloud-reload-analyzer/releases/latest/download/reload-analyzer.qvf
- Release Notes: https://github.com/qlik-oss/qlik-cloud-reload-analyzer/releases/latest
- Installation Guide: https://github.com/qlik-oss/qlik-cloud-monitoring-apps/releases/latest/download/qlik-cloud-monitoring-applications-installation-guide.pdf
Any issues or enhancement requests should be opened on the Issues page within the app’s GitHub repository.
Be sure to subscribe to the Qlik Support Updates Blog by clicking the green Subscribe button to stay up-to-date with the latest Qlik Support announcements. Please give this post a like if you found it helpful!
Kind regards,
Qlik Digital Support Team
Additional Resources:
Our other monitoring apps for Qlik Cloud can be found below.
- App Analyzer
- Entitlement Analyzer
- Access Evaluator
- OEM Dashboard (for OEM Partners and multi-cloud tenants)
-
Introducing Qlik's intelligent AI assisted Conversational Analytics in Microsoft...
Great news! Qlik has recently introduced conversational analytics in Microsoft Teams. With our new Teams app, you can easily chat with Insight Adviso... Show MoreGreat news!
Qlik has recently introduced conversational analytics in Microsoft Teams. With our new Teams app, you can easily chat with Insight Advisor, Qlik's intelligent AI assistant, to explore data using natural language directly within Teams.
Users can now ask questions through individual or group chat, and Qlik will respond with AI-generated data visualizations and insights using data from across your Qlik apps. And because it's Microsoft Teams, you can collaborate with others in real-time, collectively making decisions using the insights generated by Qlik. Insight Advisor within Teams provides a powerful new way to help more people find the right answers, make better decisions and collaborate together where and how they work.
Look for the Qlik app in the Teams App Store to get started.
Want to know more about Qlik's Conversational Analytics in Microsoft Teams?
We have put together resources to help you get started.
Video tutorials (SaaS in 60):
Introduction to Qlik Conversational Analytics in Teams
Microsoft Teams Integration Setup - Part 1
Microsoft Teams Integration Setup - Part 2Qlik Documentation:
Accessing Insight Advisor Chat through external collaboration platforms
Exploring app content with conversational analytics in Microsoft Teams
Managing connections to external collaboration platforms
Availability of the Qlik chatbot app in Microsoft TeamsThank you for choosing Qlik,
Qlik Support -
Developing for the Qlik Sense Mobile for SaaS app
Qlik Sense Mobile for SaaS was recently released to the general public and judging by what I have seen on my social media feeds, it has been well rece... Show MoreQlik Sense Mobile for SaaS was recently released to the general public and judging by what I have seen on my social media feeds, it has been well received. Having had the experience of working with the app leading up to the release, I thought it might be helpful to share some of the things that I learned about developing an app for the Mobile SaaS app.
Be conscience of the design real estate
Let’s face it, there is much more design real estate on a desktop than on a mobile device. You need to keep this at the top of mind when developing your app. Also, note that the current version of the Qlik Sense mobile app can only be viewed in portrait. With all of that in mind, when designing a sheet, most times, less is more. Only include those visualizations that are relevant to the story being told. Visualizations will be resized to meet the size of the device on which the app is consumed, so be conscience of pie charts that contain many dimensions or dimensions with small slivers of data as these can be tricky to analyze for the user. Scatter plots with data density can also be difficult for a user on a mobile device. And finally, because of the portrait only view, standard tables and pivot tables will only have the first couple of columns visible to user. To see the rest of the screen, he/she will have to scroll.
Small Screen Layout - Grid View vs List View
In the sheet properties of Qlik Sense sheets, there is a powerful property, Small screen layout, that allows you to decide how you want the visualizations to appear on mobile.
Grid View will display the sheet exactly as it is laid out on the desktop. This is good for sheets where you may want the user to be able to see the overall picture of the data.
List View will display the objects on your sheet in a horizontal card scroll view. The objects will appear one at a time and they will be sorted in the order in which they appear, starting from the top left of the sheet to the bottom right. The horizontal scroll is different from the traditional vertical scroll that you have seen in the past so it may take a little getting used to for the users.
If you prefer the vertical scroll don’t fret, from what I understand, look for the return of vertical scrolling in future editions of the Qlik Sense mobile app.
Filters
Keep this in mind, unless a field is hidden in the script, it is available to the user for filtering the data in the Available selections section. You can also, create Master Items that will also be available in the app. Current selections, bookmarks, and clear selections are also accessible through the same interface.
The Qlik Sense mobile for SaaS app is awesome. I recommend that you download it from the app store and take it for a test drive. There are so many cool things in the current version of the app, but from what I have seen, there are so many more enhancement coming in future releases that the mobile app will be a “must have” for Qlik Sense users.
-
Qlik Inline load
Inline load is a type of data load that can be used when you do not have a data source. The data can be entered manually or by using the wizard within... Show MoreInline load is a type of data load that can be used when you do not have a data source. The data can be entered manually or by using the wizard within QlikView or Qlik Sense.
Here are a couple of key things to remember:- Data entered through an inline clause must be enclosed by square brackets [ ]
- The columns of the table are defined in the first line of the inline load statement, separated by comas.
- A semi colon (;) is required after the closing square bracket
Here is an example of a simple table using inline load:Load * Inline [
ID, Name, Age, Title
1, Wladimir, 31, Team Lead
2, Paul, 22, Technical Support Engineer
3, Mark, 40, Customer Support Engineer
4, Janne, 27, IT Specialist
];
In the example inline load above, the columns for the table are:
- ID
- Name
- Age
- Title
The values starting with 1, are all data values that will populate the table. After loading the data, and looking at the data model, it will look like a normal table:As with any other dataset, two inline load tables can be created and be connected as can be seen below. You can also connect your inline table to an already existing table:
Inline load using the QlikView wizard:
You can accomplish the same result using the QlikView Inline load wizard.
In the edit script window, go to Insert > Load Statement > Load Inline. A window with a structure of a table will appear. Select the top cell indicated by F1 and change its value to the name of your first column. Repeat for the other columns of your inline table. Insert the data to create the table. At the end, your table should look like this:Inline load using the Qlik Sense wizard:
In the Data Manager, select the ‘Manual Entry’ option located at the top left corner of the screen.
Add the table name and double click on Field_1 cell to insert your first column name. Repeat this for the other columns. After naming all the columns, you should have something like in the image below.
Note that at the bottom right you can see the column count = 4 and number of rows = 0. If you Insert data, you see that the number of rows is no longer 0.For more detail information on using the Qlik Sense Wizard, please see the following resources:
Manual Entry - Qlik Sense
Inline load Delimiter
Please give this post a like if you found it helpful! Also please let us know if you have any questions or leave your feedback in the comments. -
The As-Of Table
Last week I wrote about how the Above() function can be used for calculating rolling averages and other accumulations. There is however also an altern... Show MoreLast week I wrote about how the Above() function can be used for calculating rolling averages and other accumulations. There is however also an alternative method for doing the same thing:
The As-Of table.
When you use the Above() function, you fetch a number from other rows in a chart or Aggr() table. The As-Of table is slightly different in this respect: It is not a transient table created by an object or an expression – instead it is a real table in the data model.
The idea is to create a secondary month field – the AsOfMonth - that links to multiple real months.
In the example above, you can see that ‘2015 Oct’ links to several preceding months, and each Month in turn links to several rows in a fact table. This means that a specific transaction will be linked to several AsOfMonths.
In the data model, the As-Of table should appear as a separate calendar table that links to the existing primary calendar table:
One way to create this table is the following:
First, make sure that you in your master calendar have a field “Month” that is defined as the first date of the month, e.g.
Date(MonthStart(Date),'YYYY MMM') as Month,
Then add the following lines at the end of the script:
// ======== Create a list of distinct Months ========
tmpAsOfCalendar:
Load distinct Month
Resident [Master Calendar] ;// ======== Cartesian product with itself ========
Join (tmpAsOfCalendar)
Load Month as AsOfMonth
Resident tmpAsOfCalendar ;// ======== Reload, filter and calculate additional fields ========
[As-Of Calendar]:
Load Month,
AsOfMonth,
Round((AsOfMonth-Month)*12/365.2425) as MonthDiff,
Year(AsOfMonth)-Year(Month) as YearDiff
Resident tmpAsOfCalendar
Where AsOfMonth >= Month;Drop Table tmpAsOfCalendar;
Once this table has been created, you can use the AsOfMonth as dimension in charts where you want rolling averages and accumulations.
If you as measure use
Sum({$<YearDiff={0}>} Sales)
you will get a yearly accumulation – year-to-date up until the day of the script run.
If you instead use
Sum({$<MonthDiff={"<6"}>} Sales) / Count(distinct {$<MonthDiff={"<6"}>} Month)
you will get a 6-month rolling average:
And finally, if you use
Sum({$<MonthDiff={0}>} Sales)
You will get the real, non-accumulated numbers.
I have made the Set Analysis expressions based on two fields: YearDiff and MonthDiff. However, for clarity it could be a good idea to add flags in the As-Of table, so that the Set Analysis expressions become even simpler, e.g.
If(MonthDiff=0,1,0) as IsSameMonth,
If(YearDiff=0,1,0) as IsSameYear,
If(MonthDiff<6,1,0) as IsRolling6,Summary: The As-Of table is a good way to calculate rolling averages and accumulations.
Further reading related to this topic:
Calculating rolling n-period totals, averages or other aggregations
-
Qlik and Talend Subprocessors General Data Protection Regulation (GDPR) - Versio...
Version 4.6. Current as of: 22nd November 2023 Qlik and Talend, a Qlik company, may from time to time use the following Qlik and Talend group companie... Show MoreVersion 4.6. Current as of: 22nd November 2023
Qlik and Talend, a Qlik company, may from time to time use the following Qlik and Talend group companies and/or third parties (collectively, “Subprocessors”) to process personal data on customers’ behalf (“Customer Personal Data”) for purposes of providing Qlik and/or Talend Cloud, Support Services and/or Consulting Services.
Qlik and Talend have relevant data transfer agreements in place with the Subprocessors (including group companies) to enable the lawful and secure transfer of Customer Personal Data.
Please note that, as of the date of the most recent version of this list, Qlik and Talend do not process Customer Personal Data on each other’s behalf (i.e., if you purchase Qlik offerings, your organization’s Customer Personal Data will only be processed by Qlik affiliates and Qlik third party subprocessors, and not those of Talend, and vice-versa).
You can receive updates to this Subprocessor list by subscribing to this blog or by enabling RSS feed notifications.
Subprocessors for Qlik offerings:
Third party subprocessors for Qlik Cloud
Third Party
Location of processing
Service Provided
Amazon Web Services (AWS)
If EU region is chosen:
- Ireland (Republic of); &
Paris, France (back-up); or
- Frankfurt, Germany; &
Milan, Italy (back-up); or
- London, UK; &
Spain (back-up).
- Frankfurt, Germany (Blendr only).
If US region is chosen:
- North Virginia, US; &
Ohio, US (back-up).
Customer may select one of two APAC locations:
- Sydney, Australia; &
Melbourne, Australia (back-up); or
- Singapore; &
Seoul, South Korea (back-up ).
Qlik Cloud is hosted through AWS
MongoDB
If EU region is chosen:
- Ireland (Republic of);&
Paris, France (back-up); or
- Frankfurt, Germany; &
Milan, Italy (back-up); or
- London, UK; &
Spain (back-up)
- Frankfurt, Germany (Blendr only).
If US region is chosen:
- North Virginia, US; &
Ohio, US (back-up).
Customer may select one of two APAC locations:
- Sydney, Australia; &
Melbourne, Australia (back-up); or
- Singapore; &
Seoul, South Korea (back-up).
Any data inputted into the Notes feature in Qlik Cloud
Third party subprocessors for Support Services and/or Consulting Services
The vast majority of Qlik’s support data that it processes on behalf of customers is stored in Germany (AWS). However, in order to resolve and facilitate the support case, such support data may also temporarily reside on the other systems/tools below.
Amazon Web Services (AWS)
Germany
Support case management tools
Salesforce
UK
Support case management tools
Grazitti SearchUnify
United States
Support case management tools
Microsoft
United States
Customer may send data through Office 365
Ada
Germany
Support Chatbot
Persistent
India
R&D Support Services
Altoros
United States
R&D Support Services
Ingima
Israel
R&D Support Services
Galil
Israel
R&D Support Services
ISS Consult
Romania
Support services for Blendr only
Wipro
India
IT support services
Third party subprocessors for mobile device apps
Google Firebase
United States
Push notifications
Qlik Affiliate Subprocessors
These affiliates may provide services, such as Consulting or Support, depending on your location and agreement(s) with Qlik. Qlik’s Support Services are predominantly performed in the customer’s region: EMEA – Sweden, Spain, Israel; Americas – USA; APAC – Japan, Australia, India.
Subsidiary Affiliate
Location
QlikTech International AB
Sweden
QlikTech Nordic AB
Sweden
QlikTech Latam AB
Sweden
QlikTech Denmark ApS
Denmark
QlikTech Finland OY
Finland
QlikTech France SARL
France
QlikTech Iberica SL (Spain)
Spain
QlikTech Iberica SL (Portugal liaison office)
Portugal
QlikTech GmbH
Germany
QlikTech GmbH (Austria branch)
Austria
QlikTech GmbH (Swiss branch)
Switzerland
QlikTech Italy S.r.l.
Italy
QlikTech Netherlands BV
Netherlands
QlikTech Netherlands BV (Belgian branch)
Belgium
Blendr NV
Belgium
QlikTech UK Limited
United Kingdom
Qlik Analytics (ISR) Ltd.
Israel
QlikTech Netherlands BV (Russian branch)
Russia
QlikTech International Markets AB (DMCC Branch)
United Arab Emirates
QlikTech Inc.
United States
QlikTech Corporation (Canada).
Canada
QlikTech México S. de R.L. de C.V.
Mexico
QlikTech Brasil Comercialização de Software Ltda.
Brazil
QlikTech Japan K.K.
Japan
QlikTech Singapore Pte. Ltd.
Singapore
QlikTech Hong Kong Limited
Hong Kong
Qlik Technology (Beijing) Limited Liability Company
China
QlikTech India Private Limited
India
QlikTech Australia Pty Ltd
Australia
QlikTech New Zealand Limited
New Zealand
Subprocessors for Talend offerings
Third party subprocessors for Talend Cloud
Third Party
Location of processing
Service Provided
Amazon Web Services (AWS)
Talend Cloud
AMERICAS:
- Virginia, US; &
Oregon, US (backup).
EMEA:
- Frankfurt, Germany; &
Ireland (Republic of)(backup).
APAC:
- Tokyo, Japan; & Singapore (backup); or
- Sydney, Australia; & Singapore (backup).
Stitch
AMERICAS:
- Virginia, US; &
Oregon, US (backup).
EMEA:
- Frankfurt, Germany; &
Ireland (Republic of) (backup).
These Talend Cloud locations are hosted through AWS
Microsoft Azure
United States:
California; Virginia (backup)
These Talend Cloud locations are hosted through Microsoft Azure
MongoDB
See Talend Cloud locations above
Third party subprocessors for Support Services and/or Consulting Services:
In order to provide Support and/or Consulting Services, the following third party tools may be used.
Sub-processor
Data Center Location
Service Provided
Github
United States
Support ticket replication, troubleshooting
Intercom
United States
In-app customer support messaging service
Atlassian
France
United States
Project management; support issue tracking
Microsoft
United States
Email provider, if the Customer sends Customer Personal Data through email.
Proofpoint Secure Share
United States
File sharing if Customer files include Customer Personal Data.
Salesforce
United States
CRM; support case management
Talend Affiliate Subprocessors
These affiliates may provide services, such as Consulting or Support, depending on your location and agreement(s) with Talend. Qlik’s Support Services are predominantly performed in the customer’s region: EMEA – France, Germany, UK, Americas – USA, Canada, APAC – China, Japan, Australia, India, Singapore.
Subsidiary Affiliate
Location
Talend Australia Pty Ltd.
Australia
Talend China Beijing Technology Co. Ltd.
China
Talend (Canada) Limited
Canada
Talend SAS
France
Talend Germany GmbH
Germany
Talend Data Integration Services Private Limited
India
Talend Italy S.r.l.
Italy
Talend Limited
Ireland
Talend KK
Japan
Talend Netherlands B.V.
Netherlands
Talend Sucursal Em Portugal
Portugal
Talend Singapore Pte. Ltd.
Singapore
Talend Spain, S.L.
Spain
Talend Sweden AB
Sweden
Talend GmbH
Switzerland
Talend Ltd.
United Kingdom
Talend, Inc.
United States
Talend USA, Inc.
United States
In addition to the above, other professional service providers may be engaged to provide you with professional services related to the implementation of your particular Qlik and/or Talend offerings; please contact your Qlik account manager or refer to your SOW on whether these apply to your engagement.
Qlik and Talend reserve the right to amend its products and services from time to time. For more information, please see www.qlik.com/us/trust/privacy and/or https://www.talend.com/privacy/.
-
Ctrl-Q NR
Ctrl-Q NR Ptarmigan Labs Low-code, open source tool for both client-managed and cloud Qlik Sense versions. Built on top of the excellent Node-RE... Show MoreCtrl-Q NRPtarmigan Labs
Low-code, open source tool for both client-managed and cloud Qlik Sense versions.
Built on top of the excellent Node-RED platform, Ctrl-Q NR let you quickly build data integrations, on-prem-to-cloud workflows, set up monitoring of critical app reloads and more.
Node-RED itself has more than 4600 modules covering all kinds of source systems, IoT modules, databases etc. All of which can now be integrated with both client-managed and cloud Qlik Sense.
More info at https://ptarmiganlabs.com/ctrl-q-nr-qlik-sense-nodes-for-node-red/
Discoveries
Given Node-REDs rich ecosystem of add-on modules your imagination is the limiting factor of what can be done... One example is hybrid Sense environments where on-premise/client-managed Qlik Sense is used to process data from local systems/sources. Ctrl-Q NR can then act as the hub that orchestrate cloud app reloads starting when on-prem systems have reloaded. A reload fails? That can be detected and alerted upon. Another example is integration of IoT data with Qlik Sense. Use Node-RED to collect the data and feed it to Sense, then Ctrl-Q is used to visually manage the various Qlik Sense resources that are involved (app reloads etc).
Impact
Easy prototyping and deployment of integrations that would otherwise be difficult and/or time consuming to create.
Audience
Qlik Sense admins and developers for both client-managed and cloud Qlik Sense.
Data and advanced analytics
Being able to quickly test and prototype ideas is an important capability in a fast-moving IT landscape. Thanks to the low-code nature of Node-RED (on top of which Ctrl-Q NR runs) integrations between Sense and other systems and tools can often be done in minutes. Node-RED runs on most platforms, including Windows, Linux, macOS and Docker.
-
Global Country Insights
Global Country Insights AnyChart Explore global metrics using the powerful Decomposition Tree tool in Qlik Sense, broken down by countries, cont... Show MoreGlobal Country InsightsAnyChartExplore global metrics using the powerful Decomposition Tree tool in Qlik Sense, broken down by countries, continents, and alliances in an intuitive interactive visualization.Discoveries
Explore countries, alliances, and continents using a decomposition tree, which brilliantly represents data across multiple dimensions and enables effortless ad hoc analysis.
Impact
Analyze population, area, debt, internet usage, and some other statistics, seamlessly navigating by country, continent, or alliance (APEC, NATO, etc).
Audience
Data analysts and business managers aiming to deconstruct totals and averages across multiple dimensions to empower informed decision-making about optimizing strategies and resource allocation. Anyone interested in exploring global population and other figures.
Data and advanced analytics
This app features a decomposition tree built with AnyChart's extension for Qlik Sense, the only way to add decomposition trees in Qlik Sense, utilizing The World Bank's data.
-
Transform First, or Load First? Comparing ETL and ELT Approaches
First, let's define the acronyms. ETL stands for Extract, Transform, Load. The extract step collects data from various sources. Next, the transform st... Show MoreFirst, let's define the acronyms. ETL stands for Extract, Transform, Load. The extract step collects data from various sources. Next, the transform step cleans, filters, aggregates, and prepares the data for analysis. Finally, the load step inserts the transformed data into the target destination
Conversely, ELT is Extract, Load, Transform. As the name suggests, the order of operations is slightly different. With ELT, data gets extracted from sources and then loaded directly into the target system. Transformations occur later and often within the database itself.
A core difference is where data transformation logic resides. With ETL, transformations typically happen outside the target database in a specialized engine or external programmed logic.
ETL is more flexible and can handle semi-structured and unstructured data like JSON, different text formats, files, images, and video. ELT is better suited for structured data like tables and CSV files.
By cleansing and anonymizing data prior to loading, ETL minimizes the risk of regulated data entering the target system. When there are simpler compliance requirements in unregulated industries that the cloud platform can handle, ELT can be used.
ELT is desired when simplicity in management is prioritized. However, if cloud and data warehouse design and operations management skills are in-house, ETL might come out ahead.
ETL is a mature technology with wide adoption. ELT is newer but gaining popularity as cloud data warehouses emphasize scalability and flexibility.
For small or mid-sized datasets, especially relational sources, ETL remains a sound choice. But as data volumes grow, ELT becomes more compelling with its faster loading and transformation times.
To summarize some key considerations:
- For data warehousing use cases, ELT is typically better suited, with SQL handling transformations
- In data lake environments, ETL remains preferable in most situations. ETL shines when data movement involves files and transformation is done with Spark processing.
- However, for data lake use cases demanding low latency, by offering the flexibility to consume raw data without transformation.
- When compliance with data privacy regulations is a top priority, ETL's data cleansing protections are appealing.
- ETL requires more upfront investment into integration tools and expertise, while ELT leverages existing infrastructure.
- ETL remains the prudent choice for small, relational data sources.
- As data volume, variety, and velocity increase, ELT becomes more attractive.
The optimal approach depends on your specific data environment, use case requirements, and team skills. ETL and ELT both have pros and cons, and often they can complement each other rather than act as mutually exclusive options. By understanding the key differences outlined here, you'll be better positioned to choose the right strategy or combination of both for your organization's data integration needs.
Whether you choose ETL or ELT, we have an offering for you. Take a guided tour or trial here.
-
【オンデマンド配信】Qlik ハンズオン Web セミナー：明日から使えるノウハウを習得
クリックテック・ジャパンの技術担当者が、Qlik 製品の技術的な内容や操作方法などをご紹介するオンデマンドの Web セミナーシリーズです。操作方法だけでなく、実践的なアプリを作成するためのヒントを習得することができます。 ※ 参加費無料。パソコン・タブレット・スマートフォンで、どこからでもご視聴い... Show More -
Button + Reload Data or Button + Execute App Automation - which do I use?
When creating Qlik Sense Apps - you can use a Button object and configure a variety of actions or navigation settings to it. One of those actions is ... Show MoreWhen creating Qlik Sense Apps - you can use a Button object and configure a variety of actions or navigation settings to it. One of those actions is the ability to Reload Data for the app you are in. This comes in handy during development, but also can be used where users require new data on demand.
However, when using the Reload Data action - other users may not be able to perform the reload action as they don't have ownership of the app or reload rights to the app / space the app is developed in. Here is an approach when using Qlik Cloud and Qlik Application Automation that can workaround that. This approach will also work for any App Automation that you want any user to execute.
-
Qlik Digest - November 2023
Qlik Insider: Product Release Webinar Stay at the forefront of technological advancements to gain a competitive edge with our exclusive year-end webin... Show MoreQlik Insider: Product Release Webinar
Stay at the forefront of technological advancements to gain a competitive edge with our exclusive year-end webinar. We’re unveiling our latest breakthroughs in AI-enhanced data integration, quality, and analytics as we wrap up another incredible year of innovation here at Qlik.
Join us December 6, 2023Forrester Wave™ Augmented BI Platforms List
Catch Forrester’s take on Qlik’s presence as a Strong Performer in the enterprise augmented BI market – touting time to value, security, governance, NLQ, and the strongest customer references in the evaluation. Dive into the current state of the industry and the importance AI now plays in evaluating BI platforms.
Apply to be a Qlik Luminary!
Are you a big Qlik advocate who loves driving measurable impacts from data? Why not apply to be a #QlikLuminary? Luminaries gain exclusive Qlik perks and networking opportunities. Applications for the Qlik Luminary Program are open now and close on December 15.
Tips & Tricks of the MonthWatch this recent Do More with Qlik webinar for uses of simple and complex set analysis, along with new approaches that leverage AI and Insight Advisor to develop set expressions quickly and easily.
Increase adoption for your Qlik Apps with custom training designed specifically for your users. Help your team innovate and stay competitive in the market at qlik.com/training
-
Join Us Live! Q&A with Qlik: Visualizations
Don't miss our next Q&A with Qlik! Pull up a chair and chat with our panel of experts to help you get the most out of your Qlik experience. Let our Q... Show MoreDon't miss our next Q&A with Qlik! Pull up a chair and chat with our panel of experts to help you get the most out of your Qlik experience.
Let our Qlik experts offer creative solutions to your analytics demands and answer your visualization questions.
Not able to make it live? Don't worry! All registrants will get a copy of the recording the following week.
See you there!
Qlik Global Support
-
Qlik Cloud Government is StateRAMP Authorized
As U.S. public sector organizations digitally mature and seek to be data-driven, adopting a “platform mentality” becomes essential to deliver the full... Show MoreAs U.S. public sector organizations digitally mature and seek to be data-driven, adopting a “platform mentality” becomes essential to deliver the full potential of data. Public sector agencies encounter various unique challenges around managing data governance across a disparate data landscape, navigating end-to-end analytics lifecycles, and ensuring seamless interoperability across teams. Qlik Cloud Government is here to the rescue. Created exclusively to empower U.S. public sector clients in optimizing their data potential, this cloud platform ensures both safety and compliance while fostering the seamless integration of analytics with advanced technologies like AI, ML, and automation.
What is StateRAMP?
StateRAMP, established in 2020, was designed to support state and local entities adopt cloud-based services and emerging technology in a secure way. Much like the US Government’s FedRAMP, the goal of StateRAMP is to accelerate cloud adoption and modernization by providing a standardized approach to the cybersecurity standards required from service providers offering solutions to state and local governments.
How does Qlik help customers reach StateRAMP compliance?
Qlik Cloud Government meets StateRAMP requirements. This simplifies the compliance process of authorization and streamlines deployment and implementation for customers. Qlik Cloud Government also supports FedRAMP, DISA, TX-RAMP, and more.
"I appreciate Qlik’s commitment to the StateRAMP process and investing in demonstrating the security capability and compliance of Qlik Cloud Government. This commitment by Qlik to the StateRAMP program supports our efforts at the State of Arizona for an effective and streamlined security review process, and it demonstrates a deep commitment by Qlik to security and compliance for the broader Public Sector community,"
J.R. Sloan, State Chief Information Officer, Arizona Department of Administration.
Summary:
Qlik Cloud Government can play a pivotal role in revolutionizing state and local government organizations across the United States. Serving as a comprehensive data platform, it empowers agencies to overhaul their operations and utilize data more intelligently. This ultimately enhances the delivery of public services, addressing social issues with greater efficiency, and promoting transparency and trust with citizens. The platform seamlessly integrates extensive capabilities across data integration, analytics, and provides a unified experience that makes it easy to deploy and manage. This cohesiveness results in significant benefits such as improved efficiency, cost savings, and streamlined compliance processes. Qlik Cloud Government supports a diverse range of use cases throughout the data lifecycle, from data ops for constructing sophisticated data pipelines, to enterprise BI and advanced technologies like artificial intelligence (AI) and automation. To learn more about how to create real-time insights in Qlik, check out our eBook here.
To learn more about Qlik Cloud Government, please visit out U.S. Public Sector site.
