Friday, August 8, 2014

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

Friday, August 30, 2013

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.


Monday, August 26, 2013

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.

Monday, October 15, 2012

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).

Wednesday, September 28, 2011

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.

Wednesday, May 12, 2010

Gathering data from multiple databases

I am working on a Qlikview project for a client who is using an ERP system that maintains a separate database for each site that is using the system. I think there are some regulatory requirements behind this but the reason why is not terribly important, my job is to consolidate their data into a single Qlikview document. As I was thinking about this I was dumbstruck by the possibility of maintaining three versions of my code with just a simple change in the database name. The possibility to have data inconsistencies and errors are high and I'd rather not risk it.
So I started looking at the Qlikview script and how I can use data to drive what data source will be used. I found that you can substitute parts of your SQL select with a Qlikview variable so this is the basis for my solution.

Set vdb = 'BlahBlah';

Employee:

Load *;

Select * from $(vdb).dbo.employee;
If you work with databases very much then the idea that you can easily alter your SQL queries by using a variable should get you at least a little excited. So I hope this next part will send you over the edge.

I am going to start by loading a list of databases I want to load from.

DatabaseList:

Load * INLINE [

DBName

USA

MEX

GB

];

you can use whatever source you want for this, even a query from the system tables but for simplicity I am just going to use an inline list.

So next you might want to define the tables that you want. Notice that I include the owner, this example is using SQL Server I'm sure there are variations needed for other RDBMS.

TableList:

Load * Inline [

TableName, owner

customer, dbo

customerorder, dbo

];


So now I am going to loop through the lists and pull in the data I want.

let vdbCount = NoOfRows('DatabaseList');

for db = 0 to vdbCount - 1

Let vDBName = Peek('DBName',$(db),'DatabaseList');

Let vTableCount = NoOfRows('TableList');

for t = 0 to vTableCount - 1

Let vTable = peek('TableName',$(t),'TableList');

let vOwner = peek('owner',$(t),'TableList');

If db = 0 then

$(vTable):

Load *;

Select * from $(vDBName).$(vOwner).$(vTable);

Else

Concatenate ($(vTable))

Load *;

Select * from $(vDBName).$(vOwner).$(vTable);

end if

next t

Next db



You'll notice that there are some variations in the code to create the new tables and force the concatenation. I did this because invariably you will find that the databases will be out of synch and if the columns in the tables differ then Qlikview will not automatically concatenate them. If it does not concatenate the tables you will generate some HUGE synthetic keys and if you have any significant data volume you'll be crashing your system.

In conclusion I think this code is pretty simple and could save you a ton of time if you have to perform the same operations against data from multiple systems. I think there are a ton of applications for this technique. It is important to note that in my example I am using the same database connection but clearly you could use this technique to define your connection strings too so you can hit databases on multiple servers.