Hi everyone!!

It’s about VBScript macro which export all variables from your Qlikview application. You can call it by using a button object or using document event triggers (OnPostReload) located in Settings menu Document Properties option and  tab Triggers, for example.

So, why am I develped it? I'll tell you why. A friend of mine was talking about variables sincronization issues between his Qlikview and Qliksense applications.  Every time when he create a new variable or he changed   the variable definition or even in its comment, he had to replicate this changes to another application which,  sometimes, he forgot to do it.

Firstly, I’ve searched if was there some solution on the Qlik Community. Unfortunately, I didn't found nothing ready. So, that's why I developed it. Since then, my friend has been using this macro in his applications and the variables sincronization issues was solved. He just call the macro through document event triggers, setting (OnPostReload) option and then the macro load every single variable into a file (*.csv) which is created in the same folder of your Qlikview application. Once the variables was exported our Qliksense application could be load it and create each variable easily.

Just to be clear and understandable how it works, I attached as example two files  (*.qvw) where one of them is the application which need to export his variables every time its reloaded and another is the application which will load data the exported file and it will create every single variable.

When you will open macro editor from the application which will export the variables you will can easily change the file name and its folder destiny. You will can change columns name as well.

So, that is it! I hope it can be useful for everyone!

You can read the scripts below:

ExportVarApp (Macro):

Sub ExportVar()

set docprop = ActiveDocument.GetProperties
wd = docprop.MyWorkingDirectory
wdEx = wd & "\VariablesApp\"

Set fileSystemObject = CreateObject("Scripting.FileSystemObject")
If Not fileSystemObject.FolderExists(wdEx) Then
fileSystemObject.CreateFolder(wdEx)
End If

vNameFile = fileSystemObject.GetBaseName(ActiveDocument.Name) 'Getting Qlikview's document name
fPath = wdEx & vNameFile & "_Variables.csv"

On Error Resume Next
Err.Clear
if fileSystemObject.FileExists(fPath) Then

fileSystemObject.DeleteFile(fPath)

if Err.Number <> 0 then
' An exception occurred
msgbox "Exception:" & vbCrLf &_
" Error number: " & Err.Number & vbCrLf &_
" Error description: '" & Err.Description & vbCrLf &_
" File cannot be deleted because it still open for another application." & vbCrLf &_
" Please, close " & vNameFile & " file and try it again." & vbCrLf
exit sub
else
set fileSystemObject = Nothing

Set objFSO = CreateObject("Scripting.FileSystemObject")
'--------------------------------------------------------------------------------------------------------------------------------------------
' ForReading = 1 - Open a file for reading only. You can't write to this file.
' ForWriting = 2 - Open a file for writing only. Use this mode to replace an existing file with new data. You can't read from this file.
' ForAppending = 8 - Open a file and write to the end of the file. You can't read from this file.
'--------------------------------------------------------------------------------------------------------------------------------------------
Const ForAppending = 8
vSep = "|" 'Delimiter or Separator of fields'
vColumn1 = "VarName"
vColumn2 = "VarDefinition"

set MyVarsApp = ActiveDocument.GetVariableDescriptions

for i = 0 to MyVarsApp.Count - 1
set vObj = MyVarsApp.Item(i)
Set objTS = objFSO.OpenTextFile(fPath, ForAppending, true)
if i = 0 then
objTS.WriteLine(vColumn1 & vSep & vColumn2)
objTS.WriteLine(vObj.Name & vSep & vObj.RawValue)
else
objTS.WriteLine(vObj.Name & vSep & vObj.RawValue)
end if
objTS.Close()
next
msgbox("Arquivo " & vNameFile & "_Variables.csv" & " was exported with success!!")
end if
end if
End Sub

ImportVarApp (Loading and creating variables)

//Load variables
VarTable:
LOAD VarName,
VarDefinition
FROM
VariablesApp\ExportVarApp_Variables.csv
(txt, codepage is 1252, embedded labels, delimiter is '|', msq);

//Creating variables
For i = 0 to NoOfRows('VarTable') - 1

Let vVarName = peek('VarName', i, 'VarTable'); // Name of the variable

Let $(vVarName) = peek('VarDefinition', i, 'VarTable'); // Definition of the variable

Next i

//Destroying variables of control
Let i = null();

Let vVarName = null(); //

Drop Table VarTable;

Download latest version

Challenge:

Many of us likely know that after developing a Qlik Sense app, documentation needs to be created to deploy our analysis to production. This process is time-consuming, often requiring several days to produce what feels like just a "piece of paper". Another issue is that documentation is often made using standard templates, leading to materials that aren't complete and can be hard to comprehend. Documentation can be mandatory for both regulatory and operational reasons. Following the idea of “working smarter,” I want to automate this process using DocuGen with Qlik Application Automation (QAA). This approach can save us valuable time, allow us to transfer use cases to production faster, and provide documentation that is both thorough and easy to read.

Idea:

It all started with this simple idea and a first prototype, which I built in QAA. Various Qlik Cloud Services blocks are used to extract the relevant metadata from the individual app (e.g. table definitions, sheets and visualizations, variables, measures and dimensions, etc.). This data is then stored in variables and once all the information has been collected it gets passed into a “Custom Code” block. This block uses Python 3.11 and creates a dynamic HTML file without external references. This means that the created HTML file remains transportable and does not require any external resources, as it contains all the necessary functions itself. Finally, the created HTML file is saved to MS Sharepoint.

Input parameters for the python script.

Outcome:

This approach enables us to produce standardized documentation significantly more quickly on a daily basis, reducing the time required from an estimated 3 to 5 days to approximately 30 minutes. In my day-to-day job as a BI Specialist, I am now able to transfer analysis prototypes to production much faster for my customers and deliver added value for the business departments even faster. I think many other people in similar roles like me face the same challenge. This is the reason why I created a community project out of this idea together with Dennis Jaskowiak (Qlik PreSales). We started to extend the documentation with more functionalities, generalized the design of the documentation and reworked the code. In this article, we would like to share the project and its content with the Qlik Community.

App general information overview.

Sheets & visualizations section.

Data model section.

Script section.

Content of the package:

The archive consists of only 4 files:

• DocuGen v(x).json (x = version)
• code/custom_code_block.py
• code/used_css.css
• code/used_js.js

To import the QAA you need the DocuGen v(x).json file. This file contains the exported QAA workspace.

The "code" folder contains the code segments used if you want to customise the code for your needs.

• custom_code_block.py: This file contains the code that we use in the "Custom Code" blog in the automation.

• used_css.css: This file contains the CSS definition that we use in the "Variable - vCSS" blog in the automation and imports the style description for our HTML file. The definition is not minified in this file.

• used_js.js: This file contains the Java scripts that we use in our Python script (custom_code_block.py at the very end). The JavaScript is implemented in the Python code (last <script></script> section). It is important that '{' and '}' are replaced with '{{' and '}}' so that they are not evaluated in Python.

Installation:

1. Create a new blank Qlik Application Automation
2. Upload workspace (right click on canvas) à DocuGen v(x).json
3. Create a connection for SharePoint a. scroll to the bottom and click on the “Create File on Microsoft SharePoint” Block that has a red border b. click on the Connection tab and create a connection to your Microsoft SharePoint c. After you receive the information “Connection is linked” you can use it by clicking on your created connection. The connection tab should now change its colour from red to black.
4. (optional) If you want to switch the number format from ‘comma’ to ‘point’ as decimal separator you can switch the value of the block named “Variable – vNumber Format” from 'de' to 'us'. It’s the second block of the automation.
5. Now you can run the automation to generate your first documentation

Running the automation:

When you click on “Run” the automation gets executed. By default, the automation has configured “Run Mode: Manual”. It requires a few inputs for processing:

• Space – where the app is located (Personal doesn’t work)
• App name
• List of Sections that need to be documented (e.g. Data Model, Measures, Dimensions etc.)
• Screenshots (yes/no)
• List for type of sheets (e.g. Public, Community, Private)

If you want to run the automation triggered (e.g. by REST call) it needs to me modified. We will post an example later to showcase this option.

Features:

This list showcases what DocuGen covers:

• General app information:

  • app name, description, Id, Resource type, type of usage, owner, published, published time, space Id, has section access, is encrypted, create date, modified date, last reload time, app memory footprint, app reload peak RAM

• Data model:

  • For every table it generates:
    • title, number of rows, number of fields, number of key fields, is loose, comment, table size,
    • table containing field name, number of distinct values, number of non-null values, tags, ishidden, iskey, connected tables

• Measures:

  • For every measure it generates:
    • title, label, description, Id, definition, expression, grouping, custom formatted, format type, format, color, tags

• Dimensions:

  • For every dimension it generates:
    • title, label, description, Id, definition, grouping, tags

• Variables:

  • For every variable it generates:
    • title, Id, definition, comment, tags

• Sheets & Visualizations:

  • list of sheets
    • for every sheet it generates title, type, Id, description, grid resolution, sheet mode, list of used objects
    • link to sheet
  • list of objects on the sheet
    • for every object on a sheet, it generates screenshot, type, Id, title, subtitle, footnote, color mode, color definition, dimensions, measures, link to object
  • if the object is a container (layout container, tabbed container or normal container) it creates a list of all child objects containing the following information:
    • type, Id, title, subtitle, footnote, color mode, color definition, dimensions, measures, link to object

• Load Script:

  • For every script section it generates:
    • title, script

Closing:

This is it for now. I hope this community project can help you automate and reduce the time you spend on documentation. On the bottom of that article, you will find the necessary files. The current version of the files will be hosted on GitHub.

Link to GitHub : LINK

A special thanks to Emil Koslowski (Product Manager Qlik Application Automation) for his invaluable support with various Qlik Application Automation questions. Your efforts have been instrumental in shaping a robust solution. Additionally, a big shoutout to Dennis Jaskowiak (PreSales Qlik) for his genuine enthusiasm for the idea, as well as his mentorship and unwavering support throughout the development - your guidance has been truly instrumental. Thank you!

This project has been created by Prodromos Chatziagorakis (Volkswagen Financial Services) & Dennis Jaskowiak (Qlik).

Problem

I see you out there my friend. You got parachuted into an organization that has been using QlikView or Qlik Sense for many years, and you find yourself with directories and sub-directories full of QVD files.

You weren't around when the architecture was put in place.

The boss wasn't around when the architecture was put in place. 

And all of you are scratching your head trying to understand what they are, and why they are there. Because after all QlikView and Qlik Sense are nothing more than #DataVisualization tools. Right?

Base Understanding

While QlikView and Qlik Sense can definitely visualize data and the Associative Engine is ideal for Analytics, the truth is both products provide much more functionality. In this article I will be helping you understand why those QVD's are there and help you see how that fit into the overall Data - to - Outcome platform. Notice I've highlighted sections in red that I will focus on for this post. Wow, nothing to do with visuals/analyze at all.  

For the next few minutes I want you to change hats. If you are in an IT type role, please put on a business user hat. And if you are in business please put on an IT hat. 

IT wants to ensure the data surfaced is governed and high quality. The process of doing that can take a long time without the right tools. 

Business users want outcomes, and typically want the answers they need yesterday. Frankly the last thing they want to hear is "it is going to take a long time before you can get the answers you need."

Qlik Virtual Data warehouse tables

Those two personas don't have to be mutually exclusive. The purpose utilizing QVD files for QlikView and Qlik Sense was to provide a "virtual" data warehouse. For 3 reasons:

• A Centralized data warehouse didn't exist and wasn't going to be built

• A Centralized data warehouse was going to be built, but it was going to take time and business needed answers before it was complete

• A Centralized data warehouse did/does exist, but other sources of data were also needed that were not part of it 

Thus Qlik (Architects/Modelers/Engineers/Developers) utilize QlikView or Qlik Sense to construct a "virtual" warehouse from those sources. 

Benefits of QVD files

Single Source of Truth

Compressed

Development Speed

Typically the IT and Business relationship looks like this ... "Give us all of your requirements in triplicate and signed in blood. We will then prioritize your request and deliver a finished application in 13 man months." With Qlik that can change for the good for all involved. Developers/Designers can sit down with business users, and do a rapid business intelligence session and see what is needed to get the right answers so that the right actions can be taken to drive outcomes. That is made possible by the fact that they can simply start with QVD's that already contain the single source of truth and work with, instead of against, the business users. 

Imagine you are a developer and you need to add 1 variable to the load statement in the application you are working on side by side with a business user. You type the change in the load script in say 30 seconds. Then you tell the business user "Hey let's go to lunch while we wait for 2 hours to go hammer our source system/data warehouse/data mart to pull the needed millions/billions of records." That kind of defeats the purpose of working side by side.

Delivery Flexibility 

Incremental Loads

One thing we all understand about data in source systems, data warehouses and data marts is that it is ever growing. Applications need to stay current. Because nobody is going to achieve good outcomes based on data that is a year old. While we have millions/billions of fact records but the truth is that the vast majority of them are the same that they were 10 minutes ago, a day ago, a week ago. With QlikView and Qlik Sense the data architects can perform what we refer to as Incremental Loads. We talk to the source systems, data warehouses and data marts and say "Hey give me the data that has changed since the last time we talked." (That's just pseudo code not a real SQL statement.) The new data, changed data and deleted is then merged with the existing millions/billions of rows to keep the data fresh and the original QVD is overlayed. This concept may well be the highest benefit and impacts so many of the other benefits. 

One complaint I hear is that "we really need to get answers from live data." Well the truth is running a query that has to return millions of rows of data, especially if joined to other tables takes many, many minutes to return. Can you really say you are dealing with "live" data when it takes 20 minutes to return the answer????? NO!!!! The answer from the live data is actually 20 minutes because tables were locked to return a consistent answer. With incremental loads you could actually ask for the data every few minutes and merge it. The visual applications can provide data that is fresh as of 5 minutes ago, not 20. 

Security

Reduced impact and $$$

Imagine that you are an average ordinary Qlik Developer. You start to create a new application for some healthcare big cheese so you find the virtual data warehouse of QVD files and all goes well. The data loads just fine and just for giggles and grins you utilize the Data Preview in the Load Script to see the values and panic hits you. What on earth is wrong with the Age field? 

That Qlik Dork guy is falling apart and could be 99 for all you know. But Dan and Levi? C'mon they can't possibly be that old. Suddenly you are reminded of the importance of metadata. If you knew how that field was defined, it might help you understand those crazy values. Right?

          How To / Missing Manual

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

SET FileList= ‘File1,File2,File3’

FOR i = 1 TO SubStringCount('$(FileList)', ',' ) + 1
LET var = SubField('$(FileList)', ',', $(i));

LET oldQVD = 'lib://OLDSPACE:DataFiles/$(var).qvd';
LET newQVD  = 'lib://NEWSPACE:DataFiles/$(var).qvd';

TRACE ‘Moving of the file from $(oldQVD) to $(newQVD)';
[$(var)]:
LOAD * FROM [$(oldQVD)] (qvd);
STORE [$(var)] INTO [$(newQVD)](qvd);
DROP TABLE [$(var)];

NEXT
EXIT SCRIPT;

SET FileList= ‘File1,File2,File3’

FOR i = 1 TO SubStringCount('$(FileList)', ',' ) + 1
LET var = SubField('$(FileList)', ',', $(i));

LET oldQVD = 'lib://OLDSPACE:DataFiles/$(var).qvd';
LET newQVD  = 'lib://NEWSPACE:DataFiles/$(var).qvd';

TRACE ‘Moving of the file from $(oldQVD) to $(newQVD)';
[$(var)]:
LOAD * FROM [$(oldQVD)] (qvd);
STORE [$(var)] INTO [$(newQVD)](qvd);
DROP TABLE [$(var)];

NEXT
EXIT SCRIPT;

Thank you for the reading.

Environment

• Qlik Cloud on the 20th november 2024

More detailed information about the Limitation for Generic Link

Resolution

Managing links from the Administration activity center

You can create, upload, edit, move, and delete links from the Generic links page.

Creating a link

Do the following:

1. In the Administration activity center, go to Generic links.

2. Click Create new.

3. To add an image, drag and drop an image file or click Browse to select the file.

4. Enter the following link details:

   • Name: Enter a name for the link.

   • Link URL: Enter the URL in the format https://domain.com.

     • Only HTTPS URLs are allowed.

     • Domain names can contain letters (a-z), numbers (0-9), and hyphens (-), but not underscores (_) or the '@' symbol. For example, https://my-domain.com/ is valid, while https://my_domain.com/ is not.

   • Space: Select the space where the link will be added.

   • Description (optional): Enter a description for the link.

   • Tags (optional): Add tags to help categorize the link.

5. Click Create.

Internal Investigation ID(s) 

HLP-17441

This will be released on the next documentation update in Helpsite

Environment

• Qlik SaaS

Compare Infoproviders between BW HANA environments using Gold Client BW

One of the most asked and key features of Gold client BW is to provide the users with a feature to Compare infoproviders between two BW HANA environments. This unique feature also list’s out the differences of the object between the two environments.

The function allows the comparison of various types of Infoproviders like Info Objects, Info Cubes, DSO and ADSO’s structures and properties between the current BW system and a target BW system.

Upon entering one or multiple Info Objects, Info Cubes, DSO’s or ADSO’s the function will compare the structure and properties of the same against the Info Objects, Info Cubes, DSO’s and ADSO’s in the target system specified by the user.

Example:

Source BW Environment = A4H(BWonHANA)

Target BW Environment = BW2(BW4HANA)

infoprovider to be compared between two BW environments = 0MATERIAL

Login to the source BW system which in this example is A4H and use the tcode ZGOLDBW for Gold Client BW

Click on Compare Info provider button under the Infoprovider Equalizer section as shown in the figure below

In the next screen we need to provide the details of the info provider which we would like to compare with the Target BW system. Below are the steps to be followed

Compare Info Provider for on your target system.

• Select InfoObject radio button from the Info Provider type
• Enter InfoObject(s) in the Info Provider Selection which in this case is 0MATERIAL
• Enter the RFC destination of the BW system the Info Object is being compared to – Which in this example is BW2

• Press the Execute button

Once the execute button is pressed the user will be asked to login to the target BW system which in this example is BW2

After logging in successfully, the system will compare the infoprovider between the two BW environments and will list out the differences of the infoprovider between the environments.

In the below example of 0MAERIAL we can see that the length, output length, number of attributes and the attributes which is missing or differ from the source BW system will be displayed.

Similar to 0MATERIAL the user’s will be able to compare the below different types of infoprovider between two BW HANA and BW Non-HANA environments

• Info Object
• Info Cube
• Data store Objects
• Advance Data store objects

Environment

• Qlik Gold Client  

Aside from filtering data records with Section Access, I'm usually requested to filter the objects and sheets to which users have access.  To be honest, I don't like to filter individual objects since this implies a lot of mainteinance and a more complex Section Access table. So I usually describe the scenario to the IT people and Key users and they always ( so far ) decide to go for the Sheets-Security option. Which in my opinion is the best alternative because it's more common to hide a complete block of information/analysis from a user than just a single KPI.

I've seen some examples like this before in the Community, however everything I've found its exemplified based on the USERID field. So, when I tried to use those examples I ran into some common errors when dealing with Section Access, and now that I've solved them I want to share a small example about how to go when using NTNAME.

Also, I want to note that there are some easier/faster alternatives to this approach. For instance you may use the MATCH() method described in this thread: Secured sheets

However if you're already using Section Access to filter data, you have too many users and/or sheets to use the MATCH() method, or if you just need to handle those privileges outside of the .qvw ( e.g. a user will maintain an excel file), you can use/reuse Section Access to accomplish this requirement.

In my example I use INLINE loads in order to allow other people to reload the app, however you could load all tables, including the SECTION ACCESS table, from xls or qvd files.

So, in order to test this example you have to do the following:

1. Download the enclosed file
2. In the script, go to the Section Access tab and uncomment the disabled code.
3. In the same tab, change the NTNAME and USER field values in order to reflect your local/domain users.
4. Go to Document Properties/ Opening and enable Initial Data Reduction Based on Section Access. Do not enable "Strict Exclusion"
5. Save and reload the app.
   • Note. If you have and use Publisher you'll need to add the account that runs QlikView services as an admin profile in the Section Access table before you reload and distribute the app.
6. If you only use the Desktop, save and exit any QlikView instance and reopen the application. If you want to check all profiles you'll need to sign out of Windows and sign in as the profile's user. You'll probably have to copy the file into a usb in order to open it in all the Windows accounts.
7. Depending on the user you used to sign in you should get the following results:
   • If you signed in as the admin user (first record in Section Access table), you should be able to see both sheets (A and B) and all Countries and Cities.
   • If you signed in as the first user (second record in Section Access table), you should only see sheet A but all Countries and Cities.
   • If you signed in as the last user (third record in Section Access table), you should only see sheet B and cities from MEXICO and USA.

Go to the following link to get the example file:

Sheets Security with Section Access File

I hope this example is useful and don't hesitate to ask if you have any doubt regarding it.

Issue:

Qlik data replication tasks from DB2 iSeries can fail due to communication errors with the source system. A common cause is that log stream tasks fail to resume because the CURCHAIN journal receiver has exceeded its connection limit. Below are the details of this issue and recommended solutions.

Scenario:

A log stream task configured with the Advance Run option, starting from a specific timestamp, failed and returned the following error:

The system logged this error message:

Troubleshooting Findings:

• Behavior When Resuming from Timestamp: When a task is resumed using a specific timestamp, the system references the journal receiver library *CURLIB and the receiver name *CURCHAIN.

  The following warnings were observed:

   

   

  00051632: 2024-10-03T01:39:14:491526 [SOURCE_CAPTURE] Execute status=1022502, native=-443 (db2i_endpoint_capture.c:1602) 00051632: 2024-10-03T01:39:14:491526 [SOURCE_CAPTURE] Execute status=1022511, native=-502 (db2i_endpoint_capture.c:1602)

Root Cause:

The CURCHAIN journal receiver has a connection limit (in this case, 1024 connections). When the environment exceeds this limit, the task fails if it attempts to resume from a timestamp.

However, if the task is resumed normally (without a timestamp), the system reads from a different receiver name, bypassing the CURCHAIN limit, and the task runs successfully.

Resolution:

To resolve the issue:

• Monitor Journal Receiver Chains: Regularly monitor the journal receiver chain to ensure that connection limits are not exceeded.
• Receiver Management: Implement a cleanup mechanism to detach old or unused journal receivers.
• Increase Connection Limits: If the environment requires it, consider increasing the CURCHAIN connection limit to allow for more connections during data replication processes.

This video series is designed to introduce you the Qlik NPrinting PixelPerfect report editor.

You can download this tutorial version also in PDF. The PDF contains all the formulas that are used in these tutorials.

Introduction
1. Working with PixelPerfect reports - Introduction	2. Getting started creating PixelPerfect Reports
Working with formulas
3. Add a formula to show the report creation date	4. Using formulas to calculate revenues
PixelPerfect designing features
5. Adding a Qlik Sense chart as an image	6. Creating a native PixelPerfect chart
7. Creating a native PixelPerfect table
PixelPerfect advanced features
8. Using scripts in a PixelPerfect reports	9. Using conditional formatting in a PixelPerfect report
10. Adding a sparkline in a PixelPerfect report	11. Adding page info a QR code in the page footer of the report
12. Adding images inside a level of report	13. Adding a table of content in a PixePerfect report

Intro

Data Transformation functionality was introduced in Gold Client version 8.0. It allows the transformation of fields during the Export of data in Gold Client. This functionality is delivered with default configuration; however, users are provided the flexibility to define their own data transformation routines for any table and field. 

Summary:

When using Qlik Gold Client, you are unsure if the transformation is working. 

Here is a step by step to validate all the stages of transformation. 

How To Make Sure Transformation is active: 

     1. Check that the rule(s) exist in /n/HTG/ZGOLD > Data Transform > Transformation Setup. 

     2. The configuration needs to be active and have a valid rule. 

In this example, transformation rule BP01 (designed to transform Business Partner data) is scrambling BUT0CC-CCNUM (Payment Cards Number) and EKUN-SOC_SECURE (Social Insurance Number). 

     3. Transformation rules only add value if they are assigned to Data Echo and/or Client Construct data types.

This assignment is important because the data transformation will occur automatically during the export process but only for those data types which have a rule assigned. 

To provide an example, transformation rule BP01 (designed to transform Business Partner data) could be assigned to Client Construct data type BUSINESS PARTNERS and/or to Data Echo data type CA - BUSINESS PARTNERS.  

Users who initiate exports do not need to take any actions for transformation to occur because of this rule assignment.  

     4. During the Export, please make sure that the transformation rule(s) is/are being called. Go to the job monitor (SM37) of the main job and check the job log. 

For more information, see the Data Transform User Guide found at: 

Data Transform User Guide 8.7

Tags:

• Qlik Gold Client
• Transform data
• Scramble data
• Anonimization
• SAP
• S4HANA

Labels  

• Qlik Gold Client

Environment

• Qlik Gold Client 8.7
• SAP S4 HANA  
• SAP ERP

SAP Flexible Workflow is a new concept introduced in SAP S/4HANA to simplify workflow configuration.  community.sap.com -> Flexible Workflows 

If you have recently used Qlik Gold Client to copy SAP Flexible Workflows into your target client and there are some steps missing, the most likely root cause is that table SWF_FLEX_PROC has RAW fields and Gold Client can have trouble determining the full length of these fields.

Please check the instructions below:

Gold Client > Client Exporter > WORKFLOW FLEX TABLES

In this case, check the Gold Client Configuration before the export to ensure that the Table Compression Type for SWF_FLEX_PROC (or any other related table with RAW fields) is defined as New Compression.

Gold Client > Configuration > Data Copy Toolbox > Set Table Compression Type

If you cannot resolve the issue, freely submit a support case, and the Qlik Gold Client team will respond accordingly. 

Note:

If Client Construct Data Type WORKFLOW FLEX TABLES doesn't exist, it can be included in the data model, adding all SWF_FLEX_* relevant tables.

 

Tags:

• Qlik Gold Client
• Flexible Workflow
• SAP
• S4HANA

Labels  

• Qlik Gold Client

Environment

• SAP S4 HANA  

Config REST connector for Gemini

URL: https://generativelanguage.googleapis.com/v1/models/gemini-pro:generateContent/?key=XXXXXXXXXXXXXXXX...

XXXX is your API Key

Method: POST

Body:

{"contents": {"role": "user","parts": {"text": "Give me a recipe for banana bread."}},

"safety_settings": {

"category": "HARM_CATEGORY_SEXUALLY_EXPLICIT",

"threshold": "BLOCK_LOW_AND_ABOVE"

},

"generation_config": {

"temperature": 0.9,

"topP": 1.0,

"maxOutputTokens": 2048

}

}

ALLOW WITH CONN

Config the script in Qlik Sense SaaS

Then in your script you need to create your question in a variable (like vResponseBody) and use in the Query

WITH CONNECTION (BODY "$(vResponseBody)");

Relative links

https://www.reddit.com/r/qlik_sense/comments/1f6vk0h/sentiment_analysis_using_gemini_and_qliksense/

https://www.reddit.com/r/qlik_sense/comments/1f6m35g/how_to_use_gpt_free_to_analyze_social_media_in/