Qlik: Ignoring Selections for Entire Tables

 

There are a ton of ways to accomplish the many tasks you might use in set analysis. I have figured out some favorites that can be used in an application with just a simple copy and paste of some variables and then using them in your expressions.

 

Sometimes you will find that you want to ignore all the selections on an entire table so you could manually list each field in your expression like this…

 

 

I use a standard calendar  that has 30 fields and manually coding all of that would be error prone and tedious and it would obscure the things you are actually trying to accomplish in  your expression. To save time, you can use an expression over the system data model to  automate the building of the field selection overrides. That expression uses the concat function to string all the field names together, like this…

 

You can use dollar sign expansion in your set analysis expression to insert this into your expression

 

So that is wonderful! You can override the selections for the entire table but are other developers going to have ANY clue what you are doing? Probably not. So if you put the expression into a variable then you can override selections on the calendar table in any expressions with some simple variable references expressions.

Finally what if I don’t want to just override the calendar but I want to be able to override the selections on any table. Well I have 300 tables in my data model and that would be a ton of variables and frankly the data model could change and screw up all those variables. So because I’m a little lazy and very nerdy I learned that you can add parameters to variables to make them dynamic.

 

So here is the expression we will add to a new variable that will allow us to override selections on any table we choose.

 

You’ll notice the “$1” in the expression that part of the expression is a parameter, you can pass the name of a table to the expression and override those selections on any table. You’ll also notice I removed the equal sign from the beginning of the expression, more on this later.

 

Now when I call the variable I have to do it in a two step process that I am calling “double dollar sign expansion”

The reason for the double dollar sign expansion is because I have to insert the parameter into the string and then evaluate the expression in separate steps otherwise the expression doesn’t parse but WOW that is pretty cool!!!

 

The next thing I want to talk about is the opposite case: ignoring selections on all of your tables except 1. The problem with this case is you would have to call our magical variable for ALL of our tables which would be easy if you didn’t have very many tables. But I have another solution that finds it’s roots deep in the QlikView help pages for Alternate States. When writing expressions with alternate states you can make some fields obey the default state or any other alternate state by naming the state and field in the operand of the set modifiers. It looks like this…

 

So in this example I am setting my OrderDate Year to obey the selections of the default state OrderDate Year and the OrderNumber to the alternate state named CompareState. So stay with me for a minute, I am not talking about creating alternate states I am simply pointing out some set analysis expressions that I don’t think are widely used. So if you want to make your expression obey only one table then you can use the same concepts we talked about earlier but using this alternate state syntax.

 

This is the variable I’m calling saObeyTable

 This is the expression where I call variable

So we start by making the entire set analysis expression ignore all selections by adding the set identifier “1” and then calling our variable which strings together all the fields from the Orders table.

 

I hope these tricks will save you some time and make your apps better.

The Worst Expression Ever

I wrote this as a calculated dimension for a client today.

Yes you are reading that right...

• Five expressions with Aggr.
• Set analysis with an aggregation search
• Five Class sets.

So you might be wondering what I am doing and why I chose this approach.

This expression creates irregular buckets for grouping medical claims by the total amount paid. The buckets go like this
<$10,000
<$25,000
<$150,000 (with $25,000 buckets)
<$300,000 (with $50,000 buckets)
>$300,000

So the chart ended up looking like this.

Normally I would add an interval table which would have preset buckets but the problem I ran into with this is that I would have had to pre-Aggregate the claims since the qualifier is based on the total claim amount not each individual line.

So I am defining variations of the Class function and using set analysis to limit which claims will produce a value for each bucket. then using the RangeMinString to choose the bucket that is not null.

As I said before I know this is an evil expression so please post suggestions in the comments and we'll see if we can come up with something that will perform better and meet the requirement.

I know there would be worse ways to do this with If() but I thought this approach was unique and might help somebody in a similar situation.


Happy Qlikking

Qlikview Project Files Can Help You Manipulate Objects

I was working on a QlikView for a customer the other day and I was frustrated because I could not re-size the right column of a Current Selections box. I kept getting that annoying scroll bar that makes the object look kind of ugly.

Like This...

The solution to this problem is to re-size the columns, well the left column is already pretty small and if you are on QV 11 SR3 you will notice that you cannot grab the re-size handle for the right hand column. So I pulled my hair out for a few minutes and then had a revelation. If I create a project file for the application then I could manipulate the properties in the xml definition of the object.

• First create a folder in the same directory as your application with the same name as the application with "-prj" added to the end. Just like this.

• Next open your QlikView application and click save. Qlikview will automatically export the definition of your application to the -prj folder. Open the properties of the object you want to manipulate and find the object ID on the general tab. Then close QlikView. 
• Open the project folder and find the xml file for the object you want to change. In my case it is the CS01.xml for the Current Selections object.

• Open the xml file in a text editor and start searching for things you can mess with I wanted to manipulate the column widths so I found some properties that looked promising. I changed some of the numbers to see what would happen. The sequence of what you do here is very important. Make sure your QVW file is closed, make your change to the XML and save the XML. When you open the qvw Qlikview will read the definition of the objects from the xml and update the object definition to reflect what has changed in the xml. If you are not happy with the change then close the qvw and tweak the setting again, save the xml and reopen the qvw. 

• I reduced the third ColWidths property to 40 and poof no more scroll bar.

This Project folder feature is part of the change management integration but it exposes limitless properties. I have to explore how it would be done but I think this approach would give you great time savings if  you are faced with making mass updates to colors, styles and fonts. Try some experimentation and see what you can do.

QlikView "Execute" can save your butt!

I ran into an issue the other day. I wanted to pull some data from a site and the data was stored in multiple xml files within a zip file.

This presented two problems for me as a QlikView Developer. How do I get the zip file down to my computer? Then how do I unzip the files so QlikView can read them?

Qlikview's "Execute" command makes this all possible.
You do have to enable execution of external programs in your load script but otherwise it is just another line of code.


The zip file was found at http://somedomain/somezip.zip so in order to download it I had to use a powershell command luckily the windows command line will execute powershell commands.

Disclaimer: I DON'T KNOW ANYTHING about powershell so be advised, comments on this post asking how to do stuff in powershell will be met with crickets. I'm sure there is great stuff in there and I'll figure those things out when I need them. I did some googling to figure out how to download the file from the command line so this line of code is all I know about powershell.

execute cmd.exe /C powershell (New-Object System.Net.WebClient).DownloadFile('http://somedomain.com/somezip.zip','C:\Test\somezip.zip');

So that line of code downloads the zip file from the web site and stages it in c:\Test. The next challenge is unzipping it. You may be able to extract the contents of a zip file from windows explorer but there is way to do it from the command line without downloading an executable that you can call. I got mine from http://stahlworks.com/dev/?tool=zipunzip download at your own risk. Once I downloaded the file I placed it in C:\Windows\System32. From there I could call it from my Qlikview load script using the Execute command.

execute cmd.exe /C unzip -o C:\Test\somezip.zip -d C:\Test;

This was just one way I am using the Execute command but there are many ways you can use it. For example you may want to copy data files to another directory after you load them. There are almost unlimited things you can do from the command line so keep it in mind when you are trying to solve a tricky problem.

Managing QlikView Server Without a Publisher

I have many clients who are using QlikView Small Business Edition or Enterprise Edition without Publisher. I'd love for them to add Publisher because I HATE HATE HATE managing file permissions in windows. I love that publisher automatically distributes QlikView documents to the User Documents folder and I can easily change who will receive each file right from the Management Console AND I don't have to remote into the server to do it.

Ok, time to end this publisher love-fest and time to get down to the business of designing our security environment when you don't have publisher. I'm writing this post because I spent a good chunk of the other day fixing a problem we had that was caused by a goofed up security configuration(This is called learning from experience because I goofed it up). So after banging my head on the wall trying to figure it out I thought it would be a good idea to share with the interweb.

Here is my use case...

I work for a company who has 25 Named user CALS and 100 Document CALS(Probably not an important detail)
There are several departments using QlikView Dashboards
Each department includes sensitive data in their Dashboards so they must remain private to the department.
There is also a Corporate Dashboard used by the C Suite and the CEO often uses the department dashboards to explore information about some key clients.

Our data is built by first extracting data from the source databases into QVD files. Dimensions are conformed and key fact tables are also built in QVD format. The Conformed Dimensions and Facts are combined into data models based on user requirements and finally the data models are binary loaded into the user applications and presented on Access Point.

A three tier data flow model provides the framework needed to present clear and consistent data to all your applications.

So let's start planning. We want to set up a folder structure to use in our Small Business Edition server to manage user access to their documents, and provide organization for our ETL framework, remember we don't have publisher.  Obviously we can get very complicated with this but for the sake of explanation I'm going to make a single directory our starting point, I'll call it "QlikWarehouse".

Applications
The Applications folder contains our user facing QlikView documents I always add one folder for each application and I design my security around those folders. You should create a "QlikView Users" group in active directory and assign read/write access to the Applications folder. Then you will override inheritance for "QlikView Users" on the application specific directories and grant read/write access to a group that corresponds to the folder for that application. In the QEMC you will set the Applications folder as the "Root" folder, when you do this the QlikView Server will add several files in the Applications folder, if your users do not have read/write access to these files then they will see login failure errors and you'll get to bang your head on the wall to figure out the problem.

Load Scripts
Load scripts is added to the QlikView environment as a mapped meta folder. Mapping it into the QEMC will allow you to set the reload schedule for your data. It is essential that your users do not have access to this folder. If they do you will be exposing documents to the users that will not have any data in them and probably cause nothing but confusion for your users.

Data
Most of the files in the data directory will be QVD files but you must map it because the data model files will live in here but just like the load scripts your users should not have access to this directory.

Active Directory security settings needed to manage your QlikView SBE deployment.

Following this kind of configuration will allow you a couple of advantages.

Management
Got a new Application? Create an AD group to go along with it and make it a member of Qlikview Users.

Simplicity
Users only see the applications that they have access to, period.

Compatibility
This structure is compatible with publisher so if you do decide to expand your Qlikview footprint you'll be ready to go. It won't be a turnkey deployment but this structure is close enough to adapt to the Source Docs/User Docs division in publisher.

I'd like to encourage comments since this is just what I've been doing when I deploy a server, any suggestions, criticism or praise(especially praise) is welcome.


A quick note on the images in this post, I would like to give credit where it is due. Many of the individual icons have been clipped from various presentations I have received from Qliktech however the overall diagrams are my creation(even if not terribly creative).

What a big "If" you have

Have you ever been asked to automate a manual process? If you have then you have probably been ambushed like Little Red Riding Hood. Things start off normally enough Grandma is wearing her nightcap and her glasses or the process you are automating has rules and lists but everything is pretty normal just like Grandma. Then you notice that Grandma has very big eyes, and very big ears and a monster flow chart that defines all the exceptions to the lists and rules...

THE BETTER TO EAT YOU WITH!!!

This is my big bad wolf, I mean "if"

Ok I know it really isn't that bad but the person describing the process to me says

"well every time I do this it changes because we find new things that we want to reclassify"

So now I am faced with writing some complicated code and having to rewrite it every quarter.

YAY Job security!

I am a consultant, having regular projects is very welcome but, one of the things I like about consulting is that I get to leave when the project is done. So this blog is intended to show you my exit strategy for this project. The code is Qlikview load script but the procedure could be implemented in a variety of languages.

So first of all I spent a significant amount figuring out the process for categorizing this data the result of which is found in the flow chart shown above.

Next I Build up the contributing data, fields, tables and mapping lists and I do as much mapping as I can with lists, joins, lookups etc. Now I start to dissect the remaining flow chart and I am going to follow it building a spreadsheet with the folowing columns
Step - The name of the steps, every diamond on the flow chart will have a step.
ConditionStatement - This is a boolean expression which should be listed inside the diamond
TrueType - Tells the progam how to handle the argument as a Literal, Step, or Field each of these types uses different syntax to use within the expression.
TruePart - The value for a literal, another Step name or a field name (be sure to use square braces)
FalseType - Same as the TrueType but used in the false arguments of the expression
FalsePart - Same as TruePart but used as the False Argument.

Here is a really simple example of the flow chart data

Step	ConditionStatement	TrueType	TruePart	FalseType	FalsePart
SA01	[Color] = 'Orange'	Literal	Warm Color	Step	SA02
SA02	[Color] = 'Red'	Literal	Hot Color	Field	[Fruit]

This example would be coded something like this, in no particular language

   If [Color] = 'Orange' Then
      'Warm Color'
   Else if [Color] = 'Red' Then
      'Hot Color'
   Else
      [Fruit]
   End If

However, we are after something that works as part of a QlikView load statement like this...

   If([Color] = 'Orange','Warm Color',If([Color] = 'Red', 'Hot Color', [Fruit]))

Now that we have the table we need to process it in QlikView to achieve the desired expression.
We start by loading the table FlowChartData

   
//For the sake of having an example that will actually run I'm using Inline data
//Some of the syntax does not work as inline data so you need to build the spreadsheet
//although I did not test it I would expect some text file formats would work too.

Fruits:
Load * Inline [
Fruit, Color, Shape
Apple, Red, Round
Banana, Yellow, Long
Orange, Orange, Round
];






FlowChartData:
LOAD Step, 
     ConditionStatement,
     TrueType, 
     TruePart,
     FalseType, 
     FalsePart 
FROM
FlowChartIfData.xlsx
(ooxml, embedded labels, table is Sheet1);





//The procedure used to build the Big Bad If
Sub BuildIf(sTargetVariable, sStep, lCondition, lTruePart, lTrueType, lFalsePart, lFalseType)
   //Required Parameters
   //sTargetVariable - a variable where the "If" will be held
   //sStep - the name of the first step
   //Parameters beginning with "l" should not be passed to the
   //procedure, they are listed so they
   //will be initialized as NULLS and treated as local variables.


   Let lCondition = Lookup('ConditionStatement','Step','$(sStep)','FlowChartData');
   Let lTrueType = Lookup('TrueType','Step','$(sStep)','FlowChartData');
   Let lTruePart = Lookup('TruePart','Step','$(sStep)','FlowChartData');
   Let lFalseType = Lookup('FalseType','Step','$(sStep)','FlowChartData');
   Let lFalsePart = Lookup('FalsePart','Step','$(sStep)','FlowChartData');

   //begin building the expression
   set sTargetVariable = $(sTargetVariable) If($(lCondition),;

   //Build the true Part
   //In order to build the code correctly you have to handle
   //fields reference, literals and Step References differently
   SWITCH lTrueType
      Case 'Literal'
         set sTargetVariable = $(sTargetVariable) '$(lTruePart)';
      Case 'Field'
         set sTargetVariable = $(sTargetVariable) $(lTruePart);
      Case 'Step'
         Call BuildIf(sTargetVariable,'$(lTruePart)')
   END SWITCH

   // add the parameter that comes after the true part
   Set sTargetVariable = $(sTargetVariable),;

   //Build the false part
   SWITCH lFalseType
      Case 'Literal'
         set sTargetVariable = $(sTargetVariable) '$(lFalsePart)';
      Case 'Field'
         set sTargetVariable = $(sTargetVariable) $(lFalsePart);
      Case 'Step'
         Call BuildIf(sTargetVariable,'$(lFalsePart)')
   END SWITCH

   //Close the parenthesis
   Set sTargetVariable = $(sTargetVariable));
END SUB
   


//Variable must be initialized or the value from the previous run will
//persist and the new expression will be concatenated
Let vDynamicIf = '';


Call BuildIf(vDynamicIf, 'SA01')


//use the variable as an expression in a load statement
Salad:
Load [Fruit], $(vDynamicIf) as [Color Feel]
Resident Fruits;

Wild Thing I'll Map Your Pattern Strings

While working on a QlikView project I was presented with an troublesome problem where I was trying to map some codes to a set of descriptions but the mapping was based on a pattern string. So I have a list that looks something like this…

Department Pattern	Description
ORT*	Orthopedics
VAS*	Heart & Vascular
CATH*	Heart & Vascular
*FAM*	Family Services

This is a highly abbreviated list but I think you’ll get the idea. I went looking for a mapping function that would help me match the pattern strings and I found two mapping functions ApplyMap() and MapSubstring(). I know ApplyMap won’t help me because it does an exact match and MapSubstring does not really do the job either because it will match a partial string but not a pattern.

The function that I decided to explore was WildMatch(). WildMatch is an interesting function, it requires two arguments, the first is a string usually a field that you would like to examine and then a list of pattern strings to search.

Load
WildMatch([DeptCode], ‘ORT*’,’VAS*’,’CATH*’, ‘*FAM*’) as [Department Pattern List Number]
Resident
Departments;

The WildMatch function returns a number that matches the number of the argument where the match was found and zero when there is no match. I thought if I used this function with a carefully prepared mapping table then I could achieve the “WildMap” functionality.

Step 1. Load the map as a regular table and add a row number field to the table.

DeptPatterns:
Load
RowNo() as RowNumber,
DeptPattern,
DeptDesc;
Load * Inline [
DeptPattern, DeptDesc
ORT*, Orthopedics
VAS*, Heart & Vascular
CATH*, Heart & Vascular
*FAM*, Family Services
];

Step 2. Concatenate the patterns into a string that will serve as the arguments for the wildmatch function. Notice that I am using the sort weight parameter in the concat function to ensure the list items are in the proper order. I am creating a simple table assigning the field value to a variable and dropping the table

PatternStrings:
Load
Concat( chr(39) & DeptPattern & Chr(39),',',RowNumber) as PatternList
Resident
DeptPatterns;

Let vDeptPatternList = FieldValue('PatternList', 1);

Drop Table PatternStrings;

Step 3. Create the mapping table.

DeptMap:
Mapping
Load
RowNumber as Arg,
DeptDesc as Desc
Resident
DeptPatterns

Order By RowNumber;

Step 4. Use the result of the WildMatch function to determine what row in the mapping table has the desired description.

Load
ApplyMap('DeptMap',WildMatch([DeptCode], $(vDeptPatternList)),'Not Found') as [Department Group],

…
Resident
Departments;

In conclusion, I found this technique helpful to meet this requirement I hope you will find it useful too.