One of our Qlik Community Buddy (Rupinder Singh) shared an awesome way to optimize the performance of group by statement but I don't think this got enough lime light which it should have. In order to give it better exposure, I am creating this document from this thread Optimize Group By

For below I am using QV version - 11.2 SR4

I have always had trouble with aggregating my data as "Group by" statements in scripts have always taken too long for my comfort.

For example, to group by  23 million records (2 columns - ID and Sales) it takes me 10 mins.

Table_Group:

Load ID,Sum(Sales)

Resident Table

Group By ID;

Instead of this, If I Order by my data by ID and then run the same Group by clause, I get results backs in less that 2 mins.

Table_Sort:

Load ID,Sales

Resident Table

Order by ID;

Table _Group:

NoConcatenate Load ID,Sum(Sales)

Resident Table_Sort

Group By Table_Sort;

I would have believed QV to internally perform this step, but explicitly implementing this has improved my query times by 500%.

I have tables with 40 columns (36 of which to be grouped by and 4 to be summed) and the query time has gone from 18 to 6 mins.

I have just ensured that my Order By column list is the same as Group By Column List.

So, If Order by is "Order By Column1,Column2,Column3" then Group by is "Group By Column1,Column2,Column3"

I would love to hear back if others in the community also have faced similar challenges and whether this or any other method has helped them improve Group by Performance.

Hello Everyone,

I have attached the document for the important Qlikview functions used in script as well as in UI.

Please have a look and also feel free to update the document or comment in the session for the functions which is missed.

Hi Qlikers,

Sometimes we can have requirement to calculate the business working hours between two Dates due to certain reasons. There are many solutions available on community but I thought let's create the document so that everyone can utilize this.

Consider the below case

Let's say one Ticket is logged into the system on certain day , call it as Start Date, and the same Ticket got resolved on certain day, call it as End Day. Now we may want to calculate the working hours between these two Dates to calculate the efficiency of ticket closure time.

1) Calculate business working hours excluding week ends(Saturday and Sunday) and Holidays.

Below are the considerations

1) Count only standard working hours ( 9 AM - 6 PM)

2) Exclude Saturdays and Sundays

3) Exclude Holidays

Below is the Setup in QlikView

You can change the Date format of the below variables as per your convenience eg. 'DD/MM/YYYY hh:mm:ss' to 'DD-MMM-YYYY hh:mm:ss'

SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss';

First, convert your Timestamp date format to 24 hour format using the below functions

a) Timestamp# ()    (if your Timestamp values are text) 

b) TimeStamp ()     (If your Timestamp values are in proper Timestamp format)

Now you can setup the below variables for standard working hours in weekdays.You can change the Variable according to your working hours (Here 9 AM - 6 PM) and rest calculation will be done automatically

Now Setup the Holiday list as below. You can use your own holiday list

Below is the logic to calculate the business working hours between to dates

Here is the output

2) Calculate business working hours excluding week ends(Saturday and Sunday), Lunch Breaks and Holidays.

Below are the considerations

1) Count only standard working hours ( 9 AM - 6 PM)

2) Exclude Saturdays and Sundays

3) Exclude Lunch Break (1 PM - 2PM)

4) Exclude Holidays

Set the Variables for standard working hours and lunch breaks. You can change the values according to your needs

Include the Holidays

Below is the logic to calculate the business working hours between two dates

Here is the output

Please see the attached QVW.

Feel free to provide any suggestions.

Regards,

Kushal

Hey there,

I am starting this article to yell out to the community, that I have completed a generic script (suitable for QlikView and Qlik Sense using the Qlik REST Connector) to use the API of Synthesio for loading data.

I am not sure how many folks will search for this combination, so before I document hours of "how to do" here, please ping me a note (reply below or email me at christof.schwarz@gmx.at) that you're interested. I will then share it here. If noone ever asks for this combination, I saved a lot of time 😉

The idea is the following:

• Synthesio offers several REST APIs
• You have to get an accesstoken which is valid for some hours. My script is doing so.
• You can query Synthesio's Mention API, the one you likely look for are
  • the Reach method Synthesio API Reference and
  • the Analytics method Synthesio API Reference#
• The result is a json object, which unfortunately does not translate automatically into a workable datamodel when using the REST Connector wizard.
• I had to load the result basically into one cell (one row) and then parse the content accordingly.

Excerpt from the "magic script":

Hierarchies are an important part of all business intelligence solutions, used to describe dimensions that naturally contain different levels of granularity. Some are simple and intuitive whereas others are complex and demand a lot of thinking to be modeled correctly.

• What is the difference between a balanced and an unbalanced hierarchy?
• How can a hierarchy be loaded into QlikView?
• Which data model should be used?
• How do I create an authorization scheme using a hierarchy?
• How can I check for data integrity problems in the hierarchy data?

These questions and others are answered in this Technical Brief. Attached you will also find a sample unbalanced, n-level hierarchy along with the script to load it.

See also the following blog posts:

Unbalanced, n-level hierarchies

Authorization using a Hierarchy

HIC

2013-11-26: Added section on authorization.

Hey,

I hope I can solve a lot of peoples headache with a better way of loading JSON data.

This method works by loading the JSON data into an table with a single field, and then placing it into a variable and then converting the value into xml which then is loaded. Works very well but is still a bit for the technical people. I will not provide any support.

In order to make this work, we need to detect the XML definition after JSON to XML conversion. Open the JSON Loader.qvw Application and the JSON url.

Copy the JSON output into the vData variable, and click the JSON to XML button. Now take the output in the left side and copy that into a .xml file and load it from qlikview.

The output script should look similar to this:

TAG:

LOAD adminCode3,

    adminName2

FROM [Desktop\xml.xml] (XmlSimple, Table is [XML/POSTALCODES/TAG]);

From this we need to extract 2 things. The list of fields (place into vFields), and the XML tree to load (place into vXMLTree).

In the edit script set the variables based on the above:

Let vUrl = 'http://api.geonames.org/postalCodeLookupJSON?postalcode=6600&country=AT&username=demo';

Let vFields = 'adminCode3,adminName2';

Let vXMLTree = 'XML/POSTALCODES/TAG';

Cross fingers and reload

Do this for each JSON source.

Enjoy Torben Seebach 4C Management Consulting

Hello everyone!

Higher management often requires specific data delivered to their mailbox every morning. They are not interested in interaction, using filters and “playing” with data. They just want their predefined Excel ready when they will turn on the PC. Of course great solutions like Qlik NPrinting are available, but there is a way to do it even with QlikView Personal Edition.

This document describes a methods for:

- Automated QlikView document reload

- Automated data extraction from QlikView to Excel file.

- Automated distribution of this Excel file to a specific recipient, using free Gmail account

- Scheduling and fully automating whole process with Windows Task Scheduler

You can find all files in the attachment.

TLDR: Learn how to fully automatically reload, extract and send data by email using Gmail account!

All inputs are welcome. I hope someone finds it useful!

PS. This document is based on one of mine QlikView blog posts.

BR,

Kuba

"How to discover your data in a fast dynamic way?"

When starting a new project the first thing I want to do is get a feel for the data. Preferably in a fast and dynamic way. You can use the "Advanced Data Discovery module" to tackle this problem. The Advanced Data Discovery module consists out of 4 dynamic list boxes and a dynamic table which comes in handy when modeling a project, checking data quality, testing the interaction between fields or exploring your data.

Screenshots:

Advanced Data Discovery sheet overview

Dynamic list box with field statistics

Benefits:

• Useful to check interaction between fields while creating your data model
• Dynamic Data Discovery
• Field statistics based on current selections or based on the complete set
• Fast data quality checks
• Dynamic list box

Cons:

• A script reload is required

PS: Special thanks to the creator of the first version of the dynamic list box from who I could not trace back the blog post I encountered 3 years ago.

The Advanced Data Discovery module is now part of the QvM - Qlikview Modules template.

Thanks for your feedback and comments.

Have fun Qlik'n, Koen

‌QlikViewModules.com

Hi everyone,

Here is a file to write the content of a QVD into an MS SQL database with minimum amount of coding.

The script uses bulk insert function in SQL and inserts 1,000 rows at a time.

There are instructions/assumptions in the front-end of the app.

1. Ensure the source QVD has the columns correctly formatted on the first run (as it creates the SQL table based on the coded Qlik data types - even though Qlik data types technically don't exist) as this determines the 'data types' it builds into the SQL table.
2. Replace the existing "CONNECT TO" string to the database server.
3. Point to the QVD you want to write to SQL.
4. Script will change/replace all special characters in data, table and field names. Underscore will replace any 'space' in table and field names and the latter 2 will be in 'all caps' to follow DB naming-convention.
5. Script will create a table in the database (the database itself must exist or be created prior to launching the script).
6. Re-run the script after the script has created the target SQL table and the script will (bulk) insert 1,000 rows at a time.
7. File creates/updates one "DB_Write_Log.qvd" QVD with stats of what has been written to the SQL table by this script/file (keeps a historical record).
   

Tentative stats are: (a) Writes 1,000 rows (one insert) every 1.63 seconds for a table 7 columns deep; (b) Writes 45,637 rows in 58 seconds for a table 10 columns deep.

Hope you find this useful.

A video tutorial here: Qlik Bulk INSERT into SQL table - YouTube

Have fun,

C  ;o)

HI All,

The below script helps in creating the Master Calendar with Working & Non working (Holiday) flags.  Holidays are loaded from the Excel file.

LET vWeeklyHolidays = 'Sat;Sun';   // Adjust weekly holidays here, currently considered Saturday & Sunday as holiday, based on your requirement change it accordingly

CalendarMaster_Temp:
LOAD
    Date(InvoiceDate) AS InvoiceDate,
    Year(InvoiceDate) AS Year,
    'Q' & Ceil(Month(InvoiceDate) / 3) AS Quarter,  
    Month(InvoiceDate) As Month,
    Day(InvoiceDate) As Day,
    WeekDay(InvoiceDate) AS WeekDay,  
    Week(InvoiceDate) As Week;
Load
     Date(MinDate + IterNo() -1 ) AS InvoiceDate  // Replace InvoiceDate with your actual date field name
While (MinDate + IterNo() - 1) <= Num(MaxDate);
Load
    Min(InvoiceDate) AS MinDate,
    Max(InvoiceDate) AS MaxDate    // Replace InvoiceDate with your actual date field name
RESIDENT Invoice;                  /// Replace Invoice with your actual Transaction Table.

LEFT JOIN(CalendarMaster_Temp)
LOAD Date(Holiday) AS InvoiceDate,
     Description AS HolidayDescription,
     1 AS IsHolidayFlag
FROM
[List of Holidays.xlsx]   // Load holiday details from Excel file attached, if you have this info in database tables change it accordingly
(ooxml, embedded labels, table is Holidays);  

Calendar:
LOAD
  InvoiceDate,
    Year,
    Quarter,  
    Month,
    Day,
    WeekDay,  
    Week,
    HolidayDescription,
  If(SubStringCount('$(vWeeklyHolidays)', WeekDay(InvoiceDate)) OR IsHolidayFlag = 1, 1, 0) AS IsHolidayFlag,
  If(SubStringCount('$(vWeeklyHolidays)', WeekDay(InvoiceDate)) OR IsHolidayFlag = 1,'Non Working/Holiday', 'Working Day') AS DayType
Resident CalendarMaster_Temp;

DROP TABLE CalendarMaster_Temp;      

Regards,

jagan.

A common metric with a business is to compare  current period with previous period a over same number of days.

few fall into mistake of comparison of current period with the whole previous period.  Take for example Month To Date Sales compared with Previous Month.   If the current date is 15 of the month the comparison is made over 15 days of the current month with 30 days of the previous month and this is not apple to apple comparison and the result will be on always down until reach a day of 30.  To overcome this I have created a master calendar to overcome the period comparison for WTD,MTD,QTD and YTD flags along with previous period Flags.

   if (TempDate >= monthstart($(vToday),-1) and TempDate <= monthstart($(vToday),-1)+ $(vToday)-monthstart($(vToday)) ,1,0) as PrMTDFlag,  //Prior Month Flag

in this Prior Month Flag is set over only same number of days for the current period.  Same follows for MTD vs PrMTD, QTD vs PrQTD and YTD vs PrYTD

The Script for creating an apple to apple Period Flags :

MinMax:

LOAD

  Min([Sales Date]) as MinDate,

  Max([Sales Date]) as MaxDate

RESIDENT [Sales];

LET vMinDate = Num(Peek('MinDate', 0, 'MinMax'));

LET vMaxDate = Num(Peek('MaxDate', 0, 'MinMax'));

LET vToday = $(vMaxDate);

//LET vToday = Num(Today(0));

/*************** Temporary Calendar *************

Generates a single table with one field containing

all existing dates between MinDate and MaxDate.

*/

//

TempCal:

LOAD

  date($(vMinDate) + rowno() - 1) AS TempDate

AUTOGENERATE

  $(vMaxDate) - $(vMinDate);

DROP TABLE MinMax;

/*************** Master Calendar ***************

Disconnected during the Date Island exercise by renaming TempDate as IslandDate

*/

SalesCalendar:

LOAD

// TempDate AS CalDate,

    TempDate AS [Sales Date],

  'Week-' & Week(TempDate) AS WeekDesc,

  Year(TempDate) AS Year,

  Month(TempDate) AS Month,

  Day(TempDate) AS Day,

  Weekday(TempDate) AS WeekDay,

   'Q' & ceil(month(TempDate) / 3) AS Quarter,

  makedate(year(TempDate),month(TempDate)) as MonthStartdate,

  date(rangemin(dayname(monthend(TempDate)),today())) as MonthEnddate , //Creating the field MonthEndDate

  Date(monthstart(TempDate), 'MMM-YY') AS MonthYear,

  Week(TempDate)&'-'&Year(TempDate) AS WeekYear,

    InWeekToDate(TempDate, $(vToday), 0) * -1 AS CurWTDFlag,

    InWeekToDate(TempDate, $(vToday), -1) * -1 AS PrWTDFlag,

    InMonthToDate(TempDate, $(vToday), 0) * -1 AS CurMTDFlag,

    if (TempDate >= monthstart($(vToday),-1) and TempDate <= monthstart($(vToday),-1)+ $(vToday)-monthstart($(vToday)) ,1,0) as PrMTDFlag,  //Prior Month Flag

  inyeartodate(TempDate, $(vToday), 0) * -1 AS CurYTDFlag,

    if (TempDate >= YearStart($(vToday),-1) and TempDate <= YearStart($(vToday),-1)+ $(vToday)-YearStart($(vToday)) ,1,0) as PrYTDFlag,  //Prior Year Flag

    InQuarterToDate(TempDate, $(vToday), 0) * -1 AS CurQTDFlag,                                                                   

    if (TempDate >= QuarterStart($(vToday),-1) and TempDate <= Quarterstart($(vToday),-1)+ $(vToday)-QuarterStart($(vToday)) ,1,0) as PrQTDFlag  // Prior Quarter Flag

Then YTDSales =

sum({$<CurYTDFlag ={1},Year=,Quarter=,Month=,WeekDesc= >}  [Sales Amount])

PrYTD Sales

sum({$<CurPrYTDFlag ={1},Year=,Quarter=,Month=,WeekDesc= >}  [Sales Amount])

and YTDCompassion =

num(  ( $(YTDSales ) - $(PrYTD Sales ))  /  $(PrYTD Sales ) , '#,##0%')

This way is more efficient than having a complex set analysis by adding these line to the Master Calendar and setting flags.

Ejemplos de Función Date# y Date para transformar fechas y luego darles formato. Esto como método alternativo a MakeDate, que usa 4 funciones haciendo más lento el load:

MakeDate(Left(20130628,4), Mid(20130628,5,2),Right(20130628,2))

1. Date#(20130628,'YYYYMMDD')

Devuelve 41453 en formato 20130628

2. Date(Date#(20130628,'YYYYMMDD'),'DD-MMM-YY')

             Devuelve 41453 con formato 28-jun-13

3. Date(Date#('2013.06.28','YYYY.MM.DD'),'DD-MMM-YY')

             Devuelve 41453 con formato 28-jun-13

4. Date(Date#('06-28-13','MM-DD-YY'),'DD-MMM-YY')

             Devuelve 41453 con formato 28-jun-13

5. Date(Date#('28.06.13' ,'DD.MM.YY'),'DD-MMM-YY')

              Devuelve 41453 con formato 28-jun-13

6. Date(Date#('jun.28.2011', 'DD.MMM.YYYY'), 'DD-MMM-YY')

              Devuelve 41453 con formato 28-jun-13

7. Para eliminar hora de una fecha

Date(Floor( '2013-06-28 07:30:38' ),'DD-MMM-YY')

                     Devuelve 41453 con formato 28-jun-13 sin minutos

8. Para obtener solo la hora de una fecha

Time(Frac( '2013-06-28 11:30:38' ),'hh:mm:ss')

     Devuelve 0,47960648148 con formato 11:30:38

9. Para obtener Fecha y Hora

             Date( Timestamp#( '20130628 11:30:38', 'YYYYMMDD hh:mm:ss'), 'DD-MMM-YY hh:mm:ss'   )

                      Devuelve 41453,479606481  con formato 28-jun-13 11:30:38

10. Para obtener día de la semana en fecha

             Date( '2013-06-28', 'WWW DD-MMM-YY')

                      Devuelve 41453  con formato  vie 28-jun-13

11. Resta un mes a la fecha

AddMonths('2013-06-28',-1)

Devuelve 41422 en formato 28/05/2013 (formato predeterminado)

12. Suma un mes a la fecha

AddMonths('2013-06-28', 1)

Devuelve 41453 en formato 28/07/2013 (formato predeterminado)

Esta nota esta publicada en el blog: Qlikview en Español

NOTAS:

Al indicar un formato de hora en Qlikview la letra mayuscula M= mes,  y letra minuscula m= minutos

Referencias (ingles)

http://community.qlik.com/blogs/qlikviewdesignblog/2013/02/19/why-don-t-my-dates-work

http://community.qlik.com/docs/DOC-3102

Hi all,

the product JDBC Connector reached end-of-life because it's technically outdated. Support of it ends 2018-12-31.

The successor product is the TIQ Java Service Connector and can be obtained here:

https://www.tiq-solutions.de/en/tiq-java-service-connector/

Best regards,

Ralf

--------------------------------

Hi everyone,

this is a demo version of our JDBC Connector for QlikView. It will enable a lot of non-ODBC/OLEDB sources for loading data into QlikView.

Also, it would give much more performance on QV 9 loads (Oracle, mysql etc.) and could speed up on complex network environments (like VPN, firewalls, WAN).

Please try it out and give us some feedback!

Installation instructions:

• install the JDBC Connector and config the JDBC driver (mostly a .jar file)
• connect with JDBC URL (e.g. jdbc:oracle:thin:@localhost:1521:ex)
• example connect statement for Oracle (will be included by connect popup):
  CUSTOM CONNECT TO "Provider=JDBCConnector_x64.dll;jdbc:oracle:thin:@localhost:1521:ex;XUserId=SbKdMaFNRLOA;XPassword=aWELJaFNRbTA;";

Missing feature (waiting for QV support):

• no select option for database/owner on table wizzard

Thanks to my colleague Sven "Benno" Uhlig who brought up this idea and implementation!

Ralf

UPDATE:

This is a new version which supports DDL and DML statements too. You will get a result set if a DML statement was executed with the update count.

Just try script calls like this:

DATA:

SQL CREATE TABLE test2 (col1 INT)

;

SQL insert into test2 (col1) values (1)

;

SQL update test2 set col1=2

;

SQL drop table test2

;

UPDATE:

New version comes with an installer and config dialogs. See also: http://www.tiq-solutions.de/display/enghome/ENJDBC for a list of possible JDBC sources.

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

Please see below and enjoy responsibly...

Dual()

Definition:

Forced association of an arbitrary string representation s with a given number representation x. In QlikView, when several data items read into one field have different string representations but the same valid number representation, they will all share the first string representation encountered. The dual function is typically used early in the script, before other data is read into the field concerned, in order to create that first string representation, which will be shown in list boxes etc.

Syntax:

where

text - this is first column of data

number- this is second column of data

Qlikview has specific way of storing data. Each field is represented by Text and Number data type called dual.

The Text format is visible to user and the Number is being used for calculation and sorting.

                    You can imagine that each field has top Text layer and bottom

                    (like salad in your Hamburger) Number layer.

This association works most of the time and for most of the data type but there are situation that we need to force this association to work the way we want.

Preparation

Remember

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

Qlik help  example contains two tables: inline and from csv file(sample attached)

load dual ( string,numrep ) as DayOfWeek inline

[ string,numrep

Monday,0

Tuesday,1

Wednesday,2

Thursday,3

Friday,4

Saturday,5

Sunday,6 ];

Directory;

LOAD Date,

    Sales

FROM

afile.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

//The last line  is not part of Qlikview help but you have to have this line to correctly load data into Qlikview

after Reload we have:

As you probably know when Values are on the left hand side they represent Text when on the right Number.

So why we have Text on the right?

In the background (during load) the numbers have been associated  with the text.

So what you see is the bun what you do not is the underlay salad:

Practical use:

Example 1

The simple but maybe not the most useful calculation is to add Days of the week from our data =sum(DayOfWeek)

Example 2

Counting days of the week:

Example 3

(please use attached xlsx file and below Script)

A survey has been conducted and the data in Excel file looks like this:

What we want is to assign more meaningful description to the rate numbers.

Script:

Data:

mapping load * Inline

[ Rate,Value

Very Good,5

Good,4

Average,3

Bad,2

Very Bad,1

]

;

Sort:

load

Responds,

dual(Rate,ApplyMap('Data',Rate)) as Rate

FROM

Survey.xlsx

(ooxml, embedded labels, table is Sheet1);

Now we can create Straight Table or bar Bar Chart:

This function can be use in many different ways.Please research our community to find more examples.

Still feeling hungry?

Do you Qualify?- How to use QUALIFY statement

How to use - Dimensionality()

Missing Manual - GetFieldSelections() + Bonus Example

MaxString &amp; MinString - How to + examples

The second dimension... or how to use secondarydimensionality()

Not authorized to view the specified document 8150

How to use- Dual()

Find the attachement to implement google maaps in qlikview. Documents with screenshots are avail. Attached the QVW file also for your reference. Regards. Siva Sankar k

I've re-edited the document  and fixed the  issue. Regards, siva sankar

The QlikView Management Console is a great tool for configuring your QlikView environment, but one thing it is not great at, ironically, is analysing your usage of licenses. In particular analysing your use allocation of document cals is a bit of a pain as you look at each user document individually. This document shows you how to get access to the license allocation data and present it in a way that is clear and concise.

Update: Add a new check on full users who have allocated document cals

Update: 2015/03/18 Author: Colin_Albert

Please find attached file for Lookup() example.

Regards,

Jagan.

Edit:

Attached Qlikview file is changed and shows use of Lookup in tables.

Regards,

Celambarasan

Beyond the basics of data modelling and the advanced topics of qlik datamodels there are more things which you might need if the amounts of data are increasing or if your datamodel is more complex. This meant if you have to combine multiple data-sources within more than one application and/or various applications are sharing a datamodel or it's even a cascade from (more or less nested) datamodels.

The view of things changed if you haven't only a few applications but an entire reporting environment with dozens or hundreds of applications and there are probably more then one people which work and maintain on it at the same time.

At first you should begin with a systematically approach. My suggestion is to start with a white paper and write down the requirements and how could be they solved, read books and postings like this, ask people which have done a similar work before and also consider to use external help for the difficult things - and be sure you have a plan how the end-result should look like. Nobody builds a tower without a plan from the architect.

But of course no plan is perfect and covered everything, you will need to adjust it several times and you will need by some aspects many attempts to gain your aims - but you shouldn't have a simple trial and error approach.

Therefore: Here a few links about the most importance things like: data-architecture (layer), folder-structure, naming standards, star-scheme vs. snowflake-scheme, various methods to match data (joins, mapping, concatenate, table-associations), security-topics and many more.

Best Practices for Data Modelling

Data Modeling issues and solutions

Best Practices in Data Modeling

Three Tier Architecture

QlikView Data Architectures

Beside those more theoretical deliberations exists an own group within the qlik community: QlikView Deployment Framework. to make such things easier and more practically.

Main topic here is the development of datamodels within qlikview but those are always imbedded in bigger enterprise structures therefore you should also think a little bit outside the box then you won't be successful with an isolated qlikview system.

qlik-view-project-methodology-handbook-v-10

Another important point is security and how you could manage Authentication and Authorization. The used authentication will be mostly defined on company level and could be often directly imbedded in qlikview with SSO. Authorizations instead are mostly applied on application level with section access (by use of the publisher loop and reduce is possible, too).

Beside those rather bigger things there are of course further topics on how qlikview worked under the hood which you should to know to improve the performance and the maintainability:

Symbol Tables and Bit-Stuffed Pointers

Symbol Tables and Bit-Stuffed Pointers - a deeper look behind the scenes

ByteSizes from Values in QlikView

The Importance Of Being Distinct

A Myth about the Number of Hops

Data Modelling: Clarity vs. Speed

Key field generation methods comparison

Another interesting point to the above links are synthetic keys - my personal recommendation is you should avoid synthetic keys but of course each coin has two sides and synthetic keys aren't a general problem. If you want to dive (really) deeper you should read this: Should We Stop Worrying and Love the Synthetic Key?  and this: Synthetic keys vs. AutoNumber() (- concatenation).

There is some content-overlapping within the above used links but I think the complement to each other is useful and of course you will find many more interesting postings here within the qlik community to these topic - the notes here are a good starting point to go further.

Have fun!

Marcus Sommer

Hi all,

Qlikview Server has a number of licenses (AssignedCals), there may be a problem when these licenses come to the limit.

To avoid to click 200 times (delete foreach user, apply and then restore and applies again....) I create this simple program.

Launch the program (you need to be in the same network of Qlikview Server), enter the name of the QlikView Server and the number of the port (you can see these info in the address of the "QlikView Management Console"), once you have entered this information, the program connects to the Management Console and allows with few clicks to "clean" all licenses .

Work with latest Qlikview Server version (12.x).

[UPDATE 02-2017]

The latest version do all automatically (cancel and restore), and finally the program closes.

So once you open the program, just wait.

Bye,

emakio

Last week I got a question to get number of working days between two dates

1. Excluding Public Holidays
2. Including Saturday as Half Working Day
3. Including Saturday as Full Working Day.

Have done some work and created enclosed QlikView File.

Suggestion and Correction would be highly appreciated !