Before you start reading: The way to import .json with Qlik Sense script is undocumented and may still change at any time without notice.

Qlik script natively reads JSON (JavaScript Object Notation), it is a bit tricky and slower for large files - and an import wizard is missing, which would create the script blocks.

When you click on Add data in the connection pane, the file dialog for the folder will show no specific filter for .json files:

Choose "All files" and then select your .json file. Next you will see, that the wizard attempts to interpret it as csv. Pointless, but at least you get the FROM construct properly filled. Forget the rest, as we will replace it in a minute.

Just hit "Insert script" and then put LOAD * in front of FROM, and instead of (txt ...) put (json):

data:
LOAD * 
FROM [lib://:Google_Drive/119UHVSR9_JjkT9o-mz2NAO1xyS1xU56f/01.json] 
(json);

Not too complicated so far. If your .json file only has one root object (or one root array of objects) and you will already get the keys from the top level. You are already done.

Now lets learn more about the parsing!

Simple JSON Object

A singe json file containing only one object ...

{"key1": 123, "key2": "abc", "key3": true, "arr": [], "obj": {} }

... will result in one line of data.

• Keys with simple values will be 1:1 loaded into a Qlik Sense field
• arrays and sub-objects will get a 16 char hash-value (I will explain below)

Multi-elements JSON File

If the json file has multiple "rows" (objects in an outer array [ ... ]) you automatically will get multiple rows in Qlik.

[ 
  {"key1": 123, "key2": "abc", "key3": true}
, {"key1": 234, "key2": "def", "arr": [5,6,7]} 
]

The arrays can have different keys. Common keys will concatenate in the same field, whereas keys missed out in a given 'row' will read "null" (unfortunately as text, not as Null()) in the result.

Now lets look, how to deal with sub-objects and arrays? For this we need understand the table is construct and the hash values.

• The root element has the pseudo-table name "Root"
• Every element in an array has an offset, starting with 0, even the outmost array. Hence, the first "row" above you would get with "Root/0"

• An object or an array will come back as a hash-value
• To get the object or array contents itself, alter the "table is" construct to address this key, e.g. in above example "Root/1/arr"
• If it is an array, you need to use the Pseudo key NoName to get the value and the key itself to get the hash value to link it to its parent

Importing JSON sub-arrays

That is fine for one row, but if you have a json file with many rows, you don't want to call every line with "Root/0/arr", "Root/1/arr", "Root/2/arr" ...

The good thing: You don't have to do it yourself. This article is explaining how to load .json by hand. If you want a quick solution, this one is for you:  https://github.com/ChristofSchwarz/QlikScripts/tree/master/json

Assuming a .json file with this content

[ 
    {"name": "Alex"  , "age": 50, "kidsAge": [25,23,7,4] }
  , {"name": "Dhruv" , "age": 27, "kidsAge": [1] }
  , {"name": "Eyal"  , "age": 35, "kidsAge": [] }
  , {"name": "Chris" , "age": 49, "kidsAge": [17,19] }
]

We will introduce a FOR loop now. Note, if an array is empty [] or is null, the LOAD command inside the Loop would fail on that "row", so we set ErrorMode to 0 (=continue script upon error) and reset it to the default 1 (=fail on error) after the NEXT command

SET vFile = [lib://.../filename.json];

root: 
LOAD 
    name, age, kidsAge AS %kidsAge
FROM [$(vFile)] (json, table is 'Root');

SET ErrorMode = 0;
FOR v = 0 TO NoOfRows('root') - 1
    kidsAge:
    LOAD DISTINCT 
        kidsAge AS %kidsAge, 
        NoName AS kidsAge
    FROM [$(vFile)] (json, table is 'Root/$(v)/kidsAge');
NEXT v
SET ErrorMode = 1;

The result is a nice 1:N relation resolved.

The fieldname "kidsAge" also exists on the inner LOAD (next to "NoName") and it returns - yes - the same hashvalue as in the outer, first LOAD.

Importing Json sub-objects

Last example is for a sub-object (instead of the sub-array): Similarily, you get hashes on the parent level and the content itself when addressing it right with the "table is" position. A FOR loop will ease things again, plus some more logic.

In this .json file we have one row where "kids" is null and the subobject doesn't always contain the key "happy"

[ 
    {"name": "Alex"  , "age": 50, "kids": {"count": 4, "happy": true } }
  , {"name": "Dhruv" , "age": 27, "kids": {"count": 1, "happy": true } }
  , {"name": "Eyal"  , "age": 35, "kids": null}
  , {"name": "Chris" , "age": 49, "kids": {"count": 2 } }
]

We can't count on auto-concatenate any more for the sub-objects (row 4 would end up in a different table than rows 1 and 2). Also, without error-tolerance, the 3rd row import would fail. So we go for this script, where we create an empty table for the "kids" and then explicitly concatenate into it. We have to use the LOAD * functionality, because the structure of the subobjects is not identical throughout the main array. (Assuming you set the vFile yourself)

SET vFile = [lib://..../filename.json];

t_Root: 
LOAD   // outer load
    *
FROM [$(vFile)] (json, table is 'Root');

SET ErrorMode = 0;
t_Kids: LOAD * INLINE [kids]; // start empty table

FOR v = 0 TO NoOfRows('t_Root') - 1
    CONCATENATE(t_Kids)
    LOAD DISTINCT   // inner load
        *
    FROM [$(vFile)] (json, table is 'Root/$(v)/kids');
NEXT v
SET ErrorMode = 1;

We will get now two tables, that are 1:1 related (or 1:1 or 0, to be precise)

 

Note: the inner LOAD * will return the "kids" as a field, too, containing the hash that links to the first table

The result can easily be used now in the Client

 

Uff, it can get tricky, but next week I will show you that all the manual steps (loop, field aliasing, concatenating) could be solved by a recursive SUB.

I hope though, that Qlik will also finish a wizard, that does that parsing on import like we have it for JSON objects received via the REST Connection or, likewise, as XML files are parsed.

Conclusion

Pros

• A surprise and already a good starting point for importing .json natively
• Everything can be imported using "table is" directive

Cons

• Quite some script logic needed to parse nested JSON objects
• Can be slow
• Still undocumented and may change in future

In many industries including education and healthcare, it is necessary to mask or de-identify certain sensitive fields for certain user groups but also be able to see the data in total for the purposes of comparison or benchmarking.

This can be achieved by setting a flag in the section access table for users to determine whether they should see the real data or a masked version of the sensitive fields. In the example below, the code is restricting some fields for users at the individual school level but allowing schools to compare their results to the rest of the schools in the data set.

Please refer to the attached QVF and remember that Section Access does not function in Qlik Sense Desktop and must be deployed to a Qlik Sense Server environment. I've also attached the QVW for the QlikView solution.

Notes

• Uncomment the commented section of the code
• Change userA, userB and adminUser to users in your environment
• The solution works for both QlikView and Qlik Sense.
• Consider aggregating the de-identified facts to reduce the number of records in the app.

userA from SchoolA will see just Teacher and Student for School A records ONLY. All other schools will have the words De-identified in the Teacher and Student columns:

Credit to my colleague Yaniv Feldman yfe‌ for this solution.

Matt Nunn

Qlik Consulting

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:

1. Open the desired APP and copy its app ID from the URL.
2. Open the SHEET you want to showcase and copy its sheet ID from the URL.

Take action:

1. Within the Management Console, navigate to "Web" and set up a Web Integration. Origin should match the host of your PHP/HTML file for iframe presentation (e.g., https://www.example.com).
2. In the Management Console, visit "Content Security Policy" and create a new entry with the "frame-ancestors" directive. Use the same origin as your Web Integration.

Step 5: Interacting with the JWT Endpoint

This thrilling step involves:

1. Sending a payload.
2. Signing the payload.
3. Outputting the JWT Token.

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.

1. Install node.JS: Follow the installation guide.

2. Install module uid-safe: Check out the installation details here.

3. Install module jsonwebtoken: Install it via this link.

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

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

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

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

1. Download Postman if you haven't already.

2. Create a new POST request in Postman, directing it to https://yourcloudtenant/login/jwt-session.

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

Monitoring of Tasks that reload Apps in Qlik Sense.
One or more tasks are scheduled on the Qlik server that automatically load the Apps,
The goal is to be able to query the status of a task with the help of REST API technology and integrate it with the PRTG monitoring system.

Step 1 -> Creation of a dedicated Virtual Proxy in Qlik QMC.

The documentation to be able to proceed is available from the following link:

https://community.qlik.com/t5/Official-Support-Articles/Qlik-Sense-How-to-set-up-JWT-authentication/...

Step 2 -> API Query Mode and Test.

Once the JWT virtual proxy has been configured and the validity of the Token has been verified as per the official guide, it is possible to proceed with the desired query.

NB. In this case we validated the token using a Service user who also runs all the dedicated services including the Proxy Service.

In this case we will monitor the correct reload of the tasks on the QLIK server, the Tasks are scheduled on the server and allow the App data to be reloaded.
When one of these tasks fails via API call, it is possible to have a json result with the related details inside.
Below is an example of an API call:

This is an example of a task we want to monitor:

A quick method to test if the call was successful is from the browser using the Mode Header extension for google chrome described in step 1.

https://chrome.google.com/webstore/detail/modheader-modify-http-hea/idgpnmonknjnojddfkpgkljpfnnfcklj

So for the call to be successful you need to pass the following parameters:
Where Header name: Authorization and Header value: Bearer followed by the previously generated and tested Token and finally the Xrfkey key.

This will be the result in Json format returned by the browser if the call is successful:
Specifically in PRTG what we are going to monitor is:

1. The name of the Task in this case "test"
   The status in this case"status":7 = Success
2. Task name "name": "Reload task of TEST TASK FAIL"
3. Start and end time date:

        "startTime": "2022-05-04T07:46:34.100Z"
        "stopTime": "2022-05-04T07:46:37.636Z"

3. Other information that we may integrate and otherwise see in PRTG.

NB. A table with Qlik Sense reload task status codes and descriptions is available from the following link

https://help.qlik.com/en-US/alerting/July2023/Content/QlikAlerting/system-alerts.htm

This is an example of the string JSON with ok API call.

Step 3 -> Task monitoring from PRTG.

Type of sensor used in PRTG.

How to create a sensor.

The path on the PRTG server where the template is to be uploaded is as follows:

C:\Program Files (x86)\PRTG Network Monitor\Custom Sensors\rest

In the following path, however, it is necessary to define a Lookups in order to allow the sensor to interpret the various states of the Qlik Tasks listed in Step 2.

C:\Program Files (x86)\PRTG Network Monitor\lookups\custom

Result JSON query Task from Browser a part of this information will be used for the sensor in PRTG each task will correspond to a specific sensor.

The REST Custom Sensor was used in PRTG.
The REST Custom sensor queries a Representational State Transfer (REST) application programming interface (API) endpoint and maps the JavaScript Object Notation (JSON) or Extensible Markup Language (XML) result to the sensor values the mapping rule must be available as a REST configuration file in JSON template format (*.template) according to the PRTG API definition for custom sensors.

                              Two files have been created on the PRTG server.

                               Final integration flow monitoring Task PRTG -> QLIK.

This video shows you how to add images to straight tables in Qlik Sense and Qlik Cloud. 

Environment

• Qlik Sense
• Qlik Sense Business
• Qlik Cloud

Transcript

Generative AI is here. 

Generative AI is there. 

Generative AI is everywhere. 

Welcome to life in mid-2023 my friend. Whether you are caught up in the hype that it will solve all of the worlds problems and make trillions of dollars for your organization or not, the facts are simple:

Your bosses boss wants it. 

Your boss wants it. 

You want it. 

In my previous post Creating, Calling and Implementing the OpenAI Connector I shared with you how to utilize the brand spanking new Qlik Cloud Connector for OpenAI. 

Before the ink was even dry, and I had time to celebrate, there were so many questions about whether or not the connector was going to be available for Qlik On-Premise/Client Managed solutions.

Then more questions arose about whether or not Qlik could connect to the other "9,390,029" Generative AI platforms or privately trained Large Language Models. 

ArterialTechnocracy - Created by @RuttenbergKyle and @foxtrotfrog using MidJourney

The Answer is a Resounding Yes

I responded that of course Qlik can connect to them because nearly all provide REST API's. Thus, you simply need to use the Qlik REST Connector. Easy for me to say, but it's not like the parameters for REST API's just build themselves. So I've created a few videos to help you get access to, what I know you NEED access to. 

Calling OpenAI via REST 

Nobody is sure what the question is, but they know OpenAI is the solution. Right? Well, you and I probably aren't falling for all of that hype, but we can agree that it will be a lot easier for your organization to start dreaming up use cases, once you know you can execute on them. 

In this video I walk you through all of the necessary REST API settings to successfully connect Qlik Sense to OpenAI via a REST connection. While the values I plug in are for OpenAI it's easy to correlate them to other Large Language Model (LLM) solutions including privately trained LLM's as well. 

Of course I also added some technical fun like making the calls via a Partial Reload so that you can let the end user input the prompt you want to to send OpenAI on the fly. 

Calling Hugging Face via REST

While OpenAI has all of the buzz, Hugging Face is also a very highly utilized and valuable Large Language Model provider as well. I figured it only made sense to demonstrate at least 1 alternative system being called via the Qlik Sense REST API connector as well.

The use case is a fun one, viewing a list of translation models available and choosing one and executing it to translate input text on the fly. In the previous video I demonstrated how to collect input prompts from the end user and call OpenAI via a Partial Reload. In this video, I go even deeper and demonstrate how you can debug errors that can arise during the partial reload. I mean, why not? You have to learn some time. 

Many thanks

As I shared in the Hugging Face demo I love Generative AI Art. What I also love are the art collectives where folks around the world collaborate on ideas. Prompt Engineering is an art form in and of itself. What you often see online are the completed works, but if you could see the pure prompt poetry that goes into them on the front end you would be equally amazed. 

The collective I am part of has a channel dedicated to prompts that are simply 1 or 2 words. The goal is to see how concise a prompt can be, and determine how creatively the Generative Art tools can respond. The opening illustration was produced by 2 of my friends in such a collaboration: @RuttenbergKyle and @FoxTrotFrog(on Twitter) came up with the term ArterialTechnocracy. Normally, I like to use my own generated images, but when I saw that image I knew I simply had to use it for this post. They were kind enough to permit me to do so. 

I've been writing code for 42 years. Two things I know are that while algorithms allow for creativity, the code you input must follow strict rules. I'm still learning from masters like @RuttenbergKyle and @foxtrotfrog when it comes to completely opening my brain to create words that don't even exist to try and convey concepts that otherwise might take 1,000 words. 

While I tagged them in the image description, I thought it fitting to share even more thanks here. Primarily to share my formal thanks, but also to encourage you to consider building your prompt prompt engineering skills. Generative AI tools, regardless of their flavor, all accept prompts. The better your prompts are as input, the more deterministic the responses will be as output. Or in this case, the more creative your prompt is, the more you allow for creativity as the output. 

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

Video 1 - Creating

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

Video 2 - Calling

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. 

Video 3 - Implementing

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. 

Topic  :YTD/MTD/QTD & Calculation of Rolling Average

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

Hello, everyone, hope you are doing great 🙂

I will divide this post in 3 parts: 1 - the problem to you understand when to use this, 2 -  the solution to show what you will have as result and 3 - how to do it in order to explain how you can do it.

The problem

Often I get business users asking to add some notes on the app to explain or alert the users about how to analyze their data properly. But sometimes doing that takes some precious space on the app. 

The solution

Create hidden messages that only appear when the user hover their mouse on some Qlik object.

We can do this creating tooltips using CSS on Qlik.

How to do it?

First step is to find the id of your object. You can do that by going oo your sheet in visualization mode, right click on your object -> Share - > Embed. Then you copy the object id:

After you have the id, you need to edit this css code correcting the OBJECT_ID (it appears twice) and the message on the CONTENT parameter. 

/* TOOLTIP ON KPI*/
[tid="OBJECT_ID"]:after{
visibility: hidden;
width: 80%;
background-color: #4477aa;
color: #fff;
text-align: center;
border-radius: 6px;
padding: 5px 10px;
margin: 10px;
content: "This is a personalized tooltip message for a KPI.";
left: 10%;

position: absolute;
z-index: 1000;
}
[tid="OBJECT_ID"]:hover:after{
visibility: visible;
}

Once you have corrected this. Add a multi-KPI on your dashboard and add a valid measure. 

PS.: You can hide this object with this CSS code: 

.qv-object-qlik-multi-kpi { display:none; }

The final step is to paste your css code on you multi-kpi object. You need to select your multi-kpi object and go to Appearence -> Styles -> Stylec (css). And then paste your CSS code.

Now when you move your cursos over your object, you will see the tooltip message. 

If you work regularly on Qlik Sense after a period of time you may have noticed a slowdown. After a while a published application can generate errors caused by:

• The continuous growth of data volume.
• RAM that saturates by the size of applications and the calculations made.

To give you a view of the state of your applications, Qlik has created an app: App (Metadata) Analyzer which is part of the Monitoring Apps group that allows governance of the entire Qlik Sense environment.

Monitoring Apps are a set of nine Qlik Sense applications allowing to have a governance of its Qlik environment through the Log files that are generated.   These apps retain historical data for trend analysis. By default, two Monitoring Apps are installed: License Monitor and Operations Monitor, other apps can be imported.

The App (Metadata) Analyzer provides dashboards to analyze the metadata of all your Qlik apps in your Qlik Sense Enterprise deployment. In other words, analyze the data that your applications are composed of: the tables and fields in each table.

Through the Analyzer App you will be able to know.

• The load time and size of all your applications.
• The location and owner of the applications.
• The RAM consumption of all your applications.
• The number of tables and fields present in each application as well as their RAM consumption.
• A color code to highlight items that require your attention based on your criteria.

This will ultimately allow you to

• Compare RAM consumption to the limit of your server.
• Highlight the most resource-demanding applications/tables.
• Optimize your apps
• Detect anomalies.

And therefore, be proactive in the maintenance of your applications.

The App (Metadata) Analyzer is available on Qlik Sense Enterprise On Premise and also available on the cloud: Qlik Sense Enterprise Saas and Qlik Sense Business SaaS.

For more details you may refer to the below video (Qlik App Analyzer demo) by Qlik : 

https://youtu.be/GawDAkZjFoQ

I spent a bunch of time on and off trying to figure how to generate an email alert when a reload fails. It's something I and I'm sure many others wish was built-in and worked out-of-the-box, but it's actually somewhat possible. There are some restrictions - for me, I sometimes get reloads that hang, even though I put in a max retry, and since they don't "fail," I never get the notification. And obviously, the email notification is generalized to the point where you have to log into the QMC to discover which app failed. But if you're a Qlik Sense admin, you're smart enough how to make customized email alerts for each reload task :).

Setting up Qlik Web Connectors

1. If necessary, whitelist your Qlik Sense server’s IP in your mail relay.
2. Download the QlikWebConnectors zip folder from the Qlik support site: https://us-d.demo.qlik.com/download/ 
   1. Filter down to "Qlik Connectors" and under the "Products" section, scroll down to find "Qlik Web Connectors."
   2. Find the most recent Initial Release and click on the name to download - March 2020 at time of writing.
3. Extract that folder to the root of the C drive. Rename the folder to QlikWebConnectors if it is not already named so.
4. You may notice no installation has ran yet. We will get back to that later. If you open the QlikWebConnectors folder, you can run QlikWebConnectors.exe to start the program. A window will appear in the bottom right of your screen.
   
    

   

5. Click "open in default browser" to begin setup. Accept the license agreement.
6. While you can technically write out the script directly in a Qlik Sense app, a sanitized version of which I will provide below, the following is how to set up through the SMTP Connector.
7. There are a lot of Connectors available. Browse to the "Standard" tab and open "Qlik SMTP Connector." Highlight SendEmail and click Parameters.
8. Set up the fields according to how your mail server requires. Mine is set up to allow anonymous authentication but only whitelisted servers are allowed. Below is how mine is set up, but yours may be different.
   1. SMTP Server: FQDN of your mail relay
   2. Port: usually 25
   3. SSL Mode: depends on your setup - mine is anonymous so I set to "None"
   4. To: email you want to receive the email alerts
   5. Subject: email subject
   6. Message: email body
   7. HTML: check this box so you can use HTML tags in your Qlik script
   8. From name: display name the email is being sent from
   9. From email: email address the email is being sent from
9. Click "Save Inputs & Run Table." On success, the To Email you entered above will receive the email.
10. Click over to the "Qlik Sense (Standard Mode)" tab. You will see some instructions and some auto-generated code. Copy it to the clipboard.
    
11. Create a new Qlik Sense app titled something descriptive (e.g. "Send Email"), open the Data Load Editor, and paste the code into the Data Load Editor. You may get a warning that the app has no data, which is expected and can be safely ignored.
    
12. Follow the instructions highlighted in red to set up the Web File connection. It should look as below. Give it a descriptive name as well. Make sure you create this as the Web Connector program is still running on the Qlik server.
    
13. Make sure the variable "vQwcConnectionName" matches the name of the email connection you just created.
    
14. Click "Load Data" to test the email and make sure it gets sent to the email you set above.
    
15. The obvious quirk of this set up is it requires the WebConnector program to be running constantly. Luckily, QlikWebConnectors can be set up to run as a service.
16. On the Qlik Sense server, browse to C:\QlikWebConnectors\BatchFiles.
17. Edit "Install Service.bat" and make sure the .NET and QlikWebConnectors_SVC.exe paths are valid.
    
18. Close Notepad and run the "Install Service.bat" as an admin. When the script has completed after a few seconds, press any key to close.
    
19. Open Services.msc, find Qlik Web Connectors, and change the Log On to your Qlik service account. You may otherwise run into errors in the future if you try to re-setup any connectors.
20. Start the service.
21. Right-click, Properties, and switch to the Recovery tab. Change the "First Failure" dropdown to "Restart the service." Change "Restart service after" to zero minutes. Click Apply and OK.
    
22. If you try to run the QlikWebConnectors.exe program again, you will get an error that the program is already running.
    

Setting up Task Failure Triggers

1. Log into the QMC.
2. Browse to Tasks either from the Dashboard or the sidebar.
   
   
3. Create a new task, name it something descriptive, and select the Send Email app you created above as the app. The email sends within seconds so it is recommended to lower the default timeout and increase the retries, though this depends on your environment.
   
   
4. Click the "Actions" dropdown and select "Create new task event trigger."
   
   
5. Name the trigger task something descriptive relative to the app, click "Add Task," change the dropdown to "Task Failed," and select the task you want to be alerted on a failure of. You can leave the time constraint as the default, it is not relevant when a task chain is only two tasks long, as is the case now. More information here: https://help.qlik.com/en-US/sense/June2019/Subsystems/ManagementConsole/Content/Sense_QMC/create-reload-tasks-from-tasks.htm
   
   
6. Repeat this process for each individual task you want to be alerted on. You can add a new Send Email app per app, new trigger event per Send email app, or new task per trigger event. I recommend the second for the sake of cleanliness and succinctness.
   
   
    
7. Make sure this trigger event process is repeated for each new app that is published in the future.
8. When you go to the Tasks page in the QMC, you will notice little chain symbols for every app that is connected to the Send Email app and vice versa. You can click on this chain symbol to view the task chains from either direction.
   

Editing the Notification App

As written above, the actual script that runs the SMTP Connector is complete and already published to Qlik Sense. In other words, if you ever need to edit or re-publish the app or change the SMTP settings, there is little to do. Details below:

1. When you set up the SMTP Connector and run the parameters, you will be given the below line of Qlik script. Notice how hard-coded the (unredacted) bold parameters are.
   
2. Of course, hard-coding is not best practice and the syntax is not obvious at first glance. The bold parameters are replaced by variables in the actual published Qlik Sense app.
   
   
3. Variables, when placed in the URL script line, must be in the form of $(variablename). If you look closely, every parameter is simply "parameter1=$(variable1)&parameter2=$(variable2)&..." The parameters from the original script block are untouched while the hard-coded values are now variables.
   
   
4. The script blocks are separated into sections, marked by comment sections. To go through them one-by-one:
   1. The first section is for script neatness. vQwcConnectionName pulls the webfile defined above into the script. vTimestamp is used for the email body.
      
   2. These variables are the content that you will edit. The two "...Input" variables define the content of the email body and subject. More on them in the next section. The rest of the variables are what they sound like.
      
   3. I would strongly recommend never touching these lines. The URL script line in Qlik Sense requires plus signs in the place of spaces for each value. These two lines replace the spaces with plus signs for you so editing the value is easier.
      

[Redacted] QS Script for Send Email app

/**** you shouldn't have to modify these after setup ****/
let vQwcConnectionName = '[web file connector]';
let vTimestamp = timestamp(now());

/**** Edit these variables to change the email contents. ****/
let vMessageInput = 'Qlik Sense Task Failure at ' & '$(vTimestamp)' & '.<br><br>To Review, <a href="https://[Qlik Sense URL].com/qmc">go to the QMC.</a>';
let vSubjectInput = 'Qlik Sense Task Failure';
let vSMTPserver = ' ';
let vFromName = ' ';
let vSMTPPort = '25';
/**** use %40 in place of the @ symbol ****/
let vToEmail = '';
let vFromEmail = '';

/**** DO NOT EDIT ****/
/**** These variable text strings properly encode the text in the "input" variables ****/
/**** Replaces all spaces with plus signs in the message string above ****/
let vMessage = replace('$(vMessageInput)',' ','+');
let vSubject = replace('$(vSubjectInput)',' ','+');
/**** DO NOT EDIT ****/

SMTPConnector_SendEmail:
LOAD
    status as SendEmail_status,
    result as SendEmail_result,
    filesattached as SendEmail_filesattached
FROM [$(vQwcConnectionName)]
(URL IS [http://localhost:5555/data?connectorID=SMTPConnector&table=SendEmail&SMTPServer=$(vSMTPserver)&Port=$(vSMTPPort)&SSLmode=None&to=$(vToEmail)&subject=$(vSubject)&message=$(vMessage)&html=True&fromName=$(vFromName)&fromEmail=$(vFromEmail)&delayInSeconds=0&ignoreProxy=False&appID=], qvx);

// IMPORTANT: If, when loading the above script, you receive a 'Script Error' Dialog box with a 'Field Not Found'
// (or other) error, the first thing you should do is copy and paste the full request URL (i.e. the URL between the square [...]
// brackets IN THE QLIKVIEW / QLIK SENSE SCRIPT ERROR DIALOG) into a browser address bar and check the response.