Use Case: We usually update the following in the QMC GUI. This causes problem at times when there is no RDP access is present to the server.

1. License Value
2. Host Allow List in Virtual Proxy for SAML
3. SAML Users to be setup with admin roles etc.

Steps do it in automated way without QMC GUI:

1. Keep the repository database up and running post installation
2. Open powershell
3. Go to the Qlik Sense installation path (default is C:\Program Files>>Qlik>>Sense>>Repository>>PostgreSQL\<version>\bin)
4. Connect to postgres database using the command ".\psql -h <hostname> -U <username> -d QSR -p <port number>"
   1. Example: .\psql -h localhost -U qliksenserepository -d QSR -p 4432
5. Type postgres command \dt in powershell prompt QSR=> and press enter to know the table names in current user
   1. \dt *.* will list all tables in postgres database across all schemas
   2. \d "<tablename>" will provide the metadata of the table
6. Related to Users

   1. SELECT * from "Users";-----To know all the users

   2. UPDATE "Users" SET "RolesString"='RootAdmin' where "Name"='<value>';-----To update the role of an user
7. Related to SSL Thumbprint

   1. SELECT "SslBrowserCertificateThumbprint" from "ProxyServiceSettings";

   2. UPDATE "ProxyServiceSettings" SET "SslBrowserCertificateThumbprint"='<value>';----To update the thumbprint value
8. Related to License
   1. SELECT "Lef","Serial","Name","Organization" from "Licenses";
   2. UPDATE "Licenses" SET "Lef"='<value>', "Serial"='<SerialNumber>', "Name"='<value>', "Organization"='<value>';
9. Related to HostAllowList

   1. SELECT "WebsocketCrossOriginWhiteListString" from "VirtualProxyConfigs";

   2. UPDATE "VirtualProxyConfigs" SET "WebsocketCrossOriginWhiteListString"='<value>';---to whitelist the domains/ips
10. Restart all Qlik Sense Services

The information in this article is provided as-is and will be used at your discretion. Depending on the tool(s) used, customization(s), and/or other factors, ongoing support on the solution below may not be provided by Qlik Support.

Environment

• [Qlik Sense May 2025]
• [Qlik Sense November 2025]

In a previous post called Calling Snowflake's Cortex Agent API, I started the post by saying the focus of the post was about how the REST API worked not how to create the connection to it. This post is focused solely on how to create a REST API connector for Snowflake Cortex Agents. 

Snowflake Agents

Snowflake allows you to build "agents." While I have built several, this post is going to focus on one that I called "SynthAgent." 

They also provide a way you for you to question them directly in their user interface. I can simply ask a question like "Who are the top 10 admitting providers?" and voila ... I get an answer:

Actually as my previous post indicated, when you talk to an agent you actually get to see it's entire stream of thought. 

Snowflake Cortex Agent API

While asking an isolated question inside of Snowflake is certainly handy-dandy, the bigger picture is in the fact that in the world we live in, agents should play nice with others. So, Snowflake created an API that allows others outside of the Snowflake user interface to speak with their data. 

Obviously you will need to use your own server and your own agents, but to help you understand the URL I wanted to show you the URL I will use in this post for this Cortex Agent API. Refer to the first image to see the database/schema/agent:

https://AYFR...mputing.com/api/v2/databases/SNOWFLAKE_INTELLIGENCE/schemas/AGENTS/agents/SYNTHAGENT:run

Creating your REST API Connector

While it's a rather complicated URL path, it is in fact nothing more than a URL path. Which is the most important thing that the Qlik Rest API Connector needs. Begin by creating a new connection. Be sure and choose the correct space where you want this new connector created and simply choose "REST". 

Paste in the correct URL and choose the POST method:

After choosing POST as the method, the connector is going to prompt you for the BODY of the call. 

My agent is built from a Snowflake Semantic View. Based on that, here is the sample body that I passed to my SYNTHAGENT URL: 

{
"messages": [
{
"role": "user",
"content": [
{
"type": "text",
"text": "Who are the top 10 admitting providers?"
}
]
}
],
"tool_choice": {
"type": "auto"
}
}

Notice that I'm asking the exact same question that I asked directly in Snowflake so that I can test the results I get back to ensure it is working. 

Additional request parameters

Based on the Snowflake Cortex Agent API documentation, you will need to configure a parameter and a few headers that are passed when calling the URL. 

Query parameters

You need to have a parameter named Response-Type with the value text/event-stream

Query Headers 

You need to create 4 Query Headers.

Authorization - This is to define the security for the API call. Snowflake provides multiple types of security. In this example I'm using a token model. To create your API Token, called Programmatic access token (PAT) in Snowflake you simply go to your profile, go to Settings and choose Authentication. Then Generate new token. 

When you create the PAT, simply copy the value and in the value field type Bearer and then paste in your PAT. 

User-Agent use QlikSense.

Accept simply use */*

Content-Type use application/json

Checking the box that isn't there

You had to hard code your question in the body, but in the real world in which you live are you always going to make all users, always see answers for just that question?"

This is when you need to reply out loud in your biggest voice "OF COURSE NOT!!!!"

Then I would say "So check that box that says Enable Dynamic Parameters" or any of the alternatives shown:

Because any of those check boxes would make total sense to you "Oh I need to check the box that allows me to change the question based on what my user asks." 

Instead I need you check the box that only makes sense, after you know what it does. It's the check box that says Allow "WITH CONNECTION"

It will make sense in a few minutes why the button has that name. For now just now it relates to any of the 3 button names I wish it had. 😁

Test your connection

Finally give your connection a name and press the Test connection button. 

If all of your security is configured correctly in Snowflake (Click here to see all of the things you need to ensure in Snowflake itself.) then you will get a Connection succeeded result. 

If you see an error like "Unsupported Accept header null is specified. It simply means that you followed instructions from some other post and left out the Accept */* that I showed above an retest the connection. 

Once you see the Connection succeeded save your connection so that we can actually make the call to the agent and see how the results look. 

Calling the Connector

Congratulations you know have a connector in place to call your Snowflake Cortex Agent REST API. Woohoo. 😎

Click the Select data button for the connector

It will default to CSV for the response type which is perfect. 

The Delimiter should be Tab. 

Check the box for CSV Has header.

Check the CSV_source box. 

Once you check that box it will go and make the call. If you see results like below you know that have in fact successfully asked your Snowflake Cortex Agent that question that was hard coded. 

Press the Insert script button to actually insert the script block into your load script. Isn't this great I can reload my application and get answers to the same question over and over and over again. We are really living large. Right?

Our connector is working great, but I'm still hung up on that whole "same question over and over and over again thing." Logically what we would like to do, and more importantly can do ... is call the REST API and give it the question on the fly. In other words we would need to give it the BODY for the the call on the fly ... like this:

WITH CONNECTION

WITH CONNECTION is the syntax key word that is added to calls to REST API Connectors. Now I get why that checkbox that didn't make sense is called Allow WITH CONNECTION. It is letting you declare when you create the connection if the developers with access to the connection are allowed to do that or not. Glad they didn't rename them with any of the names I suggested. We would have a nightmare on our hands. 

Now I have the ability to create a variable with the body, that has whatever question I want in it:

 

Results

When you look at the results you will realize that the Snowflake Cortex Agent API does not just return the answer to our question. It returns an entire event stream. Which is precisely why when we set the Response-Type parameter initially, we needed to set it to text/event-stream. 

 

Great news is you have successfully created your REST API connector so you can talk to the Snowflake Cortex Agent. The next step is to check out my previous post where I walk you through how to process the results. https://community.qlik.com/t5/Member-Articles/Calling-Snowflake-s-Cortex-Agent-API/ta-p/2535284

PS - This post was planned for a later date. Be sure to thank @chriscammers for requesting it sooner. 😃 

 

 

Snowflake recently released what it calls Snowflake Intelligence. It's their User Interface that enables users to directly ask questions of data. Under the covers their interface is interacting with a new Snowflake Cortex Agent API. 

Qlik is an official launch partner with Snowflake for this exciting technology as we are able to call that Snowflake Cortex Agent API just like they do. Which means you are able to present visuals to aid with insights, while at the same time allowing end users to ask questions and then present the results that the Cortex Agent API returns. 

The intention of this post, is to help you understand the nuances of the Snowflake Cortex Agent API. 

Calling the Snowflake Cortex Agent API

Calling the Agent API is super easy. You simply use the REST Connector provided to you in Qlik Sense. Either Qlik Sense Enterprise on Windows or Qlik Talend Cloud Analytics. You will want to ensure you check the Allow "WITH CONNECTION" box so that you can change the body. 

To get the REST connector to build a block of script for you, ensure that you set the Response type  to CSV and set the Delimiter to be the Tab character. 

Eventually you will modify your script to be something like the following where you set the Body to be the question your user wants to ask, rather than it be hardcoded. But who cares?

There is nothing special here and nothing worth writing about that I haven't already covered in other posts. The reason for this post isn't about the connection itself... it's about what the Snowflake Cortex Agent API returns.

Rather than returning a single response, it actually streams a series of events. Notice in the image above to load data from the connection what the results look like. It literally returns the entire "stream of consciousness" if you will, as it is working. Everything it does. 

Handling a Streaming API

It would be an exercise in futility if I simply talked my way through how to handle a Streaming API in general, and especially how to handle this even stream from Snowflake Cortex Agent. So, while I won't be walking you through all elements of the connection, or how I build the body based on what the user asks as a question ... I do want you to be able to be hands on. The following image illustrates how I used my connection (that does work) to get the event stream and store into the QVD file that is attached to this post. 

You will need to:

• Download the CortexAgentStream.qvd file
• Download the Cortex Agent Event Stream.qvf application
• In your Qlik Sense environment upload the application
• Open the application and go to the load script editor
• Choose the space you want to use to store the QVD file and then open the Datafiles area and drag/drop the CortexAgentStream.qvd so that it is in your environment. 
• Go to the "Load the entire event stream" section and change the library path where you stored the CortexAgentStream.qvd file. 
• Go ahead and press Load data. Don't worry

If you did all of these steps correctly you should be told that 487 rows of data were loaded. 

Go to the Event Stream sheet and see all of those wonderful 487 rows that were returned when I called the Snowflake Cortex Agent with the question that I passed it. 

Be sure and scroll through all of the rows to really appreciate how much information is returned. When you get to the bottom there are 2 rows that I really want you to focus on. You see all of the other events are simply appetizers for the main course we will focus on for the remainder of this post. They are merely events that let you know things are happening and then the stream says "Hey wake up now here is my official response" in rows 482 and 483. 

Now what you need to do is row 483 so that the text box on the right will show you the full value that is returned for the response event. 

Response

I'm not going to lie ... the first time I saw that I was a little bit intimidated. Sure seemed to me like the wild west of JSON data. In fact ... I ended up writing a series of posts I called Taming the Wild JSON Data Frontier just to document the process I had to go through in parsing that beast. Be sure you read each of the posts that is part of this so that you have the chops as a data sheriff to deal with this incredible structure. 

One thing you should know, if you don't already, is that JSON can be very compact like you see in the response. Which is great for exchanging/storing all of the data. But it is really really hard to understand. I highly recommend you take advantage of any online JSON Formatters that you can find. I use jsonformatter.org. You simply hand it the compact JSON structure, and ask it to format/beautify it .. and voila it becomes much more human readable. 

{ I have attached the output in a text file that you can download and view for the remainder of this article if you don't want to take the time right now to actually copy and the beautify the response. }

But I digress the important part is that you now know that the RESPONSE event is the one you care about and that the DATA associated with the RESPONSE has a massive JSON structure that contains all of the information we need to present the response back to the user. So, let's dig in. 

Go ahead and return to the load script editor and move the section named "Get the RESPONSE event data" up above the Exit script section so that it can actually be processed. 

Before seeing the code you may have thought "There is no way I'm going to be able to magically figure out how to identify the data for the response event." But as usual, Qlik Sense provides some very easy transformations. Logically we want to only pull the data from the entire event stream, if the event before it is "event: response" so that's exactly what we ask to do by using the Previous() function. We don't care at all about the part of the column that has the phrase "data: " in it, so we simply throw it away. 

Go ahead and reload the data, now that this section will run and when it completes check the preview window and sure enough ... we have exactly what we want in our JSON table.

Content

If you look at the prettified view of the response data you will see that at the highest level it contains a field called content that is an array. 

If you scroll all the way the pretty content you will see that it's actual an array of heterogeneous, or mixed type, objects. Meaning some of the array elements are thinking, some are tool_use, some are tool_result. And to make it worse the tool_result elements aren't even the same. 

If that sounds nasty ... don't let it bother you. Again, the entire reason for that series of posts I've already written was to help walk you through all of the types of JSON data that will need to be parsed. To understand the next part of the code be sure to read as well as the posts it points you to.

Parsing: Heterogeneous (Mixed) JSON Arrays

Go back to the load script and move the "Mark the Content we care about" section above the Exit script section and reload the data. 

Before I discuss the code, go ahead and preview the Content table to ensure you have the 9 different Content values that were in the array. One of the tool_use rows will have the Question_Record marked to yes and the tool_result record will have the Results_Record marked to Yes. 

Logically we do a 2 part load. The first iterates through all of the elements in the content array and pulls out just that elements content. The preceding load that takes place simply uses an Index function to know if the word "charts" is contained in the record and marks a flag accordingly. If we parse a nested set of JSON values from the record and find the question value, then we set that flag accordingly. If you haven't already read the posts I've been begging you to read ... then stop and read them now. That's an order. 😉

Why that Content?

The logical question you might have at this point, since I left you hanging, is why in the world I focused on flagging those particular rows of the content array? To understand imagine the end user asking a question of a magical black box that mysteriously just goes off and returns an answer. You've probably heard me say more than once "you can't act on data that you don't trust."

To that end, the Snowflake Cortex Agent API will return the question, as it got rephrased by it's generative AI and it also sends the result SQL that was generated. We just have to look for it in the pretty version of the response. Suddenly the mysterious black box, becomes more transparent. Which is exactly what I want to do ... report to the end user as well as audit the question and the sql. 

The results flag is set because the Snowflake Cortex Agent API literally hands us the information we need to create a chart with the results. I'm not kidding. It literally gives us the title for the chart as well as the dimension and measure fields for the chart, then it gives us the values for them. You gotta love that. 

Question and Results

Now that you understand what is returned and why I flagged it, let's look at how we pull all of that wonderful information out of what initially seemed like an undecipherable JSON mess. Go back to the load script editor and move the "Get the Question and the Results" section above the "Exit script" section and then reload the data.

We start building the Response table by simply reading the row in the Content table that has the Question_Record flag set to Yes. Getting the Question and the SQL statement to share to the end user is simply a matter of reading the nested JSON path for their values. 

Then we need to add a few columns to the Response table, which we will get by reading the row in the Content table that has the Results_Record flag set to Yes. Again pulling the information we want is simply a matter of reading the nested JSON path for those values. 

Now that you have reloaded the data to run this, and understand it ... it is time to check out the Preview panel for the Response table. We almost have exactly what we need to present to the end user. We have the Question_Asked, the SQL that was used within the Snowflake Cortex Agent, and we know the Dimension and Measure field names. Finally we have a JSON array of the values. 

Parse out the Values

Now that I know you have read the posts I mentioned, I should be more precise: "Finally we have a JSON Array of Homogeneous Objects." Which is covered in the Parsing: JSON Array (Homogeneous Objects) post. 

Go back to the load script editor and simply drag the "Parse out the Values" section above the "Exit script" section and reload the data. 

The first thing we need to do is pull the DimensionField and MeasureField names into variables that we can refer to. All we need to do is use the Peek() function. 

As you are familiar by now parsing a nested JSON structure is a simple matter of using the JsonGet() function with a pattern of:

• JsonGet(JsonStructure, '/field name')
• JsonGet(JsonStructure, '/entity/field name')
• JsonGet(JsonStructure, '/entity/entity/entity/field name')

Which is straightforward when you know the "field name." In the case of pulling the values out that we will need to present to the end user we don't know what they are. The very nature of what we are doing is asking the Snowflake Cortex Agent a question that the end user will give to us. It will then magically process that question and respond to us. Which is why we needed to extract the field names to variables. Now we simply iterate the array and parse the values by passing the variables.

Structured Data

Now let's check the work by looking at the preview for the Values table we just created. Unless I'm missing something you just rocked the world by converting an event stream of JSON structures into a structured table that is now contained in memory. 

How cool would it be if ... Never mind that's crazy!

But it would be cool if we could ... It would be really hard. 

Maybe we could so let's talk about it. 

Since we do have this data in memory now it would be so cool if we could visualize on the screen for the end user. Right? Forget my event stream of consciousness in getting here .. but it's not really that hard. Go ahead and go to the "View the Results" sheet and you will see something magical. 

Go ahead and edit the sheet so that you can see how I created that bar chart. Check that out I simply used those 2 variables that we created and did that hocus-pocus dollar sign expansion magic on them. You gotta love that. 

Art of the Possible

Of course I am going to load data into Qlik Sense so that business users can gain insights at the speed of thought. But let's face it ... I'm lazy and didn't talk to every user about everything they would ever want to know about their data. As a result I didn't build every conceivable chart to show them the answers.

Invoking the Snowflake Cortex Agent lets the users ask questions. Questions that we might not have a chart for yet. Questions that might involve scenarios that would be over the users data literacy or training level to get at. 

Oh sure I had fun doing all of the techno mumbo jumbo and sharing that with you. But by invoking it right inside a Qlik Sense dashboard I've now given business users the best of both worlds. Not only can we present their answer to them in a chart, since the values are in-memory it is associated to all of the other data. Meaning business users can interact with the values, all the other visuals will respond and naturally we can take advantage of that green/grey/white experience. While also providing the ability for them to see the aggregated answer they were looking for, but also immediately allowing them to see all of the other details they may need to follow up. Like details of the visits, provider names etc. 

Organizations face rising pressure to deliver analytics-ready data rapidly, reliably, and at scale. While platforms like Qlik Talend Cloud (QTC) and GitHub offer powerful capabilities for Continuous Integration and Continuous Delivery (CI/CD), tools alone are not enough. High-performing data teams require the right project management discipline, data architecture, and team structure to ensure predictable, high-quality outcomes as complexity grows.

This document outlines best practices for preparing an organization, configuring GitHub, and structuring QTC environments to enable efficient, governed, and scalable data delivery. It also provides a detailed walkthrough of a multi-team Medallion-architecture project implemented across two Sprints.

 Key Benefits of a Well-Designed CI/CD Framework

• Faster, more reliable delivery of analytics features
• Improved data quality through structured governance
• Higher team productivity and reduced rework
• Clean, high-quality data that accelerates AI and analytics adoption

The image above depicts a JSON Version 2 structured array. Rather than repeating the column/field names over and over in pairs with the data, they present the fields and the data separately. 

JsonGet()

When I first encountered this structure, I asked myself "How in the world am I supposed to read the data into a structured placeholder?" I couldn't find any type of JsonGet example where it said "read this data array and just use your esp to know what the field names are supposed to be. 

With any problem like this where the answer doesn't seem obvious, my recommendation is to just get started with what you can achieve ... so I did. I started by separating out the information that I could with the basic JsonGet field/value pair syntax. 

After that I had the information that would be needed, broken up into digestible pieces. 

Field Names

Then I started with the field names. Notice that it is simply a JSON Array of Homogenous Objects and we already know how to deal with those by sprinkling on a little of the Qlik iteration magic:

Voila ... we have a table of the FieldValues so we know the names of each of our columns. 

Visualize the Solution

That was the easy part. I still wasn't sure how in the world I create a table where those were the columns and the data values would be the ... well ... the data values. So, I tried to visualize what I was looking for by adding the following as comments in the code itself as a reminder/muse. 

SHERIFF_BADGE, TOTAL_BULLETS_USED

1307919, 3221

1617792, 2690

Then a crazy notion hit me ... that looks exactly like what I would do for an INLINE table. 

EXACTLY LIKE AN INLINE TABLE. 

So .... why not build it as an inline table?

What I wanted was something like this ... 

vCortexHeader

Obviously I needed to build the Header variable first. A little housekeeping first to set some variables then I just needed to loop through however many columns/fields the structured array might have. In my real case, the number of fields was more than 2, but I shortened them to help you track the solution. The logic works regardless of the number. If it is the first field, then set the vCortexHeader to the name of the first field. If it is not the first field, then update the vCortexHeader so it equals the previous value, and add a comma, then add the name of the next field. 

Voila ... the header for my soon to be inline table of values. 

vDataValues

At the beginning of this post I showed what the ResultData looked like as part of the overall Response table that was constructed using the simple JsonGet function. I've expanded here so you can focus on just it ... notice that other than some extraneous characters it is literally in the format we need for an INLINE table. 

If you take out the open/close square brackets "[" "]" and the double quotes ... the data is right there. We already know from previous posts how to use the JsonGet function to get row 1 (which is 0 offset) and get row x .... 

So, then it is just a matter of removing those double quotes and square brackets:

Putting it all together we pretty much do what we did with the field names, except this time we added carriage return line feed characters before rows 2 through x:

And we ended up a vDataValues variable that looks like this:

Practice

If you are anything like me you don't like to assume anything, and you are a visual or experiential learner. So, go ahead and download the attached WildDataFrontier.qvf that is attached, upload it to your environment and open the load script. 

{ Notice there are multiple sections. Each of them will pertain to a separate article and for this article the section named "7 - Structured Array" is the one you want to have at the top of your script. }

Edit line 49 that had 2 data values and a third data value , ("7777777", "777") and modify the numRows value from 2 to 3 like this:

'{"table": {"result_set": { "data": (("1307919","3221"), ("1617792","2690"), ("7777777", "777")), "resultSetMetaData": {"format": "jsonv2","numRows": 3, "rowType": ({"length": 0,"name": "SHERIFF_BADGE","nullable": true,"precision": 38,"scale": 0,"type": "fixed"},{"length": 0, "name": "TOTAL_BULLETS_USED", "nullable": false,"precision": 18, "scale": 0, "type": "fixed"} ) }, "title": "Top 2 Sheriffs for number of bullets used"} }}'

Since this solution involved a lot of variables, feel free to use the Debug mode and set breakpoints where you want them so that you can see the values as they are set. Or simply just reload the data after your changes and check out the values in each of the tables created so you can confirm what was done. 

Definition:

Returns the number of dimension columns that have non-aggregation content. i.e. do not contain partial sums or collapsed aggregates.

A typical use is in attribute expressions, when you want to apply different cell formatting depending on aggregation level of data.

This function is only available in charts. For all chart types except pivot table it will return the number of dimensions in all rows except the total, which will be 0.

What does it mean?

We have Table with 4 dimensions(columns): Product,Category,Type,Sales

Now we want to create Pivot Table by using those Dimensions.

We are going to use only 3 of them(Product,Category,Type) and use 4th(Sales) in our expression.

The result is shown below:

This Pivot Table  has 3 dimensions so its maximum dimensionality is 3.

For better understating please see  table below.

The function is used to show on which dimensionality level each of the Pivot Table row is:

'Sugar' has dimensionality of 1 which is Total for that 'Product'.

'Salt' has dimensionality of 2 which is Total for each 'Category' of that 'Product'.

'Oil' has dimensionality of 3 which is single value for each 'Type' of the 'Product's' 'Category'.

So then more Dimension we use the greater dimensionality of our Pivot Table is.

Practical use:

1) To show the level of dimensionality:

Expression:

if(Dimensionality()=1 ,RGB(151,255,255),if(Dimensionality()=2 ,RGB(0,238,0),if(Dimensionality()=3,RGB(255,130,171))))

2) Highlight background of rows which on each level  fall into certain condition:

Expression:

if(Dimensionality()=1 and sum(Sales)<150,RGB(151,255,255),if(Dimensionality()=2 and sum(Sales)<=20,RGB(0,238,0),if(Dimensionality()=3 and Sum(Sales)<=20,RGB(255,130,171))))

LEVEL1 --> Values <140	LEVEL 2 --> Values <=20	LEVEL 3 --> Values <=20

Remember

• To use the script below the Excel file must be saved in the same folder that your qvd file

        Otherwise you will need to make changes the this path - [Dimensionality.xlsx]

Directory;

LOAD Product,

     Category,

     Type,

     Sales

FROM

[Dimensionality.xlsx]

(ooxml, embedded labels, table is Sheet1);

Felling Qlingry?

How To /Missing Manual (25 articles)

About
What is it? What does it do?

This is a tool that I use to quick check shared file content and do some usual maintenance job on it. After playing with colleagues for a while, I think it'd be nice to share with the community and get some feedback about if/how I should proceed with this personal project.

This tool is a very simple one, it just can open both legacy ".Shared" and new ".TShared" formats of QlikView Shared File, show helpful info in it, and provide some very basic operations on shared objects (currently I have only add support for Bookmark because it's the most commonly used one day-today)

Why another Shared File Viewer?

There has been a Shared File Viewer already for quite a while (in PowerTools package)

The limitation of the existing one is it can't open the new "TShared" format that was introduced lately into QlikView. So if one wants to view new format, they have to convert "Tshared" to "Shared" first and convert it back afterwards, which is really annoying especially the shared file is *big*.

Another limitation for the current one is it provides small subset info of Shared file content and doesn't embed much shared file functions (cleaning, filtering) in it because its development toolchain is out of dated.

Lastly, I found it's not easy to run a Shared File Cleaner without GUI and want something more intuitive.

In short the legacy shared file viewer is inconvenient to use(to me at least 😅 ), especially when it comes to new "TShared" format.

So i think why not just write another tool myself to meet my need - here it comes.

Release Note
Current Stable Release: 0.2

You can find it in the attachment, where the zip file simply contains an exe file that you can run on Windows.

Features:

• Shared objects meta info overview
• Shared Bookmark
  • basic info overview
  • sort by Id, Name, LastModified, LastRecalled and Size
  • Export
  • Delete
• Convert "TShared" to "Shared" and backward.

Hopefully you have time to download and play with it, and, most importantly, give me some feedback about how you think of it, and what other functions you want to include in it in future.

NOTE:

this tool is currently under preview only. and please be CAUTIOUS if you use it with production Shared files. I know the shared content is critically important, so make sure you have backup before touching any Shared Files.

If you look closely the following image is very similar to the image above that I used for the Heterogeneous (Mixed) JSON Objects post. The difference is that instead of being multiple rows, it's a single JSON block which contains multiple rows as an array. 

It also looks very close to the image I used for the JSON Arrays (Homogeneous Objects) post above that one. The difference is that in the case of that post, the array was easy to understand as just being multiple rows for the same entity. In this case, each of the "rows" in our array our for different entities.  

Since the previous post was so long, and you definitely did the homework for both of the referenced posts I'm going to keep this one short. We are simply going to walk through how you can convert the array of data into a table of data that can be parsed in a flexible way.  

Practice

If you haven't already done so for a previous post, go ahead and download the WildDataFrontier.qvf that is attached, uploaded it to your environment and open the load script.  

{ Notice there are multiple sections. Each of them will pertain to a separate article and for this article the section named "6 - JSON Array: Heterogenous Mixed Objects" is the one you want to have at the top of your script for this post. } 

Same basic type of inline load that you've seen in previous posts. Unlike the previous post where the data was multiple rows for a table, I've returned to simulating a single JSON block return. You can see the pretty view of the data, as well as the single row view.  

Feel free to scroll right down to Step 1 in the preceding load process. You will see that all I do is use the IterNo() function to iterate the array, and simply create a table like we started with in the previous post.  

If you comment out Steps 2-4 in the load script so that Step 1 is the only active you can see the results in the preview tab. 

Just like we did in the previous post, feel free to simply uncomment 1 Step at a time in the load process, until you return to having all 4 steps being active. At that point if you take a look at the preview and will see that you have in fact parsed out all the values for all of the fields for every mixed entity JSON block in the array.  

Big Picture 

This post is part of a series aimed at ensuring you have the tools needed to Tame with Wild West Data Frontier, I mean Tame the Wild JSON Data Frontier you may be facing.  

Posts:  

• Taming the Wild JSON Data Frontier
• Parsing: Flat JSON - Field Value Pairs
• Parsing: Nested JSON Objects
• Parsing: JSON Array (Homogeneous Objects)
• Parsing: Heterogeneous (Mixed) JSON Objects Fixed Manner
• Parsing: Heterogeneous (Mixed) JSON Objects Flexible Manner
• Parsing: Heterogeneous (Mixed) JSON Arrays - You are here

As you look at the image above you will notice multiple JSON objects. In the previous post Parsing: Heterogeneous (Mixed) JSON Objects Fixed Manner we walked through how we could easily handle this type of data in a fixed way. All you need to do is know every single field:value pair that will ever come to you. 

Flexibility

If you haven't already read that previous post be sure to do so and go through the practice. You need to understand that while easy to maintain for new fields that come across ... the thought of knowing every single field:value pair that will ever come to you is kind of daunting. Right? 

I kind of slipped it in there but:

• You know that you can't see over the horizon to know which bandits are heading to your town.
• You also know that while the job security is great, constantly being behind and having to rush to identify the new bandits can cause a lot of stress.  

This post will walk you through the same data that we dealt with in the previous post, but will allow you to read all of the values for all fields that exist now, and those that might come riding in to town tomorrow.

Practice

If you haven't already done so for a previous post, go ahead and download the WildDataFrontier.qvf that is attached, uploaded it to your environment and open the load script. 

{ Notice there are multiple sections. Each of them will pertain to a separate article and for this article the section named "5 - Mixed JSON: Flexible Values" is the one you want to have at the top of your script for this post. }

Same basic data as the previous post, but I've also included the 4'th row of data that I asked you to do as part of the practice. The flexibility that you need as a data sheriff in this JSON Wild Data Frontier is going to be handled by the incredible flexibility available to you in how Qlik Sense can load and transform that data. 

In previous posts we've simply done a resident load to transform the data directly in 1 step using that ever so flexible JsonGet() function. In this post our transformation is going to do a few things that may be new to you. Be sure to click the links for each to get at least an introduction to them:

• Preceding Load
• Generic Load

Working backwards

Once you have the application open and have moved section "5 - Mixed JSON: Flexible Values" to the top of the script go ahead and load the data. We are going to start by previewing the data for the finished product and then work backwards to understand how it was accomplished. If you look at the preview screen you will notice something odd: You have the Mixed_JSON table that was built by the inline load part of the script, then you have a whole bunch of other tables. One for each of the fields that is part of the data. 

Go ahead and select the Information_Flexible.bounty table so you can preview what data is in that table.

If you walk each of the tables you will notice that each table contains the value for that given field. If you look at the Data Model Viewer you will see that every table is associated based on the row number. Which means in any chart that you visualize you will easily be able to present the values for all of the fields. 

Add Data

Add the following row of data to the inline load script, and then reload the data. Notice that it is brand new type and contains a value for a new field called bowtie_color, as well as values for two existing fields text and bounty. 

5, '{ "type": "dork_mail", "bowtie_color": "Qlik Green", "text" : "Qlik Dork is coming to town", "bounty": 1000000 }'

What do you know? Our application is indeed flexible and it has created a new table called Information_Flexible:bowtie_color

Feel free to check out the values for it (as well as the values for the text and bounty tables) to see that indeed we have created structure from this unstructured heterogeneous JSON mess. 

How

I tried my best to include comments in the load script which will hopefully aid in your learning. But I will admit sometimes it's hard to mentally walk through a script like this, even with comments, when there are multiple preceding loads like this. So, let's take this one step at a time and walk through what happens for each of the preceding load steps. To do this highlight rows 20-43 (from the Generic Load line until before Step1) and then comment those rows out and then reload the data.

If you look at the preview window now you will see that you have a single Information_Flexible table. As described in my comments, all step 1 did was to remove the { } characters out of the JSON block and we simply have some field:value pairs. 

Now uncomment lines 41-43 and reload the data so that we are running the first preceding load statement.  

If you look at the preview for the Information_Flexible table you will see something interesting. We have multiple rows in the table for each of the original rows. As the comments indicated the SubField() function has done that. Each of our field:value pairs has been put into it's own row:

This is getting fun. So let's keep going. Now uncomment rows 31-34 and reload the data again. 

As you look at the preview again for the Information_Flexible table you will notice something cool. The field:value JSON pairs, have been turned into structured Field and Value fields. 

If we could created a table for each of the different Field values and store those Value values in it we would really be in business. Now I remember that's exactly what a Generic Load does. 

Go ahead an uncomment lines 20-24 so that we can once again reload the data, and this time return to our starting point. 

Summary

Like many things I have written in the past ... this solution isn't really all that complicated, but understanding how multiple Qlik Sense load transformations work together can be. Hopefully, you now feel confident that you have an example load script that you can use to flexibly parse out any nasty JSON Heterogeneous data that those varmints throw at you. Plus you realize how easy you can make it for yourself to simply comment out preceding load steps so that you can get a picture of what is occurring each and every step of the way. After all, not everyone documents their code so thoroughly. 🤠

Big Picture 

This post is part of a series aimed at ensuring you have the tools needed to Tame with Wild West Data Frontier, I mean Tame the Wild JSON Data Frontier you may be facing.  

Posts:  

• Taming the Wild JSON Data Frontier
• Parsing: Flat JSON - Field Value Pairs
• Parsing: Nested JSON Objects
• Parsing: JSON Array (Homogeneous Objects)
• Parsing: Heterogeneous (Mixed) JSON Objects Fixed Manner
• Parsing: Heterogeneous (Mixed) JSON Objects Flexible Manner <--- You are here
• Parsing: Heterogeneous (Mixed) JSON Arrays

As you look at the image above you will notice multiple JSON objects. In previous articles we've dealt with single JSON objects. Now we need to deal with the fact that the file has multiple rows, or we called an API that returns as a streaming set of values, rather than just a single response. 

Like previous posts these JSON objects have field:value pairs that we can certainly parse out, but each record seems to be different than the previous. There is a type field, but the value for type seems to indicate a different entity type all together. 

SQL Like

In previous posts I tried to help you relate the unstructured JSON block to it's SQL counterpart. 

When I presented Parsing: Flat JSON - Field Value Pairs we discussed how a JSON structure was similar to a single row/record of a table. 

When I presented Parsing: Nested JSON Objects we discussed how a nested JSON structure was similar to a select clause that joined data from multiple tables. 

When I presented Parsing: JSON Array (Homogeneous Objects) we talked about how the array structure was similar to returning multiple rows from a table, rather than a single record. 

In SQL terms this would be like reading a table and if the value said report, then you would join to a table that stored values for a report. If the value said wanted_poster, then you would join to a table that stored values for a wanted_poster. If the value said telegraph, then you would join to a table that stored values for a telegraph. 

Wait, that's not really how SQL works is it? You don't have columns that might be a foreign key to any of 3 different (or more) other tables. Complete random thought here ... What if this kind of flexibility is exactly what makes JSON a great data exchange format? 

Enough random brainstorming ... let's get back to parsing out this data.

JsonGet()

Qlik Sense provides a very easy to understand function called JsonGet that we will use to get the values for the fields contained in this homogeneous JSON data. It wouldn't matter to you if I asked you to tell me the text contained in the report, or if I asked you for the bounty amount for Billy the Byte's wanted_poster, or if I asked you to tell me the message contained in the telegraph. You would simply find the fields and tell me the value. 

If you are anything like me I'm sure you worried/fretted/shook in your boots that the function would return an error if a value you asked for didn't exist. But stand tall data sheriff, because it will simply return null if you ask for a field in the JSON structure that doesn't exist. 

Thus the easiest way for you to read all of this homogeneous data is simply call the JsonGet function for all of the field types from any of the entity types. It's that simple:

Practice

If you are anything like me you don't like to assume anything, and you are a visual or experiential learner. So, go ahead and download the attached WildDataFrontier.qvf that is attached, upload it to your environment and open the load script. 

{ Notice there are multiple sections. Each of them will pertain to a separate article and for this article the section named "4 - Mixed JSON: Fixed Values" is the one you want to have at the top of your script for this post. }

Each section of the application begins with preparing the JSON object(s) we will be parsing. Previous posts loaded a single JSON block, but in this case we have a simple inline table with multiple rows. To help you more deeply understand this set of data imagine that you are reading data from a source with multiple rows, or an API keeps responding with results and each row you read you use the RowNo() function to create an ID for that row and stores it in a field called RowNumber. 

Preview

Before making any changes to the data be sure to load the data for this section and check out the preview. I want to ensure that you are confident in the fact that jail cells aren't going to blow up when you call the JsonGet() function for a field:value pair and that row of data doesn't exist. 

Now go ahead and add a new row to the inline table:

4, '{ "type": "homestead", "coordinates": "Some Where", "amount" : 7777 }'

Before actually adding any new JsonGet() function calls in the script just go ahead and load the data so that you can see that it has no problem reading your new row and will display the homestead type in the preview window for you. Now go ahead and add the 2 lines of code you need to handle the 2 new fields for the homestead type: coordinates and amount.

Big Picture 

This post is part of a series aimed at ensuring you have the tools needed to Tame with Wild West Data Frontier, I mean Tame the Wild JSON Data Frontier you may be facing.  

Posts:  

• Taming the Wild JSON Data Frontier
• Parsing: Flat JSON - Field Value Pairs
• Parsing: Nested JSON Objects
• Parsing: JSON Array (Homogeneous Objects)
• Parsing: Heterogeneous (Mixed) JSON Objects Fixed Manner <--- You are here
• Parsing: Heterogeneous (Mixed) JSON Objects Flexible Manner
• Parsing: Heterogeneous (Mixed) JSON Arrays

The image depicts what is called a JSON Array and to be precise its a JSON Array of Homogeneous Objects. I'm sure you are no greenhorn at this point in my series on parsing JSON. If I said tell me the name who is the owner of the third saloon in our town, I'm sure you would immediately reply "Miss Ada." 

This post is going to focus on how you came to your answer. Guess what function Qlik Sense provides that will allow you to parse that answer out of this unstructured textual array just like you were able to do with your human mind?

JsonGet()

If you guessed it was the same JsonGet() function covered in the previous posts on Parsing: Flat JSON - Field Value Pairs and Parsing: Nested JSON Objects you are 100% correct. 

The how is where it gets interesting. In the post Parsing: Nested JSON Objects, I brought up the concept that we needed to qualify the path to our field:value pair and we worked through how accessing the name field for an entity called sheriff was accomplished by doing this:

I told you that the notation was essentially /entity/field. But you somehow managed to astound me with your ability to parse the opening image and provide the answer to my question about the third saloon. You were able to answer because you automatically, in your mind, altered that notation to be something like /entity/INDEX/field. 

The essential part of this post is the fact that the INDEX value starts at an offset of 0. Meaning the first value has an index of 0. 

Assume that the JSON structure from the image at the start of the post is inside a field called Array that is in a table called JSON_Array. The following code would pull out values from the array. Notice that we pull the name, owner and capacity field values for the first saloon. We only pull the name for the second saloon. The last line simply pulls the entire JSON block, the "row" itself for the final saloon in our array. Maybe it will deal with parsing it later. It's entirely possible that I purposely did that in this basic post to prepare for a much much deeper post in the future where that is in fact what I do. I pull out the block and deal with it in a different part of the code. 

Practice

If you are anything like me you don't like to assume anything, and you are a visual or experiential learner. So, go ahead and download the attached WildDataFrontier.qvf that is attached, upload it to your environment and open the load script. 

{ Notice there are multiple sections. Each of them will pertain to a separate article and for this article the section named "3 - JSON Array: Homogeneous Objects" is the one you want to have at the top of your script for this post. }

Each section will begin with a common pattern. I will show you the prettified version of the JSON structure. Meaning it's indented, and thus easy to read. Then I will remove the line feeds so that the JSON structure being covered is a single line that we deal with as a single string for transformation within Qlik Sense. That single text/block is what API's will return or what you will read out of textual fields that contain JSON blocks from RDMBS systems. In the other articles it was easy for me to simply create an inline table that contained that JSON Block. But for an array we have a special issue to deal with ... those brackets the define the beginning and end of the array "[" "]" 

Those 2 characters are special and define the beginning and end of our inline statement and thus they cause a problem. Notice that I've simply duplicated the characters [[ and ]] in my line of code, so that the inline table reads it in, and then I do a preceding load to pull the duplicate brackets right back out. 

Preview

Before you worry about making any changes, simply load the script as is and go to the Preview window and see the JSON_Array table. You will then see that our Array field is formatted as needed to be a proper JSON Array. 

Iterative

When I began the article I asked you to focus on the third saloon. Then I showed you the code needed to parse out and get the same response from the code that you were able to do mentally. 3 entries fit my example case for this post because ... I needed to parse out the array 3 different ways. I wanted all values for a "row", only 1 value for a "row" and just the JSON for the final "row." 

But I don't want to leave you hanging there. Some rascally gunslinger is bound to head into your town with 5 rows. Or 10 rows. Or 20 rows. The last thing I want you to do is sit there and hand code a hard coded limit and then tell the good folks in your town that as Data Sheriff you will happily give them insights from the first 20 values. They just might run you out of town. 

Never fear my friend, Qlik Sense provides a fantastic function call that will actually iterate through as many rows as needed. The function is called IterNo() and as the help makes really clear, the only useful purpose for the IterNo() function is when used with a While condition. In other words ... you need a way to ensure we end the recursion. 

So here is how we can utilize it to parse our JSON Array of Homogeneous Objects. Rather than just reading the resident JSON_Array like we did above when hardcoding the parsing, we simply take advantage of the IterNo() function and then combine it with another Qlik Sense function called IsJson(). 

Resident JSON_Array
WHILE IsJson(JsonGet(Array, '/saloons/' & (IterNo()-1)));

The IterNo() function will start at 1 and then keep iterating, until you tell it to stop. Easy breasy.

The IsJson() function will say "Yep you have properly a properly formatted JSON structure" or "Sorry partner you drew a bad hand." 

Thus ... as the IterNo() function iterates through we will be handling the following:

• WHILE IsJson(JsonGet(Array, '/saloons/0')));
• WHILE IsJson(JsonGet(Array, '/saloons/1')));
• WHILE IsJson(JsonGet(Array, '/saloons/2')));
• WHILE IsJson(JsonGet(Array, '/saloons/3')));

The first 3 iterations will succeed, but the 4'th iteration will return nothing and thus end our while loop. As you will see in the script in the WildDataFrontier application, we can then simply refer to the iteration number (will remain constant for the row being processed) and pull out our values for every row that is returned in the array. Whether it be 1, 10, 20, 100 or more. 

Thus when we preview the flat/hardcoded method the results are usable

But when we preview this iterative approach the results are usable today, tomorrow and next year. So, you can remain as the Data Sheriff. 

Big Picture 

This post is part of a series aimed at ensuring you have the tools needed to Tame with Wild West Data Frontier, I mean Tame the Wild JSON Data Frontier you may be facing.  

Posts:  

• Taming the Wild JSON Data Frontier
• Parsing: Flat JSON - Field Value Pairs
• Parsing: Nested JSON Objects
• Parsing: JSON Array (Homogeneous Objects) <--- You are here
• Parsing: Heterogeneous (Mixed) JSON Objects Fixed Manner
• Parsing: Heterogeneous (Mixed) JSON Objects Flexible Manner
• Parsing: Heterogeneous (Mixed) JSON Arrays

Well howdy partner.

Back in that there big city all your data was nicely structured into tables and columns. Other than the chaos of a million requests your life as the data sheriff was pretty easy. You knew how to wrangle up any stray data with your "Selects" and your "Joins." 

Like so many that hopped on a wagon train to the west in the past for a gold rush, your company  hopped on board a train to a modern data stack. Now here you are with all your outlaw JSON data coming at you like rattle snake. 

• Your drifter data.
• Your snake oil data.
• Your Ghost Town Data.
• Your Gold Rush Data. 
• Your embedded unstructured JSON returns from an RDBMS.
• Your unstructured JSON returns from RESTful APIs.

The good news is, that it's easy to read visually. At least it's easy to read when it's prettified. 

But your problem is that your company needs you to translate it from this unstructured textual JSON data into structured values that they can gain insights from, and take action on. Just when you think you've figured out a pattern, you discover yet another JSON structure type. 

Well partner, let's walk through the various types of JSON structures you will see together. I want to help ensure your JSON Data General Store is fully operational and that you know the patterns to looks for when any new varmints come moseying along.

In this post I will walk you through various examples, provide you a sample Qlik Sense Application that contains all of the types, and then provide a linked article explaining each type in more detail that you can access. 

Flat JSON - Field Value Pairs

The most basic type of JSON structure is a flat one that simply contains a set of field and value pairs. The { and } are the textual wrappers that basically signify what you could think of as a single row for an entity. In a RDMS world, you would have column names and then values. In this JSON world you have field and value pairs. In this example image we have 4 fields/columns "outlaw_name", "bounty", "status" and "last_seen_town." Notice that the values for the fields are separated using the ":" character. If the values are textual, then like the field names, they are wrapped in double quote characters, while numeric data isn't. 

Click here to access a post dedicated to helping you parse out a simple Flat JSON - Field Value Pair block. 

Nested JSON Objects

A slightly more sophisticated JSON structure is one that includes a nesting if you will of the objects. Rather than simply using field value pairs like above, information surrounds entities. And entities can include other entities. In other words in an RDBMS world this would be multiple tables with primary key and foreign key relationships that would need to be joined. 

You can distinguish an entity from a field based on the value. Notice that sheriff doesn't have a single value, it instead contains a block of JSON code. That block has 2 simple field:value pairs for name and badge_number and it also includes an entity called office. Unlike SQL where the office entity would exist on it's own, in our JSON structure it is nested into the office structure. While a minor distinction the point itself is major: You don't have a sheriff entity and an office entity. You have a sheriff entity and a sheriff/office entity nested within the sheriff entity. 

Click here to access a post dedicated to helping you parse out Nested JSON Objects. 

JSON Arrays (Homogenous Objects)

Like anyone panning for gold would tell you ... the only thing better than a gold nugget is finding more than 1. Any good bandit would tell you the only thing better than a town with 1 saloon, is a town with multiple saloons. 

A JSON Array of (homogenous objects) is very much like a table in an RDBMS. Instead of just having the field:value pairs for a single row, the array contains all of the rows. The rows for the entity are wrapped in brackets "[" ... rows ... "]." In this image you can see we have a saloons entity and rather than field:value pairs for a single saloon, we have an array of saloons and each row of the array contains the simple field:value pairs. 

Click here to access a post dedicated to helping you parse out JSON Arrays of Homogeneous Objects.  

Heterogeneous (Mixed) JSON Objects

Up to this point we have been working with a single JSON object. A single string. You either open a file and voila there is one string. Or you make an API call and it returns one string. 

Now we will focus on heterogenous JSON data that may be streaming into you. Rather than opening a file and finding one string you find multiple strings. You call an API and instead of returning 1 result string, it keeps sending you an unknown number of strings. What's worse is that you receive heterogenous objects. The type field seems consistent, but each type seems to present with a different entity: A report, a wanted_poster and a telegraph. 

Don't panic data sheriff, I've got a posse of posts targeted at helping you handle these mixed JSON objects. In the first post we will look at handling this data pretty much like we've handled the JSON structures until now. In the second post I show you a much more flexible approach so that you can handle whatever desperados happen to ride into town. 

Click here to access a post dedicated to helping you parse out Heterogeneous JSON Objects in a fixed format.

Click here to access a post dedicated to helping you parse out Heterogeneous JSON Objects in a flexible manner. 

Heterogeneous (Mixed) JSON Arrays

If you look closely the following image is very similar to the image above that I used for the Heterogeneous (Mixed) JSON Objects post. The difference is that instead of being multiple rows, it's a single JSON block which contains multiple rows as an array.

It also looks very close to the image I used for the JSON Arrays (Homogeneous Objects) post above that one. The difference is that in the case of that post, the array was easy to understand as just being multiple rows for the same entity. In this case, each of the "rows" in our array our for different entities. 

The good news for you is that you've already done the work to handle both arrays and heterogeneous data. That is assuming you've done the homework in both of those posts. The post I'm writing for handling this type of data will be brief. All I need to do is help you understand how to combine the two algorithms. 

Click here to access a post dedicated to helping you parse out Heterogeneous JSON Arrays.

Structured Arrays

Just when you thought you had all those JSON array critters rounded up ... another one appears while you were in the saloon celebrating. While there is no formal name for this JSON Version 2 array, structured, seems to make sense. Notice that is very much like SQL in that the column headers and the data are sent separately. Which reduces the data redundancy when dealing with transmitting lots and lots and lots of rows of data. 

 
Click here to access a post dedicated to helping you parse out these structured arrays. I think you will really like the creativity I used. 

Big Picture 

This post is the starting post for a series aimed at ensuring you have the tools needed to Tame with Wild West Data Frontier, I mean Tame the Wild JSON Data Frontier you may be facing.  

Posts:  

• Taming the Wild JSON Data Frontier <--- You are here
• Parsing: Flat JSON - Field Value Pairs
• Parsing: Nested JSON Objects
• Parsing: JSON Array (Homogeneous Objects)
• Parsing: Heterogeneous (Mixed) JSON Objects Fixed Manner
• Parsing: Heterogeneous (Mixed) JSON Objects Flexible Manner
• Parsing: Heterogeneous (Mixed) JSON Arrays
• Parsing: Structured JSON Arrays

The image depicts what is called a nested JSON structure containing a variety of both entities and field and value pairs. If I asked you what the sheriff's name was, you would instinctively say "Marshal Ada Lovelace." 

Unlike the previous post Parsing: Flat JSON - Field Value Pairs this structure has more than just the fields and values it has entity structure. The name field isn't a standalone name, it's part of the sheriff entity. The sheriff's office isn't just a field ... it's a nested entity, that includes multiple field value pairs.  

JsonGet()

In the previous post I referred to above, I introduced you to a function called JsonGet. It allows you to give it a JSON block and ask it for the value of a specified field. Your likely question is "How do I ask it for a field that might be in a nested entity of another entity?" 

The answer is by simply providing the qualified path to the field name. Just as I asked you what was the "sheriff's name," you would call the JsonGet() function and ask it for the /sheriff/name field. Assume that the JSON structure from the first image were loaded into a field called Nested_JSON_Block in a table called Nested. The following code would load all 4 of these unstructured data values into structured fields. 

SQL Like

If you are familiar with SQL then this notion of qualifying the field name is nothing new to you. If you issue a Select statement from a single table all you need to do is list the field names in the select clause. 

Select FieldA, FieldB, FieldC 

From Table1

But when you join tables, especially if you have a field name in more than 1 table, you have no choice but to qualify the path for the field

Select Table1.FieldA, Table1.FieldB, TableA.FieldC

From Table1 ... blah blah blah join Table2

Instead of table.field notation like SQL, the JsonGet() function within Qlik Sense simply needs the path in /entity/field notation. If you have nests of nests of nests, it is the wild west of JSON data you might be pulling, that notation would continue /entity1/entity2/entity3/entity4/field. 

Practice

If you are anything like me you don't like to assume anything, and you are a visual or experiential learner. So, go ahead and download the attached WildDataFrontier.qvf that is attached, upload it to your environment and open the load script.  

{ Notice there are multiple sections. Each of them will pertain to a separate article and for this article the section named "2 - Nested JSON: Nested Objects" is the one you want to have at the top of your script for this post. }

Each section will begin with a common pattern. I will show you the prettified version of the JSON structure. Meaning it's indented, and thus easy to read. Then I will remove the line feeds so that the JSON structure being covered is a single line that we deal with as a single string for transformation within Qlik Sense. That single text/block is what API's will return or what you will read out of textual fields that contain JSON blocks from RDMBS systems. 

For your learning, simply modify any/all of the field names, modify the JsonGet transformations and then load the data. Try adding:

• A date field to indicate when the sheriff became the sheriff.
• A sheriff office fax number.
• If you really want to be brave ... add a new entity for the sheriff called spouse. The spouse entity should have a married true/false value, and spouses name and their marriage date. 
• If all that was to easy for you, feel free to really jump into it and add a new entity for the office entity called statistics. The sheriff/office/statstics entity should have fields like number of deputies, number of jail cells. 

Preview

As you go through your changes, the easiest thing to do is visualize the Preview at the bottom of the load editor. 

Hint

As you try and make your changes if editing line 19 is to confusing and you struggle with the nesting, feel free to make your changes instead in the area that is commented out between lines 4-11. Then copy that prettifield block of code and change line 19 to only have the opening and closing single quote marks. Paste your copied block after the first single quote and start removing the line feeds. That's how I built the code for you so no shame in taking the easy route. 😁

Big Picture 

This post is part of a series aimed at ensuring you have the tools needed to Tame with Wild West Data Frontier, I mean Tame the Wild JSON Data Frontier you may be facing.  

Posts:  

• Taming the Wild JSON Data Frontier
• Parsing: Flat JSON - Field Value Pairs
• Parsing: Nested JSON Objects <--- You are here
• Parsing: JSON Array (Homogeneous Objects)
• Parsing: Heterogeneous (Mixed) JSON Objects Fixed Manner
• Parsing: Heterogeneous (Mixed) JSON Objects Flexible Manner
• Parsing: Heterogeneous (Mixed) JSON Arrays

The image depicts the simplest of JSON structures, a flat list of field and value pairs. Your eyes can easily parse the data, so within a second if I asked you what the outlaw's name was, you would respond with "Billy the Byte." Because instinctively, and without instruction from me, you determined that "outlaw_name" and "Billy the Byte" went together as a PAIR.

JsonGet()

Qlik Sense provides a very easy to understand function called JsonGet that does exactly the same thing that your eyes did. You simply hand it the JSON Block your JSON is in, and tell it the field name you want to parse out of the structure, and it "gets" the value that is paired with the field name you gave it. 

Let's assume that the JSON structure is loaded into a field called JSON_Block in a table called Flat,  the following code would parse this unstructured textual JSON block into structural field values that you can utilize like you do any of the other 93,302,203 disparate sources you utilize in Qlik Sense every day. 

Practice

If you are anything like me you don't like to assume anything, and you are a visual or experiential learner. So, go ahead and download the attached WildDataFrontier.qvf that is attached, upload it to your environment and open the load script. 

{ Notice there are multiple sections. Each of them will pertain to a separate article and for this article the section named "1 - Flat JSON: Key-Value Pairs" is the one you want to have at the top of your script. }

Each section will begin with a common pattern. I will show you the prettified version of the JSON structure. Meaning it's indented, and thus easy to read. Then I will remove the line feeds so that the JSON structure being covered is a single line that we deal with as a single string for transformation within Qlik Sense. That single text/block is what API's will return or what you will read out of textual fields that contain JSON blocks from RDMBS systems. 

For your learning, simply modify any/all of the field names, modify the JsonGet transformations and then load the data. 

Preview

As you go through your changes, the easiest thing to do is visualize the Preview at the bottom of the load editor. 

That way you can see what the flat JSON structure/text block looks like:

As well as seeing how the Information is parsed with that simple JsonGet statement:

Big Picture 

This post is part of a series aimed at ensuring you have the tools needed to Tame with Wild West Data Frontier, I mean Tame the Wild JSON Data Frontier you may be facing.  

Posts:  

• Taming the Wild JSON Data Frontier
• Parsing: Flat JSON - Field Value Pairs <--- You are here
• Parsing: Nested JSON Objects
• Parsing: JSON Array (Homogeneous Objects)
• Parsing: Heterogeneous (Mixed) JSON Objects Fixed Manner
• Parsing: Heterogeneous (Mixed) JSON Objects Flexible Manner
• Parsing: Heterogeneous (Mixed) JSON Arrays

Hello everyone,

With a Qlik Cloud Analytics Premium license, you can have up to 5 separate tenants.

New tenants can be activated by the SAO through the My Qlik portal. The following link will guide you through configuring your first and/or additional tenants:
How to create your Qlik Cloud tenant - Qlik Community - 1711273

When working with multiple tenants, it’s important to understand that your licensed data capacity is global and is shared across all tenants.

For example:

• License: 50 GB

• Tenant A: 23 GB used

• Tenant B: 22 GB used

Your total consumption will therefore be 45 GB, aggregated across both tenants.

It’s essential to monitor overall usage to avoid exceeding your licensed capacity, regardless of which tenant the data resides in.

When Connecting to a SharePoint side and select Data an Error occurs directly on Open

Resolution

Adding the Path or at least the initial Folder (like Shared Documents for default Document Storage) will solve the issue. 

 Once added you can browse and select Files you have access to. 

Cause 

The SharePoint File Connector will always initially open the Root Folder by Design causing the Access Error. 

A small demo with a link on how the layout container can be used

 Few buttons

Treemap and images can transform your static dashboard into interactive report.

In hte attached link you will find a qvf with all necessary information.

Link: LC Example 

This guide walks you through building a feature-rich spreadsheet that looks and feels like Excel in Qlik Sense using the Spreadsheets extension (first demoed at Qlik Connect 2025).

Check it out as a video tutorial or keep reading:

Step 1: Add the Spreadsheets Extension

1. Open your Qlik app and go to the Assets panel.
2. Head to Custom objects > AnyChart 4, and drag AnyChart Spreadsheets onto your app's sheet.

This creates a basic spreadsheet looking as follows:

Step 2: Add Data

1. Select the object, open the Properties panel, go to Data, and click Add.
2. Choose the new data section, enable Create synced sheet, and click Edit data.
3. Add the dimensions and measures you want to use.

This creates one more sheet, titled DataSheet1, populated with your data.

Step 3: Format Your Spreadsheet

1. To keep things clean, feel free to delete the empty sheet created by default, titled Sheet1.
2. On DataSheet1, select your table area, head to the INSERT tab, and click Table. Confirm with OK.
3. Apply formatting as needed, all in a familiar Excel-like interface. For example:
   - Resize rows and columns.
   - Change alignment and font settings from the HOME tab.

Step 4: Add Charts

You can also add charts within the spreadsheet's sheets, just like in MS Excel.

1. Select your data, open the INSERT tab, and choose Insert Chart.
2. Select a chart type, click OK, and place the chart where you need it.

Step 5: Save Changes

1. Whenever you need to save everything you have done, open the USER tab and click Save to app.

Step 6: Apply Selections & See Your Spreadsheet in Action

1. Apply filters or selections in your Qlik app.
2. The spreadsheet updates instantly. Charts, tables, and formulas adjust to the selected data in real time.

This works as the spreadsheet is fully connected to Qlik’s associative engine.

Optional: Export to Excel

If you want to work outside of Qlik, such as in Microsoft Excel or Google Sheets, feel free to export the entire spreadsheet as an .xlsx file:

1. Go to the FILE tab, choose Export, and select Excel format.
2. Click Export Excel File, enter a file name, and confirm with OK.
3. The file downloads immediately, ready to open in Excel or another compatible tool.

Here's how the downloaded XLSX file looks when opened in MS Excel:

Done

You now have a reactive spreadsheet, built with live Qlik data, ready to expand, customize, or share.

Helpful Links

• Download the extension
• Explore the demo app (view live or download QVF)
• See the documentation
• Request a personalized live demo

Starting Point

Before you get to excited, the Qlik Lineage API's do NOT work against your Qlik Sense Enterprise on Windows environments. But I used that title because that is how I searched when a customer said "I really like tracking the lineage for my SasS applications, but I also need it for the Qlik Sense Applications I still have on-premise." 

Don't get discouraged just yet, as I'm going to show you a way forward that will let you capture the lineage you want using a product Qlik provides that you might not be aware of ... Qlik Lineage Connectors. 

In a nutshell, the Qlik Lineage Connectors collects all the lineage information for your on-premise environments and publishes the needed METADATA for them to the Cloud, where you can use the API's. No data is moved to the cloud, and you do not need to reload anything in the cloud to make it work. 

Installation & Configuration

Note: You will need at least a bare bones Qlik SaaS tenant to serve as the catalog for your lineage data. Follow the instruction for the Qlik Lineage Connectors application, as the point of this post is to help you after that so you can configure and get the Qlik Lineage API's working on all of that precious on-premise lineage metadata. 

The metadata can only be published to a Managed space within your Qlik SaaS Tenant. In the following screen shot you will notice I conveniently named that space "OnPremise" so there would be no doubt in my old mind what the space was when I see it a few weeks from now. 😉

The following screen shot shows what that space looked like before configuring and running the Qlik Lineage Connectors in my environment: 

Executing Qlik Linage Connectors On-Premise

You can run/test the process by going to the Scheduler tab and simply pressing the run/play icon. As you will see in the image below I configured my environment to be limited to a single stream that had 22 applications in it. 

As the process was going I began seeing "items" appear in my "OnPremise" space. 

Output

I used the word "items" because they are a type of object called an "External link." You can click on the link and it acts just like other hyperlinks and would launch the application in your QSEoW environment. However, it also has the advantage which is driving this post ... the ability to see the Lineage Metadata for those on-premise applications. Simply right click on the "external link" and choose either "Lineage" or "Impact analysis", just like you would any of your SaaS applications. 

 Yeah! We are in business: 

API Calls

If you checkout the Lineage APIs you will notice a pretty simple format. 

https://{Qlik Tenant}/api/v1/lineage-graphs/nodes/{QRI_ID} 

If you look at the online documentation or examples people have shared they all involve "applications." Which shows you that the QRI_ID involves a pattern of "qri:app:sense://{Application Id}"

Alas, these "external links" are not "applications" they are simply pointers to the on-premise applications along with the lineage metadata. So, that format won't work for us. However, we can easily get that QRI_ID value using the Qlik "items" API call. 

Items API

In the documentation you will notice that one of the parameters that can be utilized for the "items" API call is something called "resourceType." That parameter allows you to filter the API to exactly what you are looking for which we need to do in this case. The "resourceType" value we want is "genericlink."  

Notice that in my screenshot (from Postman) that in addition to passing "genericlink" for the "resourceType" parameter, I have also configured another parameter called "spaceId" that then limits the list to the "genericlink"(s) found in my "OnPremise" Managed Space. 

If you look at the blue highligted area in the screenshot above it output exactly what we are looking for in the form of the "secureQri" value. Woohoo! We can programmatically iterate through the output from calling the items API and pass the QRI_ID values to the lineage API.

Lineage API

Two important parameters for the Lineage API are called "level" and "up." The "level" parameter lets you define what aspects of the lineage information you want. While the "up" parameter lets you declare how many values to return. 

In my call, notice that I have indicated that I wish to get all values, and I want the resources.

You simply change the "level" parameter if you wish to obtain Table names or Field names and the information about them. Or set it to all and it will return all of the metadata you could dream of.