Qlik Design Blog

5 Posts authored by: Adam Bellerby

I was asked recently if I could produce a standard control chart or SPC chart in QlikView. So I thought I would share this on the blog.

SPC and Control charts are ways of tracking if a process is "in control". If you take a look at the Wikipedia entry below you can see that Control charts have been in existence for nearly a 100 years, and are still used today, especially with the introduction of 6 sigma in 1995.

"The control chart was invented by Walter A. Shewhart while working for Bell Labs in the 1920s. The company's engineers had been seeking to improve the reliability of their telephony transmission systems. Because amplifiers and other equipment had to be buried underground, there was a business need to reduce the frequency of failures and repairs. By 1920, the engineers had already realized the importance of reducing variation in a manufacturing process. Moreover, they had realized that continual process-adjustment in reaction to non-conformance actually increased variation and degraded quality. Shewhart framed the problem in terms of Common- and special-causes of variation and, on May 16, 1924, wrote an internal memo introducing the control chart as a tool for distinguishing between the two."

(https://en.wikipedia.org/wiki/Control_chart)

In this QlikView example of a control chart I have tried to give you a number of reference lines and also used the Standard Deviation functon to drive our control limits. The chart itself is dynamic and will react to any selections made wihtin the application. This will let you monitor your process control by any dimension you add such as Country, Region, Plant and also specific products and time periods.

spc.png

You can find the application and document here.

Adam Bellerby

AGGR...

Posted by Adam Bellerby Mar 8, 2013

The AGGR Function.....

Capture.JPG

I thought I would start this blog post with a simple multiple choice question:

Is AGGR.....

a - Used in many QlikView applications to great effect
b - Mis-used in many QlikView applications
c - Used to return an answer without really knowing why you get the answer
d - Not used at all because we're "not quite sure what is does"
e - All of the above

The answer in my opinion is "e - All of the above".

When I came to write this post and indeed the technical brief, the hardest part of all was actually coming up with a really good, easy to understand description of AGGR, as it has to make sense to both "technical" and "not so technical" people. I finally settled on the one below...

When it is used, the AGGR function produces a virtual table, with one expression and grouped by one or more dimensions. The contents / result of this virtual table can then be used / aggregated by a further outer aggregation function(s).

With this definition in mind, I have produced a Technical Brief and application which can be found here and here. In this document I have tried to provide an overview of the function and provided some examples of where it can be used to great effect.

So, when should you use AGGR? The answer is: Whenever you want to perform an aggregation in two steps. In the technical brief we show you some scenarios like.....

  • Largest average order value for each Salesperson for each Country, where average order value for each Salesperson is the first calculation and the largest in each Country is the second.
  • The Salesperson responsible for the largest average order value in each Country
  • How many Salespeople have average order values of less than $100 in each Country

I would also be interested in any other creative and powerful uses of AGGR you may have.


Thanks.

ABY

Regularly in a QlikView application, you need to load from a directory or set of identical files which simply have different file names but contain the same type of data. For example, log files generated by systems contain the same type of data in a set structure, but the filenames often reflect the time and date or the system the log file was generated from.

In this blog, I will show you how to (really simply) load in to QlikView, a set of files that are identical in makeup but are not named the same.

So, imagine we have a directory full of log files on your server as below...filelist.JPG

 

I need to load all of these log files in to my application, but I do not want a single load statement in my script for each of the 100+ files. So, firstly we need to perform a standard load from our table (.log) files. When you have done this using the qlikview script editor, you will have a simple load statement...

LOAD Sequence#,
     Timestamp,
     Level,
     Hostname,
     Logger,
     Thread,
     Id,
     User,
     Exe_Type
FROM
[vm-qvs12__Application_QVS_2012-12-04T13.25.38Z.log]
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

So this statement would load all of the columns listed from the specific file in the FROM statement. However, we want to utilise one single LOAD statement for all of our log files. By simply changing the filename to contain an asterisk, provided all of the columns that you are placing in your load statement exist in all of your files, using an asterisk will cycle through all of the .log files that start with the text "vm-qvs12__Application_QVS_" in the directory, and load the data in to your application.

example:

FROM
[files\Log\QlikViewServer\vm-qvs12__Application_QVS_*.log]
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

Now we have all of the data in our application, we may want to know from which log file the data in the application came from. So, we can use some functions to give us some extra data.

We can use the Filebasename() function to give us a column in the data that contains the filename of the loaded log file.

LOAD filebasename() as SourceFilename,
     Sequence#,
     Timestamp,
     Level,
     Hostname,
     Logger,
     Thread,
     Id,
     User,
     Exe_Type
FROM
[files\Log\QlikViewServer\vm-qvs12__Application_QVS_*.log]
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

Now, that was a simple example of the wildcard load. What if I wanted to load data from multiple files and enhance the script with something like a preceding load? To do this I would have to utilise a simple function/variable and a small loop. In the example below, you can see that I am loading from multiple xlsx files, again using the filebasename function, but I am also creating a column called Total in my preceding load. The For Each and Next keywords tell QlikView to loop through the files in the location that conform to the wildcard in the Filelist function.

For each vFile in FileList('C:\Users\aby\Desktop\*wildcard.xlsx')

Load Col2+Col3 as Total,
     *;
Load *,
     Filebasename() as Source

from [$(vFile)]
(ooxml, embedded labels, table is Sheet1);

Next vFile

Hopefully I have managed to simplify some of the scripts you use today!

 

ABY

Adam Bellerby

The Concat Function

Posted by Adam Bellerby Oct 19, 2012

QlikView has many useful and sometimes underused functions that can make your life as a developer a lot easier. One of these is CONCAT(). This should not be confused with the CONCATENATE script keyword.

In its simplest form, CONCAT() is used to string together values/words/selections in to one string. However, it can be utilized in a number of ways to help you solve different problems.

All of the examples in this post are based on the data in the table below.....

concat data table.png

I have shown a few simple but useful examples below to hopefully help you in your everyday QlikView development!

 

Simple String Concatenation

As mentioned earlier, the function lets you string together a list of values. These values can be hard coded or driven my selections/data.

=CONCAT(MyColumn,',')

=CONCAT(DISTINCT MyColumn,',')

This simple concat statement would string together all of the possible values from the column MyColumn. You may wish to add the DISTINCT keyword. This would ensure that each value is only displayed once in the string.

ABC,DEF,GHI,JKL,MNO,PQR,STU,VWX

When using a simple concat, you have the option to add a sort weight to the function to order the string values by a column of your choice. In the example below, I have added the date column to sort the values…..

=CONCAT(MyColumn, ',', Date)

Result: JKL,VWX,GHI,ABC,STU,PQR,MNO,DEF

 

Concat() within an expression/set statement

There are occasions when you want to pass a selection of values to a set statement. To do this I would need to add some single quotes to the string so that the CONCAT() function returns e.g. 'JKL','VWX'. But you cannot have the single quotes as they are, since they then would be interpreted when the Concat is evaluated instead of when the set expression is evaluated. Instead I use the Chr() function:

=CONCAT(Chr(39)&MyColumn&Chr(39),',')

I can  then  pass this concat statement to the inside of an expression…

=Sum({<MyColumn={$(=CONCAT(Chr(39)&MyColumn&Chr(39),','))}>} Value)

In most instances, this technique would be used where data islands are present. It lets me pass values to an expression that will not affect any part of the data model as the data island table is not joined to it.

 

Concat() in the Script

Concat can also be utilised in the script to let you convert multiple rows in to one single column value, just as any other aggregation.

Remembering the source data we have earlier in the blog. The result of script side CONCAT can be seen below...

concatscript result.png

Using Rank() to influence the Concat()

When you start to utilise other function in conjunction with concat() you can start to achieve to clever results. In this example, I have used Rank() to grab the top 3 performers (based on Value) and string them together.

=CONCAT(IF(aggr(Rank(sum(Value)),MyColumn)<=3,MyColumn),',')

Result: ABC,MNO,STU

 

So as you can see, there are a number of uses for CONCAT(). Let's if you can find more!

 

For more information, please refer to the Technical Brief here.

 

ABY.

Adam Bellerby

Colour

Posted by Adam Bellerby Jul 12, 2012

Are you colourblind? Do you actually know if you’re colourblind?  On the 3 occasions I have presented on this topic I have had a two male attendees admit to not knowing….. (until now...) that they were colourblind to some degree.

It is estimated that approximately 8% of men and 0.5% of women are colourblind. This large difference in probability is due to the fact that the genes that produce photopigments are carried on the X chromosome; if some of these genes are missing or damaged, color blindness will be expressed in males with a higher probability than in females because males only have one X chromosome, whereas women have two!
(http://en.wikipedia.org/wiki/Color_blindness)

The most common way to determine level of colourblindness is to take an Ishihara test. The test consists of a number of plates that contain patterns of different coloured/shaded dots. These dots form to show numbers and shapes that you can…. or in the case of colour blind people…. can’t see.

Can you see a number in this plate…… I can’t…. I am actually colourblind, which is quite ironic considering I work with QlikView visualisations every day. A person with “normal” vision should see the number 6, colourblind people will see nothing.

Ishihara1.gif

 

You can take the tests online on a number of websites :

http://www.tedmontgomery.com/the_eye/colortst/colortst.html

http://colorvisiontesting.com/ishihara.htm

http://www.colour-blindness.com/colour-blindness-tests/ishihara-colour-test-plates/

One great example I found is the image below. I cannot see any difference between the two images due to me being Red-Green colourblind. People in the office who have perfect vision could see a massive difference in the colours.…

globes.jpg

(Source: http://facweb.cs.depaul.edu/sgrais/colorvisiondefi.htm)

I often find that colour and the use of colour is something that is decided upon without much thought, hence me writing this. We have to be very careful when we decide on the colours we use in our charts and dashboards. In your QlikView objects, really think carefully about colours and the impact your choices will have on your users.  Ensure you use colour blind friendly palettes, and avoid using colours that are “close” together in tone, next to each other in charts such as scatter and bar charts.

There are a number of resources on the web to help you choose friendly colour palettes..

http://jiminy.medialab.sciences-po.fr/tools/palettes/

http://colorbrewer2.org/

So, choose your colours wisely, try not to use colours that are “close” together. Utilise the web resources to help determine your palette. If, like me, you are colour blind then these resources are a massive help!

Remember the number…. 8% of males are colourblind…this is a big number. This 8% could be the key decision makes or indeed the developers.

Wherever possible, colour should purvey meaning, not just used because it looks nice!

 

See more about this topic in this techincal brief Colour

ABY

Filter Blog

By date:
By tag: