Qlik Community

Michael_Tarallo · 2015-03-25

Set analysis offers a way of defining a set (or group) of data values that is different from the normal set defined by the current selections. Watch this video to learn more about the cool stuff you can do with Set Analysis as well as to get a better

...

Set analysis offers a way of defining a set (or group) of data values that is different from the normal set defined by the current selections. Watch this video to learn more about the cool stuff you can do with Set Analysis as well as to get a better understanding of its expression syntax and how it can be used.

Previous Video: A Beginners' Introduction to Set Analysis

NOTE: For a more complete and deeper dive of Set Analysis check out this excellent document: Set Analysis: syntaxes, examples

Sample App: (Qlik Sense is .qvf - QlikView is .qvw)

See attached .qvf file for sample Qlik Sense application
If using Qlik Sense Desktop:
- Copy .qvf file to C:\Users\<user profile>\Documents\Qlik\Sense\Apps
Refresh Desktop Hub (F5)
If using Qlik Sense Server
- Import .qvf using the QMC and then access from My Work Stream in hub
For the .qvw - just opened with QlikView Desktop

Set Analysis Expressions:

Conditions (hard-coded lists,searches,wild card,comparison operators, not equal):

Sum({$<CategoryName={'Bath Clothes'}>}Sales)Sum({$<CategoryName={'Bath Clothes','Babywear'}>}Sales)

Sum({$<CategoryName={"*Clothes"}>}Sales)

Sum({$<CategoryName={"*Clothes",'Babywear'}>}Sales)

Sum({$<Year={'2013'}>}Sales)

Sum({$<Year={2013,2014}>}Sales)

Sum({$<Year={"<2014"}>}Sales)

Sum({$<Year-={2014}>}Sales)

Conditions using variables:

SET vDefaultCat = 'Bath Clothes';

Sum({$<CategoryName={$(vDefaultCat)}>}Sales)

$ Expansion - using functions to evaluate comparisons values:

Sum({$<Year={"$(=Year(Today()))"}>}Sales)

Conditions with multiple dimensions:

Sum({$<CategoryName={'Babywear'},Year={2014}>}Sales)

Conditions with measures:

Sum({<[Model Variation]={"=SUM(PartSales) >=1000"}>}PartSales)

Anonymous · 2011-11-23

A QlikView Technology White Paper

Published: November, 2011

www.qlik.com

Introduction

Event Driven Execution (EDX) allows the running of QlikView batch jobs based on external events. The primary usage of EDX is to have an external scheduler or Extract,T

...

A QlikView Technology White Paper

Published: November, 2011

www.qlik.com

Introduction

Event Driven Execution (EDX) allows the running of QlikView batch jobs based on external events. The primary usage of EDX is to have an external scheduler or Extract,Transform, and Load (ETL) tool (for example, Informatica) run QlikView batch jobs as part of a larger batch process. There are also other use cases, forexample, triggering a QlikView batch when a file arrives and user-initiated batches. This document describes the requirements for each of these three use cases.

The example code (attached below as QMSEDX-v1.exe) and executable (attached below as QMSEDX_CommandLine_v1.exe) that is delivered together with this document uses the new EDX API, which means this document and the example code are useful both when using the EDX functionality for the first time and when migrating from previous versionsof the EDX API.

Use Cases

Using an External Scheduler or ETL Tool

Many large organizations have other batch requirements than just QlikView. Such organizations often use a standard tool to run all batch processes, so that the IT training costs are minimized and common operations (for example, logging and re-launching) are handled in one central place. In addition, QlikView batch processing is almost always reliant on new data being available and in many cases this data is created by other batch processes. An external scheduler or ETL tool can be used to prepare the data and then run the QlikView batch to load it.

Most scheduler and ETL tools are designed to run external batch programs via a command line interface. The reasons for this include:

Batch processes are long-running, so starting aseparate process to handle them provides a simple way for the scheduler tomonitor the batch processes.
The scheduler can be configured to run the batchusing a particular operating system account that provides the required securitycontext.
If the batch process fails, the standard operatingsystem error handler can be used to pass this information back to the scheduleror ETL tool.

The main alternative to using a command line tool is using some sort of web services. Unfortunately,web calls cannot be used for long-running processes, since a timeout eventually occurs. A common solution to this problem is to start the process with a web request and then follow it up regularly with subsequent requests to find out if the original request has finished successfully or not.

File Arrival Detection

In many IT departments, the inter-system communication is not orchestrated by ETL or even Enterprise Application Integration (EAI) tools. Instead, files are pushed. For example, a non‑QlikView batch job that extracts data from a production database is typically owned by the IT team running the database, which means the team is responsible for creating and configuring the batch job and ensuring that it runs correctly.On the QlikView side, a program that checks if a file has arrived must be running, so that the file can be used in a subsequent QlikView batch.

In this scenario, simply starting the QlikView batch process should be enough. There is no need to follow its execution. The program is typically a Windows service as it runs all the time.

User-initiated Action

TheQlikView Automation API provides a number of functions that run QlikView batch scripts, for example, Document.Reload(). However, it is normally not a good idea to let users run batch jobs, since a QlikView batch job typically replaces the entire data set of an application used by many users.This means a single user can interfere with the work of all the others unless the QlikView batch is carefully configured. Because of this, the batch functionality is disabled in a QlikView Server deployment.

The marketing department at a pharmaceutica lcompany has an organizational division between the analysts and the product managers. The analysts are in charge of loading a dozen external data sources of varying quality. Once the data relating to a particular product is ready, the running of a QlikView batch loads the data and publishes the application to the relevant product managers.
A software company with thousands of employees wants each employee to have access to their sales data up to the last 30 minutes. The computer resources needed to support such data usage are massive. Since the salespeople only use the system once a week on average, it is decided to allow each user to run a QlikView batch to generate a QVW (for the user)whenever needed.

EDX in QlikView 11

In QlikView 11, EDX runs through the QlikView Management Service (QMS) API. This is a major change from QlikView 10, where EDX is realized by calls directly to a QlikView Distribution Service (QDS). The QMS API is a web service API that uses theSimple Object Access Protocol (SOAP). Client applications make HTTP (web) requests to QMS on port 4799. The system is secured by NT LAN Manager (NTLM) as well as special protective measures to avoid certain types of hijacking attacks known as “time limited service key”. This combination of security means the client application must be written in .NET and therefore the provided example code is .NET projects/solutions developed in Microsoft Visual Studio 2010.

The client application uses NTLM to authenticate a Windows account to QMS. QMS then checks which Windows groups the Windows account for the client application is member of to determine the function calls the user is allowed to make. Most of the QMS API requires membership in a local group called “QlikView Management API”, but to run EDX, a separate group, “QlikView EDX”, should be used. Both groups are local Windows groups on the server where QMS runs.

The client application makes calls to instruct QMS to start a task and in return receives an error code indicating success or failure, as well as an execution ID. The execution ID uniquely identifies the execution of the task as opposed to the task itself. The client application then periodically polls QMS to check the progress of the task execution. This poll request returns a data structure that contains, among other things, the execution status, start time, stop time (if already finished), and a list of new execution IDs. These subsequent execution IDs represent the execution of tasks that are triggered because the initial task has finished. The client application can then follow an entire set of inter-related tasks that together make up a full parallelized batch flow.

Lastly, the example code outputs log information and returns an error code to the operating system. The error code can be recovered in a standard way, for example, by using the %ERRORLEVEL% environment variable in a BAT file.

Changes Compared to Previous Versions

From a functionality perspective, the following has changed compared to using EDX in QlikView 10:

Because communication is with QMS instead of QDS, a QDS cluster can be used to load balance EDX calls. If high availability is required for the batch environment, QMS is a single point of failure. Consider either virtualization of QMS or an active/passive system.
The overall success or failure code of aQlikView batch can be returned. Previously, the status of the task could be fetched (that is, the definition of the batch job), but not the execution ofthe batch job.
The execution of subsequent tasks can be followed.

Notes on Using the QMS API

Finally,some notes on using the QMS API in QlikView 11:

The EDX password must not be null. Use an empty string, if no password is configured.
The QDS ID is required in the function call that starts the EDX batch job. However, higher-level privileges than membership in “QlikView EDX” are required to get a QDS ID. The simple solution is to pass an empty GUID for the QDS ID, which means QMS will search for the task in all configured QDS instances. Almost all projects today only have one logical QDS configured, so this can be a useful solution.
EDX can be used without a QlikView Publisher license. Simply pass the name of the QVW instead of a task name and the reloadschedule of the QVW will run.

Colin-Albert · 2014-02-24

The attached file SectionAccess.zip contains several example documents that show how Section Access can be used to restrict access to specific users and to specific data.

This is not a guide on how to use Section Access, but instead shows some of

...

robert_mika · 2015-02-17

What it is used for?

The Qualify statement is used for switching on the qualification of field names, i.e. field names will get the table name as a prefix.’

In situation where we have two (or more) Tables with the same field names:

Product	Payment

The Qua

...

Dalton_Ruer · 2016-12-15

I recently spent a lot of time digging through materials trying to prepare for a webinar I did today on Section Access in Qlik Sense.

What I found was that while there are a gajillion posts on Section Access in QlikView I found very little detail abou

...

Michael_Tarallo · 2015-05-22

In this video I will introduce you to a concept known as the Master Calendar which can be used with both QlikView and Qlik Sense. A Master Calendar is simply a table that contains a time period and time attributes that you define, linked to your exi

...

dominicmander · 2016-01-25

Date formats can often be challenging for beginners, particularly when different data sources supply dates in different formats, and your reporting requirements specify yet another final date format.

There are two main functions for dealing with da

...

Date formats can often be challenging for beginners, particularly when different data sources supply dates in different formats, and your reporting requirements specify yet another final date format.

There are two main functions for dealing with date formats.

Date() and Date#()

Often I see people using the two functions in different combinations with a bit of trial and error until they get the result they want, without really understanding why it has worked. But, with a few simple basics understood, this is easy to understand.

First: Dates are a dual data type

Date fields, once properly formatted, are a dual data type. This means that each value in the field has two components, a string (text) representation of the date, and an underlying numeric component.

You may have seen when working with dates in Microsoft Excel that sometimes when you change the format of a date cell you end up with 42019 instead of 15/01/2015. This number is a difference, in days, between the date and an “epoch date”, in this case 01/01/1900. This is done, because this way it’s much easier to subtract one date from another to find the difference or add a number of days, months or years to a date to forecast for example.

Second: Each function manipulates only one component of that dual data type

Date#() takes a date string (e.g. the text ‘15/01/2015’) and calculates and adds the numeric component to make the proper dual data type date field. It leaves the string component unchanged.

Date() takes a number (either the number 42019 or an already properly interpreted date field with the underlying numeric component already in place) and calculates and adds (or changes) the string component. It leaves the numeric component unchanged.

Both functions take an optional second parameter as a format string for how to interpret in the case of Date() or how to format in the case of Date#(). If this second parameter is not provided, the default set in the script variables is assumed.

Example: A string in the format 2015-01-15 needs to be loaded, interpreted as a date, and formatted to 15/01/2015.

Let's assume the source field is called [OrderDate].

Solution: Date(Date#([OrderDate], 'YYYY-MM-DD'), 'DD/MM/YYYY') as Date

Reading from the inside out, we take the [OrderDate] field, apply Date#() to interpret the string and calculate the appropriate numerical component using the format mask YYYY-MM-DD, and then apply Date() to that result to reformat the string representation using the format mask DD/MM/YYYY.

Aside: Timestamps

Times and Timestamps take this simple concept one step further. In this case a time is again represented by a string (e.g. '12:00:00' or '15/01/2015 12:00:00') and the underlying numerical component will represent the time as a decimal fraction of a day (e.g. 0.5 or 42019.5). The corresponding functions are Time() and Time#() or Timestamp() and Timestamp#().

stevedark · 2013-10-16

Often when building QlikView applications, or picking up applications which have been built by someone else I want to have a quick and easy way of viewing the data that is in that application. To enable me to do this I have put together a page of ob

...

richbyard · 2015-09-06

The simple attached applications (and sample code) will allow you to setup your Qlik reload processing (in QlikView Server, Publisher or Qlik Sense) to kick off directly on completion of a process on a source system (e.g. an ETL process on a DW).

A si

...

The simple attached applications (and sample code) will allow you to setup your Qlik reload processing (in QlikView Server, Publisher or Qlik Sense) to kick off directly on completion of a process on a source system (e.g. an ETL process on a DW).

A simple process.

At the end of the ETL process the source database can be made to write a success value to a specific table in the DB for us to use as an indicator of a successful reload. For example, this might be today's date.
Create a Qlik application (QlikView or Sense) which will be scheduled at the start of the reload process and all other applications will be dependent on this, and any other dependencies they have. This initial application is a simple script that loops until a condition is met by the data being loaded. In this example, when the date loaded from the source DB is equal to today.
This application will be scheduled around the time of the earliest conceived completion of the source DB process or slightly before. This ensures that our reload process is ready and waiting for a completion record, in effect polling the source DB for a completion record.
As this application completes all subsequent reloads will start. This ensures that your reload process kicks off as soon as the source DB has completed processing and never before.

Tip 1 - Add in a pause to the loop so that your application doesn't spam the DB with requests.

A sleep command is added which pauses the reload following an unsuccessful pass. This could be set to every 10 or 60 secs to reduce a constant barrage of requests being sent to the DB. When the condition is met the script will exit the loop before the sleep is activated.

Tip 2 - Multiple triggers to set off different reload streams.

If you have a more complex environment where reload windows are constrained you could setup multiple triggers based on different event outputs from the DW. For example, where the DW completes a sales transactions fact but has many other dependent tables still to process you could have an event written to allow one stream of your reload to commence. The other streams could rely on different event triggers when all table reloads are complete.

Tip 3 - Keep it simple!

I have seen many different ways of setting up processes such as this, most of which IMHO are over-complicating something quite simple. One of the pleasures of working with Qlik products is the relative lack of complexity and the ease of understanding each implementation you come across. This is something we should promote rather than the tendency in IT to introduce/play with another bit of code or tool which makes debugging issues more technical. It also results in hidden steps in the process when someone new is introduced to the implementation, and documentation rarely gets the message across. When I say 'hidden' I would suggest that an initial trigger application in the reload schedule is very clear to someone new to the specific Qlik implementation, actions in source DBs or other programs are not immediately clear and require some digging etc to find and understand.

The example code based on a .txt file in Qlik Sense - change the LIB reference to a defined folder location for QlikView.

LET vDWDate = null();

LET vToday = num(today());

// Start loop script that will continue until condition is met.

Do until vDWDate=vToday

Trigger:

LOAD num(@1) as DWDate

FROM [lib://Trigger/Trigger.txt]

(txt, codepage is 1252, no labels, delimiter is '\t', msq);

LET vDWDate = peek('DWDate');

// where condition is met, exit and complete.

if vDWDate=vToday then

Exit Do

// where condition is not met, drop that table ready to restart the load process.

else

DROP TABLE Trigger;

// The sleep means that we can control the frequency of calls to the source database (or file).

Sleep 10000; // 10000 milliseconds = 10 seconds.

end if

// where condition is not met then restart loop.

loop

Try it yourself!

The attached files can be used to test this process.

Download them and have them in the same folder.
Kick off the reload of the QlikView document and watch as it loops through as the condition is not met.
Open the Trigger.txt file and change the date to today's date (format D/M/YYYY unless you change your date settings in the script)
Save the .txt file and watch as the next time the application loops it will complete the reload.
Simple!

Date formats

It is important to match the formats of the dates in both files. Check your global variables in the main tab to understand the settings you are assigning to the vToday variable as this will need to match the data being read from the source to complete the process. The screenshot below shows where to check in Qlik Sense, the same applies in QlikView.

hic · 2016-07-04

A calendar is very useful when you want to link your data to different time periods, e.g. when you want to display your KPIs over different years or months. Often you only have one date and you just want to use a standard calendar. For this case, the

...

A calendar is very useful when you want to link your data to different time periods, e.g. when you want to display your KPIs over different years or months. Often you only have one date and you just want to use a standard calendar. For this case, there are plenty of resources on this community. You can find a good overview on How to use - Master-Calendar and Date-Values..

If you have several date fields, you should most likely have several calendars defined in your script. See

Why You sometimes should Load a Master Table several times

Canonical Date

If you want to use a non standard calendar, like a fiscal calendar or a 4-4-5 calendar, the challenge becomes more difficult. See e.g. Fiscal Yearor Recipe for a 4-4-5 Calendar‌.

The script posted here will help you create a more complicated calendar. It has parametrized examples for the following calendars:

Standard Gregorian month-based calendar
Standard Gregorian week-based calendar
Standard Gregorian week-based calendar with broken weeks
The first and last weeks of the year do not span the shift of the year and they do not necessarily have 7 days
Month-based fiscal calendar
The Gregorian months are used as fiscal months, but January is not necessarily the first month of the fiscal year.
Week-based 4-4-4 calendar
The fiscal year is divided into 13 months with 4 weeks each. Occasionally there is a 14th month with one week.
Week-based 4-4-5 calendar
The fiscal year is divided into 4 quarters, each with 3 months. The first month of the quarter has 4 weeks, the second has 4 weeks, and the last has 5 weeks. Occasionally the last month of the year has an additional week.
Week-based 4-5-4 calendar
The fiscal year is divided into 4 quarters, each with 3 months. The first month of the quarter has 4 weeks, the second has 5 weeks, and the last has 4 weeks. Occasionally the last month of the year has an additional week.
Week-based 5-4-4 calendar
The fiscal year is divided into 4 quarters, each with 3 months. The first month of the quarter has 5 weeks, the second has 4 weeks, and the last has 4 weeks. Occasionally the last month of the year has an additional week.
Broadcast calendar
The months are week-based and start on the first day of the week that contains the first day of the Gregorian month.

There are several parameters that you can use to configure your calendar: The first month of the year, the first day of the week and the first week of the week-based year.

The script is commented, so hopefully you can read it and understand it. You can paste it straight into an empty app and run it to evaluate how it works. If you want to use parts of it inside one of your own apps, you may need to rename some fields.

The script should work in both Qlik Sense and QlikView.

Good Luck!

HIC

robert_mika · 2015-03-01

Bullet points:

MaxString and MinString belongs to Qlikview String Aggregation function.
They are equivalent to MAX and MIN for integers
They return the highest and lowest string value

What actually a value of a string?

This is the value described by ANSI

...

Bullet points:

MaxString and MinString belongs to Qlikview String Aggregation function.
They are equivalent to MAX and MIN for integers
They return the highest and lowest string value

What actually a value of a string?

This is the value described by ANSI character standard where 0 ( NULL) is equal to 0 and ÿ to 255.

(For full ANSI character set please attached dosument)

Taking only the standard English alphabet (A-z) ‘A’ will be the MIN and ‘z’ will be the MAX.

Remember:

In ANSI standard A < a and Z < z

(A=65,Z=90,a=97,z=122)

Language specific characters will be not recognized in the place where they are in your alphabet but they will be 'pushed' to the end

(see ANSI table)

MinString and MaxString works on dimensions (columns) only so you cannot use is to evaluate single character or strings .

(MaxString(‘a’,’z’…) is not supported

Examples:

1) MIN & MAX (textbox)

Data

Expression

Results

MinString(Category)

MaxString(Category)

2) MIN and MAX of String per group (Straight table)

Data

Expression

Result

MinString(Category)

MaxString(Category)

3) With condition:

Return Max and Min string based on another column

We are looking for MIN and MAX string value from column Category where Country='Poland'

Data

Expression

Solution

Result

MinString( if(Country='Poland', Category, null()) )

MaxString( if(Country='Poland', Category, null()) )

4) With LEFT/RIGHT

When used with LEFT the result will be the same as without as the value will be still based on first characters.

Data

Expression

Solution

Result

=MinString( Right(Country,1))

=MaxString( Right(Country,1))

5) With MID

Data

Expression

Solution

Result

=MinString( Mid(Country,2,2))

=MaxString( Mid(Country,2,2))

6) With Substring

When our data are little scrambled -TextBetween will return string between '.' and end of the row.

Data

Expression

Result

=MinString(TextBetween(Country,'.','',1))

=MaxString(TextBetween(Country,'.','',1))

7) In set analysis

When Criteria of Sales is 2 return Country with highest and lowest string Value

Data

Expression

Result

=MinString({<Sales={2}>} Country)

=MaxString({<Sales={2}>} Country)

😎 With Aggr()

For each Country return lowest and highest Category string value.

Data

Expression

Result

=Aggr(Minstring(Category),Country)

=Aggr(Maxstring(Category),Country)

If know about other ways of using these functions please let me know.

Kushal_Chawda · 2016-01-04

I 'd like to thank avinashelite‌ for sharing the method to load the multiple excel files with the multiple sheets. You can find the article at the link below.

Loading Multiple Excel Sheets Dynamically along with file name and sheet name

Unfortunately

...

I 'd like to thank avinashelite‌ for sharing the method to load the multiple excel files with the multiple sheets. You can find the article at the link below.

Loading Multiple Excel Sheets Dynamically along with file name and sheet name

Unfortunately, this method has some limitations. It only works when all files have the same number of non-blank sheets, and all sheets must have the same number of columns with identical names.

I’d like to share a method below that works effectively without these restrictions

1) Define some variables to automate process.

/* Change the below variable with actual folder path */

LET vExcelFilePath = '..\Files'; // Root folder path on which all the excel files are stored. It might contain sub folders as well.
LET vFileExtension = 'xlsx'; 
LET vQVDFilePath='..\QVD'; // Path on which a QVD is stored which contains data from all the excel files
LET vQVDName ='Sales_data'; // Name of a physically stored QVD File

2) Define function to get the list of all the files located inside the folder. It also scans sub folders inside the parent folder.

/* Below subroutine is created to get the lis of excel files stored in specific folder and sub folder within that */

sub ScanFolder(Root)

         for each vFile in filelist( Root & '\*.' & vFileExtension)

                FileList:
                LOAD '$(vFile)' as Files
                AutoGenerate 1;

         next vFile

                for each SubDirectory in dirlist( Root & '\*' )

                    call ScanFolder(SubDirectory)

                next SubDirectory

end sub

Call ScanFolder('$(vExcelFilePath)') ;

let vFile = Null();

3) Define a function to loop through the list of Excel files generated by the previous function. Let’s take a look at the connection string below. I’ve used CONNECT64 because I’m working with a 64-bit ODBC driver and Excel application. If you’re using 32-bit ODBC drivers and Excel, you may need to use CONNECT32 instead. The 'Excel Files' DSN is created on the machine using either 64-bit or 32-bit ODBC drivers. Typically, when Office drivers are installed, the 'Excel Files' DSN is created automatically, but in some cases, it may need to be created manually. If your DSN name differs, be sure to update it in the connection string below."

ODBC CONNECT64 TO [Excel Files;DBQ=$(vFile)];

/* Below subroutine is created to load all the excel file and store it into the QVD. */

SUB load_all_excel_files_and_store_in_qvd(LoadData)

  FOR EACH vFile IN FieldValueList('Files');

  ODBC CONNECT64 TO [Excel Files;DBQ=$(vFile)];

  Temp:
  LOAD *;
  SQLtables;
  DISCONNECT;

  [$(vTableName)]:
  LOAD * INLINE [
  junk ];

      FOR i = 0 TO NOOFROWS('Temp')-1

            LET vSheetName = PURGECHAR(PURGECHAR(PEEK('TABLE_NAME', i, 'Temp'), CHR(39)), CHR(36));

            Set ErrorMode=0; // Disable error mode to avoid error while loading blank sheet

            CONCATENATE([$(vTableName)])
            LOAD  *,
                  SubField('$(vFile)','\',-1) AS FileName,
                  '$(vSheetName)' AS Sheet_name
            FROM $(vFile)(ooxml, embedded labels, table is [$(vSheetName)]);

                 if len(FieldName(FieldNumber('A','Data'),'A'))>0 THEN

                      Drop Field A;  // When there is a blank sheet in the excel file, field A is created which we don't want

                 ENDIF
   
            Set ErrorMode=1;

      NEXT

  DROP TABLE Temp;
  DROP FIELD junk;

  NEXT 

let vFile = NULL();

    if NoOfRows('$(vTableName)')>0 THEN

        STORE [$(vTableName)] into $(vQVDFilePath)\$(vQVDName).qvd(qvd); // store into the QVD file

    ELSE

        TRACE "Data is not available";

    ENDIF

END SUB

CALL load_all_excel_files_and_store_in_qvd(LoadData);

DROP Table FileList;

Note: This Script is best suited for QlikView

Please feel free to offer any suggestions to improve this document.

Thanks & Regards,

Kushal Chawda

Patric_Nordstrom · 2019-11-10

At Qonnections 2019 I hosted a session with tips called “Qlik Sense Visualizations, best practice and top tricks”. I have received a lot of positive feedback, so I decided to make a part two with 41 more or less useful new tips.

Positive and negativ

...

robert_mika · 2015-04-07

Have you ever wondered how the examples from the Qlikview help may look like?

Please see below and enjoy responsibly...

Below() and Above()

07-Apr-15 3-22-43 PM.jpg

Returns the value of expression evaluated with the chart's dimension values as they appear on the row above the c

...

pchatziag · 2024-12-05

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

...

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:

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:

Create a new blank Qlik Application Automation
Upload workspace (right click on canvas) à DocuGen v(x).json
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.
(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.
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).

Patric_Nordstrom · 2021-05-10

More charts and tips for app development in Qlik Sense. This app and nr IV was used in the Top 10 Viz tips session at QlikWorld 2021. It's a mix of recipes for new charts and tips for app development. Content as follows, full descriptions and demos i

...

vinieme12 · 2017-01-12

Hi,

The Following blog was inspired by a recent post that allowed me to exercise my brain into trying something new

(link to thread: Qlikview Chart)

Below is one of the two charts to be achieved in Qlikview. This led / music equalizer style looked prett

...

richbyard · 2016-01-20

The aim was to theme an application with a variable driven color range that can highlight strong performance and fade off for less performant values. The out of the box palette allows you to do this with the Red to Yellow range but there are often re

...

The aim was to theme an application with a variable driven color range that can highlight strong performance and fade off for less performant values. The out of the box palette allows you to do this with the Red to Yellow range but there are often requests for more corporate color sets. I'm sure we'll see this addressed in future versions but for now there is a workaround (in the soon to be release v2,2 at least - read the Note section below for an issue that stops this working in v2.1 and the workaround to address it)

The approach aims to use an expression that doesn't need to be rewritten each time the measure changes and can be copied across different objects without changes. I managed to get something working as you can see below (click on the GIF below if it's not playing for you).

Animation 0.gif

In the end it is quite simple.

Steps to recreate two color range approach

1. setup two variables (vColorMixDark and vColorMixLight) to act as the boundaries for the color range we will use with the ColorMix1() function.

2. If you wish to play with the example application you will need the qVariable extension from Branch ‌‌(or use the attached). If you want to set these values just once you can do so through the variables window without the extension.

3. Create your charts and use the following formula in the Appearance > Colors & Legend > Color by Expression area

ColorMix1( rank(TOTAL column(1))/NoOfRows(TOTAL) , $(vColorMixDark), $(vColorMixLight))

To explain this formula (just in case):

Assuming that we have one dimension (can be a drill through dimension) and one measure. We use the value of the measure to weight the values to assign a color in the range using the ColorMix1() function. This is completed by ranking the measure results and dividing this by the total number of rows as color mix requires a value between 0 and 1. It is written using column(1) and NoOfRows() so that it doesn't hold the formula itself but references the appropriate field in the objects dataset.

Changes to recreate three color range approach

1. setup an additional (third) variable called vColorMixMiddle, this will set the middle color of the range.

2. use the following expression in place of the one above:

ColorMix2( (rank(total column(1))/(noofrows(TOTAL)/2))-1 ,$(vColorMixDark), $(vColorMixLight),$(vColorMixMiddle))

This formula assigns a value in the range of -1 to +1 as required for the 3 color approach based on your expression identified in column(1) of the dataset. I.e. the second field of your dataset which is typically the first measure after a single dimension field.

NOTE:

- This works on v2.2 (release Feb 2016). In v2.1 there appears to be an issue with sorting when such a color by expression is applied (not all color by expressions). In v2.1 and below you will need to follow the workaround below which is not quite so graceful but gets the same result.

Workaround for v2.1 and below

- The sort order is kind of critical, the only way of making this function in v2.1 was to use the formula itself as rowno(), rank(column(1)) and other options would all deactivate the sort order of descending values which is pretty important in most charts. As such your formula would be as follows:

ColorMix1( rank(total [your measure expression here] ) / NoOfRows(TOTAL) , $(vColorMixDark), $(vColorMixLight))

- Unfortunately this means there is always an admin element whenever you use this, and whenever you change the expression formula. However, a quick upgrade to v2.2. in a months time (as of writing) will have you back to quick and simple.

Taking this idea to the next level with a cool UI through the Color Styler extension

The Color Styler extension is a brilliant tool to make your applications look stunning and fully tailored to the organisation or use case your making it for.. Excellent work by jsn Johannes Sunden.

Any comments are welcome as there's always another way of approaching things and there are things to learn in every approach.

28 Likes · 2014-09-04

Here I am sharing my last extension for QlikSense: JSPivotTable.

This extension is built using Nicolas Kruchten’s pivot table found here.

I have integrated into QlikSense to work in two different modes, dynamic and static:

Static mode: The fields a

...

vikasmahajan · 2013-08-26

Dear all

Sharing Incremental Load concepts in Qlikivew using SQL .

Vikas

Qlik Community

popularResources

Date() and Date#()

First: Dates are a dual data type

Second: Each function manipulates only one component of that dual data type

Example: A string in the format 2015-01-15 needs to be loaded, interpreted as a date, and formatted to 15/01/2015.

Solution: Date(Date#([OrderDate], 'YYYY-MM-DD'), 'DD/MM/YYYY') as Date

Aside: Timestamps

Challenge:

Challenge:

Idea:

Input parameters for the python script.

Outcome:

App general information overview.

Sheets & visualizations section.

Data model section.

Script section.

Content of the package:

Installation:

Running the automation:

Features:

Closing: