Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
This forum is where any logged-in member can create a knowledge article.
Qlik Cloud Analytics has a very powerful and easy to use data loading and transformation framework. This includes no-code data flows and Qlik scripting for advanced transformations. However, there are times when a customer may prefer to handle these transformations as much as possible outside of Qlik Analytics. Maybe the data is being used by Multiple Qlik Analytics environments, in a multi-tenant or hybrid architecture. Or maybe the data set needs to be consumed by other tools in addition to Qlik Analytics. You may also be looking to provide ready to use tables for Self-service analytics. In these situations, it makes sense to move any logic earlier in the lifecycle to avoid duplication and so all tools can benefit from the transformations.
The aim of this guide is to provide some recommendations on how to model your database for minimal transformations, or transformation-free consumption by Qlik Analytics. We cover our top ten recommendations, that if followed reduce, if not eliminate the need for extensive transformations within your Qlik analytics applications. While some of these recommendations assume a database as the data source, most are applicable across all data sources.
There are many powerful tools in Qlik’s Data Integration portfolio that can be used to achieve this, such as Talend Studio, Qlik Talend Data Integration or Qlik Compose. Qlik Talend Data Integration can even be used to replicate tables to QVDs in Qlik Cloud using Change Data Capture (CDC). For more information on these solutions, see Data Integration and Quality. However, this document will focus on the end goals, rather than the tools used to achieve this.
I am going to start with what is a somewhat controversial, but I believe necessary step for many organisations that wish to minimise downstream transformations. Views in an RDBMS allow use to encapsulate all the other changes we will need to make into a virtual table than we can simply read from our Qlik Analytics app. There are a few assumptions behind this approach though that if not true may render this unnecessary or undesirable. These assumptions are:
Often when building a data warehouse, data lake or Lakehouse we tend to include all possible columns and data so it is available at a later data to meet changing requirements. However pulling this data into a Qlik Sense app is not recommended unless there is a use for it. This is because Qlik Analytics uses a very powerful in-memory associative model to manage your data and bringing unused data into memory will have a performance impact.
It is quite common for tables in databases to include standardised columns. For example: CreatedBy, CreatedDate, etc. These columns are useful for organisational governance and audit requirements, but will cause problems in Qlik Analytics as Qlik will associate tables based on the identically named columns. Assuming you can not exclude the columns, renaming them e.g. myTable_CreatedBy, myTable_CreatedDate, etc. will avoid this issue.
Qlik does not support composite (multi-column) keys in the same way databases do. When Qlik encounters a multi-column key it creates a synthetic key based on all the possible values for the keys. Synthetic keys use extra memory and can impact performance, as well as increasing the complexity of the analytics application. They are often not required and are due to factors such as standardised names (see #3). If there is not a natural key, you may be better to use an Autonumber or guid function to create a unique key. See Synthetic keys for more information.
Closely related to #4, Qlik expects primary and foreign key columns to have the same name. So if your primary key is person.id and your foreign key is role.person-id they will not join by default. The solution is to rename person.id to person.person-id.
There are no benefits from a highly normalised data model in Qlik. Qlik stores values in symbol tables so there isn't much extra data storage required for denormalised tables and extra joins impact performance. Therefore denormalising before it reaches Qlik removes the complexity of having to do this afterwards.
Closely related to #6, Qlik Analytics performs best against a Star Schema, and well against a snowflake schema. This is also true of many other query tools so transforming into one of these Schema styles in your database will provide value across your data landscape. These transformations from a normalised schema can sometimes be easier to make in SQL outside of Qlik Analytics than if they need to be hand-coded in Qlik Script. See star and snow & When do we use Snow Flake schema in Data Model?. And as you ideally only want a single fact table, you may want to consider concatenated fact tables (see Concatenate vs Link Table ).
when loading data from a source database, It is common to filter the data to provide just what is needed for your use-case. It is possible to filter any data source, however if this is done within a Qlik Analytics load statement, the data will first be read by Qlik before excluding that which does not meet the filter. This increases load times, network bandwidth and resource usage. If however, the filter is placed on a select statement, then this will be pushed down to the source and data excluded by the filter will never be sent to Qlik. In the following example, the end result is the same. In the former however the filtering is done once the data has been read by Qlik. In the latter, it is pushed down to the database and is never seen by Qlik.
If this filter is static, it would make sense to include it as part of the view to ensure self-service users do not load unnecessary data.
Analytics rarely involves drilling down to the individual transactions. Therefore often less precision is needed for analytics as is needed for an operational system. Rounding down (say from 8 to 4, or even two decimal places) saves space, reduces data transferred and improves performance. If you are doing this it makes sense to do it before it gets to Qlik and this could be embedded in a view. For example if I have a ‘rate’ field that allows 6 decimal points, but IO only need 2, I could use a statement like “cast(rate as NUMERIC(6, 2) )”. Doing this before it gets to Qlik will provide better consistency across your Qlik Landscape and provides better performance to your self-service users.
Remember when we said to exclude columns not relevant for analytics (#2) ? Well there is an exception to this and it relates to the need to understand when data was changed so you can perform incremental loads. Storing the last modified date allows us to only look at the data changed since our last reload, so will make a big difference to performance for incremental loads.
The suggestions on this list are not the only things that you can do to make your source database Qlik analytics ready however they deserve consideration as they can provide a lot of value. And this is not the only approach you can take – there may be good reasons for for taking a different approach in some of these areas, however at least considering these facors will help you to build an analytics ready database.
What other suggestions do you have? What has your organisation done to create a Qlik Analytics ready environment? Let me know in the comments!
Hello there!
Solving the Qlik Cloud Content Embedding Puzzle with JWT Iframe Magic
Welcome to a tutorial designed for those navigating the waters of embedding Qlik Cloud content while ensuring a seamless, invisible authentication experience. Say goodbye to complexities and embrace a dynamic way to present Qlik Cloud app/sheet using iframes.
The default behavior of embedding an iframe with tenant URL, app ID, and sheet ID usually leads to unwanted redirections, demanding logins before content becomes visible.
Enter JWT authentication, the solution that sends a payload to Qlik Cloud, signs the response, and generates a JWT token for seamless authentication.
Let's dive in!
Step 1: Enabling JWT for Your Qlik Cloud Tenant Begin by enabling JWT for your Qlik Cloud tenant. This involves raising a support case with Qlik to activate JWT.
Step 2: Crafting the Key Pair for JWT Configuration As JWT gets activated, generate a public/private key pair to configure JWT authentication for Qlik Cloud. Follow this guide.
Step 3: Configuring JWT IDP Once JWT is live in your Qlik Cloud tenant, configure the JWT Identity Provider (IDP) using details obtained from the public/private key pair. Follow this guide.
Step 4: Preparing the Iframe Presentation
Gather essentials:
Take action:
Step 5: Interacting with the JWT Endpoint
This thrilling step involves:
This process allows us to authenticate (POST) against https://yourcloudtenant/login/jwt-session
. This session creation facilitates content presentation through iframes.
For implementation, I've used node.JS, requiring modules such as fs, jsonwebtoken, and uid-safe. Follow the provided links for installation instructions.
Install node.JS: Follow the installation guide.
Install module uid-safe: Check out the installation details here.
Install module jsonwebtoken: Install it via this link.
Install module fs: Find installation instructions here.
Create the .js
file, e.g., generate.js
, to handle payload/signing and JWT token output. Replace placeholders with your specifics.
Here's a peek at the code:
const fs = require('fs');
const jwt = require('jsonwebtoken');
const uid = require('uid-safe');
const privateKey = fs.readFileSync('<the path to your privatekey.pem, for example: certificates/privatekey.pem>', 'utf8'); // Adjust the path and encoding
const signingOptions = {
keyid: '<the keyid. You can copy this when you create the JWT IDP on your cloud tenant>',
algorithm: 'RS256',
issuer: '<the issuer. You can copy this when you create the JWT IDP on your cloud tenant>',
expiresIn: '30s',
notBefore: '-30s',
audience: 'qlik.api/login/jwt-session',
};
const payload = {
jti: uid.sync(32),
sub: '<the subject of the user>',
subType: 'user',
name: '<full name of the user>',
email: '<e-mail address of the user>',
email_verified: true,
groups: []
};
const myToken = jwt.sign(payload, privateKey, signingOptions);
// Output the token
process.stdout.write(myToken);
To proceed, some aspects within the JWT payload need customization. Let's address these requirements:
sub: The "sub" field within the payload represents the user ID used to transmit the payload. Obtain this ID by accessing the URL https://yourcloudtenant/api/v1/users/me
after logging into your Qlik Cloud tenant. The URL will display relevant information on-screen; focus on the URL itself. You'll find a segment like "****-ugW-**-nGH8sRxq97ksBpwx." Remove the asterisks and utilize this value for the "sub" entry.
name and email: These fields are straightforward. Retrieve the user's full name and email from the same endpoint mentioned earlier. Alternatively, you can click the "i" icon in your user list within the Management Console to access this information.
keyid and issuer: These values are furnished during JWT IDP creation. When you set up the JWT Identity Provider, these details will be visible. Finally, the const privateKey
should point to the location of your .pem
file on the server.
Make sure to make these adjustments to ensure the JWT payload accurately reflects the user and adheres to the requirements of your Qlik Cloud integration.
This .js
file interacts with the JWT endpoint and prepares the authentication token.
Testing JWT Token Generation
Now comes the exciting part – verifying the successful generation of the JWT token. To do this, execute the following command in your terminal:
node <filename.js>
Upon running this command, your terminal will display the resulting JWT token, which appears as a lengthy encrypted string. This token is crucial for establishing secure authentication within your Qlik Cloud integration.
Here's a glimpse of the token (partially redacted for security reasons):Every character within this token holds encrypted information necessary for the secure operation of your integration.
eyJhbGciOi....7sNtjlad6CJA1AV2552GGMclPXBPdfXUyrxGzNvzN0L9Rr8jhB4AyrVVXUsBjgCo6VbF6WogxPbk7P07OPN-P9awEG_7oCVaBK9gJUXT4GSI8moy7VOMFHO6tzLennruCiHIMUhKy9L3ncXIzwe9vEvllZ1bvs
Remember, your Qlik Cloud experience is about to get smoother as this token plays a pivotal role in seamless and secure authentication. Now that you've got the token, it's time to move on to the next steps.
Step 11: Validating the JWT Token
You can verify the validity of the generated JWT token by using Postman, a versatile API testing tool. Follow these steps:
Download Postman if you haven't already.
Create a new POST request in Postman, directing it to https://yourcloudtenant/login/jwt-session
.
Set the necessary headers:
Content-Type: application/json
qlik-web-integration-id: <Your web integration ID>
Authorization: Bearer <Your JWT Token>
If all goes well, you'll receive a 200 OK status, indicating the successful authentication of the JWT token.
Step 12: Crafting the JWT Token Endpoint
To provide a convenient endpoint for code reference, you can create a simple .php
file that executes the node generate.js
command on your server and presents the JWT token in encoded JSON format.
Here's an example of the PHP code:
<?php
header('Content-Type: application/json');
echo json_encode(['body' => `node generate.js`]);
This endpoint URL will be something like https://www.example.com/qlikcloud/jwt_token.php
.
Step 13: Building the Iframe Display Page
Finally, let's build the index.php
/index.html
file that utilizes the JWT Endpoint to acquire and utilize the token for the iframe display. You'll need to replace placeholders with your actual details:
<html>
<head>
<script src="https://unpkg.com/enigma.js/enigma.min.js"></script>
</head>
<body>
<div id="main">
<div id="message"></div>
<iframe id='qlik_frame' style='border:none;width:100%;height:900px;'></iframe>
</div>
<script>
// CONFIGURATION
const TENANT = '<yourcloudtenant>';
const JWTENDPOINT = 'https://www.example.com/qlikcloud/jwt_token.php';
const WEBINTEGRATIONID = '<web integration id>';
const APPID = '<appid>;
const SHEETID = '<sheetid>';
const IDENTITY = '<not mandatory, fill in whatever you want>';
// MAIN
(async function main() {
const isLoggedIn = await qlikLogin();
const qcsHeaders = await getQCSHeaders();
const [session, enigmaApp] = await connectEnigma(qcsHeaders, APPID, IDENTITY);
handleDisconnect(session);
const theme = await getTheme(enigmaApp);
renderSingleIframe('qlik_frame', APPID, SHEETID, theme, IDENTITY);
})();
// LOGIN
async function qlikLogin() {
const loggedIn = await checkLoggedIn();
if (loggedIn.status !== 200) {
const tokenRes = await (await getJWTToken(JWTENDPOINT)).json();
const loginRes = await jwtLogin(tokenRes.body);
if (loginRes.status != 200) {
const message = 'Something went wrong while logging in.';
alert(message);
throw new Error(message);
}
const recheckLoggedIn = await checkLoggedIn();
if (recheckLoggedIn.status !== 200) {
const message = 'Third-party cookies are not enabled in your browser settings and/or browser mode.';
alert(message);
throw new Error(message);
}
}
console.log('Logged in!');
return true;
}
async function checkLoggedIn() {
return await fetch(`https://${TENANT}/api/v1/users/me`, {
mode: 'cors',
credentials: 'include',
headers: {
'qlik-web-integration-id': WEBINTEGRATIONID
},
})
}
// Get the JWT and use it to obtain Qlik Cloud session cookie.
async function getJWTToken(jwtEndpoint) {
return await fetch(jwtEndpoint, {
mode: 'cors',
method: 'GET'
})
}
async function jwtLogin(token) {
const authHeader = `Bearer ${token}`;
return await fetch(`https://${TENANT}/login/jwt-session?qlik-web-integration-id=${WEBINTEGRATIONID}`, {
credentials: 'include',
mode: 'cors',
method: 'POST',
headers: {
'Authorization': authHeader,
'qlik-web-integration-id': WEBINTEGRATIONID
},
})
}
async function getQCSHeaders() {
const response = await fetch(`https://${TENANT}/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,
};
}
// ENIGMA ENGINE CONNECTION
async function connectEnigma(qcsHeaders, appId, identity) {
const [session, app] = await getEnigmaSessionAndApp(qcsHeaders, appId, identity);
return [session, app];
}
async function getEnigmaSessionAndApp(headers, appId, identity) {
const params = Object.keys(headers)
.map((key) => `${key}=${headers[key]}`)
.join('&');
return (async () => {
const schema = await (await fetch('https://unpkg.com/enigma.js@2.7.0/schemas/12.612.0.json')).json();
try {
return await createEnigmaAppSession(schema, appId, identity, params);
}
catch {
// Handle race condition with new users who do not have permissions to access the application. The code makes another attempt after a 1.5 seconds.
const waitSecond = await new Promise(resolve => setTimeout(resolve, 1500));
try {
return await createEnigmaAppSession(schema, appId, identity, params);
}
catch (e) {
throw new Error(e);
}
}
})();
}
async function createEnigmaAppSession(schema, appId, identity, params) {
const session = enigma.create({
schema,
url: `wss://${TENANT}/app/${appId}/identity/${identity}?${params}`
});
const enigmaGlobal = await session.open();
const enigmaApp = await enigmaGlobal.openDoc(appId);
return [session, enigmaApp];
}
// BONUS! DYNAMICALLY FETCH THEME
async function getTheme(enigmaApp) {
const createAppProps = await enigmaApp.createSessionObject({
qInfo: {
qId: "AppPropsList",
qType: "AppPropsList"
},
qAppObjectListDef: {
qType: "appprops",
qData: {
theme: "/theme"
}
}
});
const appProps = await enigmaApp.getObject('AppPropsList');
const appPropsLayout = await appProps.getLayout();
const theme = appPropsLayout.qAppObjectList.qItems[0].qData.theme;
return theme;
}
// HANDLE ENGINE SESSION CLOSURE
function handleDisconnect(session) {
session.on('closed', () => {
const message = '<Your text here> Due to inactivity or loss of connection, this session has ended.';
document.getElementById('qlik_frame').style.display = "none";
document.getElementById('message').innerHTML = message;
});
session.on('suspended', () => {
const message = '<Your text here> Due to loss of connection, this session has been suspended.';
document.getElementById('qlik_frame').style.display = "none";
document.getElementById('message').innerHTML = message;
});
window.addEventListener('offline', () => {
session.close();
});
}
// HELPER FUNCTION TO GENERATE IFRAME
function renderSingleIframe(frameId, appId, sheetId, theme, identity) {
const frameUrl = `https://${TENANT}/single/?appid=${appId}&sheet=${sheetId}&theme=${theme}&identity=${identity}&opt=ctxmenu,currsel`;
document.getElementById(frameId).setAttribute('src', frameUrl);
}
</script>
</body>
</html>
Customizing Integration Parameters
Before proceeding, let's customize a few essential parameters that will tie your integration together seamlessly. These values act as the foundation for your Qlik Cloud integration, enabling it to function smoothly. Let's take a moment to personalize them:
TENANT
: Replace with your Qlik Cloud tenant URL.JWTENDPOINT
: Set this to the endpoint where your JWT token will be generated, for instance: https://www.example.com/qlikcloud/jwt_token.php
.WEBINTEGRATIONID
: Input your unique web integration ID.APPID
: Insert the App ID of the Qlik Cloud app you want to embed.SHEETID
: Specify the Sheet ID of the specific sheet you wish to display.IDENTITY
: Optionally, add a unique identity to enhance tracking and management.With these values tailored to your integration's requirements, you're ready to proceed to the next step.
Witness Your Integration in Action
Now that everything is in place, it's time to witness the magic unfold. By serving your index.php
or index.html
file, you'll see your Qlik Cloud integration come to life.
Prepare to be amazed as the iframe showcases your chosen app and sheet, all seamlessly authenticated without any hassle. Sit back and enjoy the smooth presentation of data within the iframe.
Step 14: Conclusion
And there you have it! By following this thorough guide, you've accomplished the intricate process of embedding Qlik Cloud content using iframes and JWT authentication. Your users can now seamlessly access the content without encountering any login hurdles.
Remember, every placeholder like <...>
needs to be replaced with your specific details to make the entire process work seamlessly.
Enjoy the enhanced user experience and feel free to reach out if you encounter any challenges or need further assistance!
So here was the situation I was facing, my boss was screaming at me to do something that didn't exist so I ... alright truth be told he was never screaming at me and he was fine with the initial work around I came up with. I was the one screaming to try and connect to the Google Gemini Pro #LLM in the same Server Side Extension manner I had been for other Large Language Models. I wanted to be able to ask a question and get a response, but also have the response be responsive if I filtered selections in the data.
The problem is that the API for Google Gemini Pro expects a nested block of JSON, and the generic Advanced Analytics Connector only sends a basic JSON block with field/value pairs. But my hero, Daniel Pilla, helped me realize that a Qlik Application Automation will accept the basic JSON field/value pair block and that I could then infuse the question and data into the URL call as needed for the Google Gemini Pro API.
Feel free to watch the video and skip all of the yapping I do after it. Or if you think it might be helpful and actually want to try, feel free to read the step by step instructions to give it a try on your end.
To test let’s create a brand new Qlik Application Automation
Be sure to set the Start block to Triggered and keep a copy of the information that it will display for you as to how to call the automation you will need that when creating the Advanced Analytics Connector inside of Qlik Sense momentarily.
Then add an Output block that will simply output “Yeah I did something”
Create a new Qlik Sense application and go to the Load Script.
Before adding our Advanced Analytics connector we need to add some sample data to our load script to test with:
Data:
Load * Inline [
Question#, Prompt
Question 1, "Human: What can you tell me about the Qlik Associative Model and why customers love it so much? Assistant:"
];
Choose Create new connection and then choose Advanced Analytics
Simply copy the URL from the Automation you just created, and then create a HTTP Headers entry for X-Execution-Token as shown and the token provided.
In the Response Table section simply input any table name value you wish for the Name of Returned Table and uncheck the box that says Load all available fields, and set the Table Field (JMESPath) to response and response.
You will see why based on the data in a second but for now just enter Question# as the Association Field and be sure the Send Association Field box is not checked:
Save your newly created Advanced Analytics connector.
Now choose the Select Data icon for your Advanced Analytics test connection
After you type in the name of our sample data table “Data” the connector will then show you what information will be returned. Notice in my image it’s the AnyNameYouWant table because that is literally what I input, your screen will contain the name you used.
Run the load script and you will see that you sent 1 row and you got 1 row back:
Keep in mind that we didn’t do anything special in our Qlik Application Automation yet, we are simply using this as a test to understand HOW the Advanced Analytics Connector works. So let’s go over to the Qlik Application Automation you created and see what we learned.
Click on your Output block and delete the “Yeah I did something” text that was being output previously:
Now from the dropdown for Data to output choose the right arrow icon for Output from start because we want to see what it actually contains:
Not surprisingly it shows us that it received a simple record. Now click the Toggle view mode icon so that you can see what I mentioned at the onset. It is the very simple JSON block with a simple field/value pair that is passed in:
With that knowledge untoggled the View mode so we see the array instead and type
“You asked for: ” then click the Prompt field and choose Select first item from list. (While you saw it knows we only passed 1 entry, it assumes the body can be an array. So we need to select the first one, which is all we would ever pass it.)
Instead of calling the automation from the load script again, lets build a simple Analytics sheet instead.
Simply add a Text & Image chart to your new sheet.
Now add a measure
And use the following string as your expression. The connectionname you use is the one you created and you can copy it right from the load script if needed to ensure you have spelling and spacing correct:
Your measure expression should be:
endpoints.ScriptEvalStr('{"RequestType":"endpoint", "endpoint":{"connectionname":"Advanced_Analytics"}}', 'Tell me a dad joke' as Prompt, 'Qlik Dork' as UserName)
The measure we added is evaluated and our Text & Image Chart now contains the response from our Automation:
Now let’s go back to the Automation and take another look at it. We know we were just forcing the Output value, it’s the Start block that we care about. Notice that now the Start block had a plain JSON body with both fields.
Now let’s add a Table object to our screen. Add the Question# and the Prompt as dimensions. Then add a measure with the following expression:
endpoints.ScriptEvalStr('{"RequestType":"endpoint", "endpoint":{"connectionname":"Advanced_Analytics"}}', Prompt)
Yeah … we can call our Automation as an SSE style Advanced Analytics connector and we know that we can pass fields from our actual data, or made up values as needed.
We know there is nothing super exciting about getting a return value that repeats your question. But this post is just the foundation for you to build upon, which is exactly what I did and what I refer to in my video. Who knows what you may try and utilize the Advanced Analytics Connector for and when you encounter a red screen error or something you will know just what the issue may be.
On my end, one Dan helped me know I could call my Application Automation from an Advanced Analytics Connector, the rest of my problem was resolved quickly. All I simply had to do was add the simple basic URL block to my automation and call the Google Gemini Pro API.
In a similar fashion to what it expects, their API returns a nested block. I was able to pull that value out and return it as the result.
A while ago, I conducted an internal research project with my colleagues @AlaneMiguelis, and Dr. @priscilarubim, aiming to validate the accuracy of a Two-Sample T-Test in Qlik by comparing it with the medical research gold standard: R. While we explored various hypothesis testing methods, we prioritized formalizing our findings on the T-Test. Recently, we decided to make this document available to the whole community.
The focus of our project was to assess whether the statistical results produced by Qlik matched those generated by R, particularly for the Two-Sample T-Test. Our methodology involved rigorous data analysis and repeated testing to ensure the reliability and consistency of our results. Through our detailed comparative analysis, we aimed to provide insights into the robustness of Qlik’s statistical capabilities. The code and dataset is available in a Git repository for those who want to reproduce the study.
Here is the abstract of our study. The full document can be accessed through the link at the end of this post.
ABSTRACT
Objective
The statistical determination of a large or small difference between two groups is not based on an absolute standard, but is rather an evaluation of the probability of an event.1,2 In the field of medical research, it is common to use statistical software for descriptive statistics as well as to perform statistical tests.3 However, most software provides ready-to-use functions, and the researchers have almost no information as to how those statistical tests are calculated inside those functions. This article evaluates the accuracy of two-sample Student’s t-test using Qlik analytics software. The gold standard used for this evaluation is the set of standard t-test functions available in R software, a widely used, robust, and reliable statistical software.5–7
Materials and Methods
The tests performed in this evaluation used a subset of Framingham heart study data. The dataset contains data on 4,434 anonymous participants, collected in three periods apart from each other by 6 years from 1956 to 1968. Five t-tests with 2 scenarios each were performed in Qlik analytics and in R and the results compared.
Results
In general, the results for multiple statistics obtained in Qlik analytics match the ones found in R for multiple scenarios: small and large sample sizes, small and large p-values, assuming and not assuming equal variance.
Discussion
Although Qlik analytics matches all statistics for t-test found in R, the p-value only matches up to four decimal points, which is concluded to be enough for testing hypothesis since the conventional levels of significance do not go lower than 0.1.
Conclusion
This research concluded that Qlik analytics can be used for two-sample t-tests in multiple scenarios.
Keywords: Qlik, t-test, r language, Framingham.
Link for the full article: Comparing Qlik Analytics and R Software for Accurate Two-Sample T-Tests Research Paper (ipc-global.com)
QUESTION:
How to display or hide one or more internal sheets of a one o more Qlik Sense App.
ANSWER:
For convenience, I use a simple INLINE table that reproduces 2 fields necessary for dynamic matching when opening the sheet: USER ID and SHEET ID
SA_SHEET:
LOAD * INLINE [
UTENTE,SHEET
55ad8d29ed671d80ecac0d1b4594546e8f88d730d23a63b4da55f51ed77f4ffb,8f3e7e83-00ef-4c34-9458-54ba3840c123
55ad8d29ed671d80ecac0d1b4594546e8f88d730d23a63b4da55f51ed77f4ffa,0af346e4-b0b3-4a20-b2ee-bf9e869412e2
];
// the green color is the correct record to match (ID USER and ID SHEET)
Add 3 variables:
the first in the load script;
LET USER = SUBFIELD(OSUSER(),'|',-1); // Extract the User ID session login ( one value only)
the second in variable editor:
ObjectId('sheet') // One value for each sheet in app
the third variable in the visualization condition of sheet properties:
=index(concat( {< UTENTE = {"$(USER)"} >} distinct SHEET,' '),$(vSHEET_ID))
the result will be that when the user ID and the sheet ID are equal to the user of the current session who opens the sheet within the App, the formula returns a value greater than zero, enabling the sheet.
These variables can be inserted more conveniently in an TXT file and loaded with an INCLUDE instruction so as to be able to quickly insert the code into all the published APPs (maximum efficiency) as for the INLINE table which can be included in the loading loads by hooking it if present , to that of ECTION acces for data profiling. Seeing is believing.
Good work
Valerio Fatatis wrote
By the time I’m writing this article Qlik Cloud Service does not support yet the GCP user group during the authentication phase. Thus allow GCP users to access Qlik Cloud, base on their groups is not (yet) an option.
This is expected to be a temporary situation and Qlik will deliver soon (or later) also this feature. Nevertheless timing is important and it may avoid the SaaS adoption for Qlik enthusiast with the hard constraint to relay on Google groups.
In the context above, in this article I'm going to propose an alternative way to achieve the same business scope granting users to spaces based on their membership groups. Therefore, acting on the Authorization instead, the Authentication. This solution is imperfect, widely improvable (anyone is welcome to contribute) and to some extent graceless, but it just works and sometime could make the difference between adopting Qlik SaaS or something else.
Any authenticated user (through GCP or any other solution) will land on Qlik Cloud creating a new user for their first access. This solution is triggered on that specific event “User Creation”. Qlik Application Automation is triggered on the User creation event, and go through the following steps
Here you can find the Google API documentation . The HTTP request retrieve a paginated list of users belonging to the same {groupKey}. This HTTP request needs an authentication method, for this you need to create a service account on the google platform.
Pay attention to paste the Private Key. It is made of 3 lines. The first line is the “BEGIN PRIVATE KEY” the second line is the key itself without any carriage return line feed, the third line is the “END PRIVATE KEY” line. You should end up with something like this:
-----BEGIN PRIVATE KEY-----
<Your private Key in one single line, remove all the CR+LF or /n>
-----END PRIVATE KEY-----
Private Key id from the variable
Private Key from the variable
Time as: {number: {date: 'now', 'U'}}
Select Python as language and paste these lines:
import jwt
iat = inputs['time']
exp = iat + 3600 * 1000
payload = {'iss': 'qlik-user-sync@qliksaasidp-377811.iam.gserviceaccount.com',
'sub': 'qlik-user-sync@qliksaasidp-377811.iam.gserviceaccount.com',
'aud': 'https://admin.googleapis.com/',
'iat': iat,
'exp': exp}
additional_headers = {'kid': inputs['Private Key Id'],"alg": "RS256","typ": "JWT"}
signed_jwt = jwt.encode(payload, inputs['Private Key'], headers=additional_headers,
algorithm='RS256')
print (signed_jwt)
For more details please check this page.
Remember to use as parameter the pageToken
And in the header the authorization Bearer as follow:
Here attached to this article you can find the Automation. Remember to replace the placeholder in the Variable Private Key Id and Variable Private Key with your own keys as well as the group in the two call url blocks.
"Hurray! There is an OpenAI Connector. Now what do I do with it?"
If that thought has been going through your head I totally understand and have created a series of 3 videos to help you get going. And never fear, these aren't hype videos. They are centered on a very real premise and use that involves the fact that "Qlik already provides industry leading #AI, so let's augment that instead of trying to reinvent the wheel."
In this video I will help you understand the various prompts and value ranges that are required to create an #OpenAI connection so that you are comfortable with the incredible flexibility offered.
Here is the link to the OpenAI API help page I referred to in the video: https://platform.openai.com/docs/api-reference/completions/create
In this video I go beyond just calling the OpenAI connection with a question, to passing data. Not my highly sensitive, albeit fictitious, sales data, because let's face it, your first use case probably isn't going to be pass your real sensitive data either. More importantly this video helps you appreciate the need to really understand what OpenAI and other Large Language Models do best. Hopefully, you appreciate this incredibly powerful use case that takes full advantage of OpenAI in a way that augments the #AI that Qlik already provides.
If you thought Video 2 was fun setting up the use case, you will really love this final video in the series as I show you how to actually implement the use case in a real way.
This document will help those how are looking for solutions for creating flags with ytd/mtd/qtd & rolling averages in qlik sense or Qlikview.
I used HIC's Concept As-of Table https://community.qlik.com/t5/Design/The-As-Of-Table/ba-p/1466130 to implement this document is extension with rolling averages and creating flags.
Thanks
Vikas Mahajan
How the DoD makes decisions is changing. Historical transaction reporting and static batch data analyses are giving way to predictive data science, streaming analytics, machine learning (ML), and artificial intelligence (AI). The DoD must embrace these new paradigms for more informed, real-time decisions to impact business, operations, and mission performance.
This whitepaper will address how Qlik can enable the DoD to achieve it's mission.
Here are some example show conditions. The second is if you have multiple different groups that shouldn’t have access to a given field.
=if(OMITGROUP=’SALARY’,0,-1)
=if(match(OMITGROUP,’SALARY’,’CONTACTINFO’,’ACCOUNTING’)>0, 0, 1)