Where was that article I read about the ColorMix function?

 

A great resource was put up by Steve Dark of Quick Intelligence fame.  AskQV aggregates curated QlikView blog articles into a single location. 

Why would you want this, you might ask?  I, myself, already include most of these in my RSS reader.  But this site is cool because you can search the blog rolls for specific topics like “Set Analysis” for example or “Incremental Load”.  Are the wheels turning yet?

The site also functions as a news feed and will give you the latest articles form the curated blog collection.

Finally, it will also give you contact information (Twitter, QV Community and LinkedIn) for each author.

So check it out.  I think it is a worthwhile tool to help consolidate my "technical notes".  I will never again have to ask “Now, where was that article I read about the colormix function?”

A Review - QlikView 11 for Developers

 

I am asked frequently if there is a book I would recommend to help a beginner better learn QlikView.  Up until the last year or so, that was a difficult question to answer because there definitely was a void in this area.  But recently I have been recommending a guide that fills this void quite nicely.

QlikView 11 for Developers is written by Miguel Garcia and Barry Harmsen.  Both are notable (dare I say famous) BI consultants that have been in the QV space for many years.  The book weighs in at 534 pages.

We follow the case of HighCloud Airlines throughout the text.  Users are also encouraged to download the example material so we can also develop the solution alongside the text. 

The chapters are arranged just as we would normally develop a Proof of Concept (POC) so the rhythm is natural and iterative in approach.  This allows Miguel and Barry to cover a broad range of topics ranging from data modelling down to the Class function and every topic in-between.  This does mean that the guide is not necessarily organized in a formal way like a reference manual or “encyclopedia”.  But by following the case of HighCloud Airlines in a natural progression, it makes the material more readable and realistic.  The back-end index will help anybody who wants to reference a specific topic.

I read it a little bit ago and put it away and found myself digging it back out on multiple occasions, so now I just keep it near my workstation in case I need to reference something.

Although QlikView is relatively easy to learn compared with other BI tools, it is still hard!  And because it is constantly evolving, ongoing training is also necessary.  A book like this was long overdue.

I think QlikView 11 for Developers is a must have for any serious developer.  Even an old pro will pick up a few tips and be able to refer to it as a reference (I know I have).  And for those of us just starting out in the QlikView world, this will be a handy way to jumpstart the process.  It can be picked up from Amazon .

Adventure Works Year Update

Adventure Works - Our favorite fictional company.

There are many times when we must rely on a sample database to develop our QlikView applications.  I use these DB's for POC's, training, for testing a particular technique, creating examples, blog posts and other situations.

My standard "Go To" has got to be the Adventure Works database that comes with MSSQL.

But the problem with ANY sample database is that the data tends to grow stale in regards to the date columns.  It seems that too quickly, data that felt so fresh in 2008 doesn't make much sense in 2014 or beyond.

So I developed a QlikView application that will take my favorite sample database and update all the date fields to the maximum year of my choosing.  This allows me to get a few more years out of my Adventure Works database without having to make excuses for the age of the data.

This particular application extracts all the tables from AdventureWorksDW2008R2, transforms the date columns in needed tables and then saves all the tables in the schema to the directory of your choice.

Instructions

1. Download the qvw here
2. On the first page of script, create a new connection to match your SQL instance.
3. Adjust the value for vMaxYear to the highest year you want to appear in the sales data.
4. Adjust the value of vQVDPath to the fully qualified path where you wish the transformed qvd's to be deposited.

Notes

• The script is specifically for AdventureWorksDW2008R2.  But feel free to adjust to the version you are using or another db.
• The DimData table is omitted since I usually create my own calendars in QlikView.  All other schema tables are extracted and stored to qvd.

Look! I can see my qvw from here.

Our friends over at Vizubi, famous for their great NPrinting product, have created a neat little plugin called QlikLook.

Have you ever said to yourself, “Where is that qvw that I did <blank> in?”   Which qvw had that trick for establishing a closed hierarchy?  Where was that expression with the crazy set analysis in it?  Which qvw has that great mini-chart example?  Which qvw out of the 200 scattered throughout my local drive is the one that I am looking for?

QlikLook allows you to preview any QlikView document (qvw) in an Outlook or Windows Explorer preview pane.

In addition, you can browse the sheets and make selections in the preview pane.  Basically you can use it as if you are using the document in a browser from the access point.

You can find the application here .  IT IS FREE.  You will be identified through LinkedIn, thus allowing you to download the executable and license.  The install is straight forward.

Now, if you enable your preview pane in Outlook (in the View menu) and then select an email that has a qvw attachment, the preview pane will fill with the sheets in that qvw.  For Windows Explorer, the preview pane is turned off by default.  To turn it on go to Organize à Layout à and enable Preview Pane. 

Although there is some lag depending on the size of the document, it seems to respond very well and has helped me scan through documents without opening QlikView (or yet another instance of it).

This is an incredibly convenient and useful tool.  I would encourage everyone to check it out.