QlikView Examples

A collection of code examples using QlikView BI developed by Cohn Consulting Group
Loading...

Friday, August 19, 2011

Accessing Random Excel Worksheets using ODBC in QlikView

How often have you been given the challenge to decipher an Excel spreadsheet without knowing the names of the worksheets? As you probably know, in QlikView reading an Excel worksheet with prior knowledge of the worksheet names is relatively simple. The challenge sometimes is to read all of the worksheets and then process the worksheets that meet specified criteria. In our example, we only want to process worksheets that contain expense information.

Our example Excel worksheet contains expenses for multiple months. Each worksheet represents a month. However, there are also Excel worksheets with information that is not relevant to QlikView. These worksheets need to be skipped. We will be able to identify which worksheets to read by looking for key information in certain columns. We are also going to show how to read all the Excel files in a directory. This will allow our users to simply copy their Excel files to a single directory.

Please note that this example is developed using Excel 2007. The ODBC Driver is specific for Excel 2007 and later versions of Excel. This logic will probably not work in versions of Excel prior to Excel 2007.

This example is developed using QlikView 10 but should work fine in previous versions.

Excel File Layout

Our Excel file layout is relatively simple. We require our users to enter their expenses in the format below. We specifically look for the work ‘Employee:’ in cell A1. We then look for the name in the next cell.

Worksheets that do not contain the word ‘Employee:’ in cell A1 are skipped.


Employee:

Michael Stoler



Date

Description

Category

 Amount

7/11/2011

Auto Travel to Boston

Auto

 $    76.50

7/11/2011

Hotel Boston

Hotel

 $  199.00

7/11/2011

Dinner Boston

Meals

 $    12.50

7/12/2011

Breakfast Boston

Meals

 $    10.00

7/12/2011

Lunch Boston

Meals

 $      5.50

7/12/2011

Auto Travel from Boston

Auto

 $    76.50

Total

 $  380.00

Read the Excel Files and Worksheets in a Directory
The first step is to read in the Excel worksheets in the directory specified in our variable vExpenseDirectory. This variable is set by the user in the Setup sheet.

The script below reads in all of the Excel files in the directory and then loads the worksheet names into the ‘Tables’ table.

Let vDir = '$(vExpenseDirectory)' & '*.xlsx';

for each vFile in filelist (vDir)

   // Read in the worksheet names of the Excel file.
   Connect to
   Provider=Microsoft.ACE.OLEDB.12.0;Data Source=
   $(vFile); Extended Properties="Excel 12.0
   Extended Properties="Excel 12.0
   Xml;ReadOnly=true;IMEX=1;HDR=YES;"];

   Tables:
   SQLtables;

   // Make sure to disconnect from the ODBC drive
   Disconnect;
   (………)
   drop table Tables;
next


Identify the Worksheets We are Interested In

The next step is to identify the worksheets we are interested in.  In our example, we are going to look for the text ‘Employee:’ in cell A1.
The script below iterates through the worksheets looking for the text ‘Employee’. One note is the worksheets are internally stored with a trailing ‘$’ character. We need to remove this character for QlikView.



If the text ‘EMPLOYEE’ is detected, we will process the worksheet.

For nIndex = 0 to noofrows('Tables') – 1
   Let vWorkSheet = peek ('TABLE_NAME', nIndex,'Tables');
   // Remove the $



   if right (vWorkSheet,1) = '$' Then
      Let vWorkSheet=mid(vWorkSheet,1,len(vWorkSheet)-1);
   end if



   Temp:
   LOAD A,
        B
        FROM
        [$(vFile)]
        (ooxml, no labels, table is [$(vWorkSheet)]);



   Let vEmployee = trim(upper(peek('A',0,'Temp')));
   Let vName = trim(peek('B',0,'Temp'));
   drop table Temp;



   if upper(left(vEmployee,8)) = 'EMPLOYEE' Then
      (………)
   End if
Next
Read in the Data

After we have identified the worksheet, the last step is to simply load the data into the Expenses table. Note that we take advantage of the QlikView feature that will automatically concatenate data with the same field names.

// Read in the Excel File
Expenses:
LOAD '$(vName)' as Name,
     Date(Date,'MM/DD/YYYY') as %Date,
     Description,
     Category,
     Amount
     FROM
     [$(vFile)]
     (ooxml, embedded labels, header is 2 lines, table is
     $(vWorkSheet)])
     where
     len(trim(Date)) > 0;

Display our results

While not the main focus of this article, an informative display is always the icing on the cake! Note the example of a pie chart displaying both values and percents.

 
Contacting Me

I always welcome your feedback. Please write to me at mstoler@jhcohn.com and visit our blog at http://www.qlikviewexamples.com.

Downloading the Example

Please click on the line below to download the example.

Download the file




Monday, July 19, 2010

QlikView Profit and Loss Statement

I would like to thank everyone for their feedback on the first Profit & Loss Statement demo; your comments are greatly appreciated. I received a request to generalize the Profit & Loss design for internal use. In response, I decided to modify the Profit & Loss QlikView file to accept an Excel file for input.


In addition, I want to demonstrate how to use a “Date Island” for date filtering. A “Date Island” is a date table that is not connected to the main QlikView data structure. Date filtering is accomplished using QlikView expressions with functions such as InMonth, InYearToDate, etc. The Date Island makes it easy to compare this year to last year, this quarter to last quarter, etc. without having to use Set Analysis. I only use Set Analysis as a solution to problems that cannot be resolved using standard QlikView logic.

In this document, I will only cover the differences between this version and the first version. The previous version is available at http://www.qlikviewexamples.com/. Search for the text ‘Developing a Profit and Loss Statement in QlikView’.

Designing an Excel File to Drive the Profit & Loss

Our first task is to design an Excel file to define the layout of the Profit & Loss Statement. This Excel file must include the following:

• Ability to associate ledger accounts to line items.
• Ability to format the Profit & Loss with blank lines.
• Ability to create subtotals and totals.

Let’s take a first pass at the layout of the Excel file.

Excel File Layout


By using an Excel file, changes are easily made without reprogramming the dashboard. This can be very useful for making layout changes if your General Ledger structure changes.


The Excel file is read during the QlikView load and is used to build the table layout. The layout is then linked using the IntervalMatch table to the PLData. The PLData simply contains the Account values from the General Ledger System.

The PLFormat table contains HTML format codes for the different line types (Line, Total, etc.).

The LineNum field is a hidden column in the chart used to define the order of the Profit & Loss Statement.

QlikView Table Layout


 
Profit & Loss Statement

Below is the Profit & Loss Statement in QlikView. I have included the Pyjama Green style to improve the aesthetics.

 


Downloading the Example

Please click on the line below to download the example.

Download the file


Monday, July 12, 2010

Using the Crosstable Feature in QlikView

Often, QlikView developers are confronted with the fact that loading data from a spreadsheet may not be as simple as it sounds. One such example is illustrated below. I have seen even experienced developers struggle to get data out of Excel crosstable spreadsheets.

Formatting these types of spreadsheets in QlikView and getting them ready for loading can be time-consuming, frustrating, and ,above all, may result in loading the wrong information. I have had this experience myself so I have put together some tips to share with others who are facing the same issue.

Below is a step-by-step example.

Suppose we have an Excel file with date (Week Ending), product type (Books, etc.), and sales amount for each product type in the following format:





































The last row is a total of sales amount. Instead of formatting the Excel file and loading it into QlikView, we will load it as is and do the formatting in QlikView.

What we first need to do is create a new QVW file, save it, and load the Excel crosstable.





















What we see in the above illustration is the data load preview in its original format. We won’t change anything at this stage, instead click on the ‘Next’ button. The next step is where we will have the option of either proceeding further or enabling the data transformation step. The data transformation step is useful when you need to transform badly formatted data, as the example we have here, into a traditional table.
















Click on the ‘Enable Transformation Step’ button. QlikView will give you a preview of the transformed data with further options to format and manipulate the data before it gets to the load statement stage. Here is how it looks:
















As you can see in the above illustration, by default we get to the Garbage tab first where we can remove unwanted columns or rows. In this example data, row six is a total and hence should not be loaded. It could simply be computed at the expression level once the sales figures are loaded. To get rid of this row, click on number 6. Row six will be highlighted and the grey button at the top left side will be activated, which will enable you to delete the marked/highlighted row.

Notice that the date values in column Week Ending are numbers. You should not be worried at this stage about formatting. QlikView handles date values like numbers and only displays them in date format at a higher level. Now, we have the date values in column Week Ending and the sales amount for each product type in columns PT1 to PT4.

Click on the ‘Next’ button . We are now at the point where we can transform the data structure using the Crosstable feature of QlikView.

















As you can see, there are multiple options we can use to read/manipulate the data we are loading. It is not always the best option to select ‘Embedded Labels’ from the Labels drop-down menu, but for this specific example we need to do that. If you select ‘None’, QlikView will give your columns the default column names, which may not be useful at a later stage and may result in having to rename the columns. In the result box, rename the first column ‘Week Ending’ to ‘Date’. Simply click inside of the column name, it will become highlighted, rename it, and press Enter. The column is now renamed.

We also don’t use the Clauses box for this example. To turn the crosstable into a straight table, click on the button labeled ‘Crosstable’. The help context describes Crosstable as a common type of table featuring a matrix of values between two orthogonal lists of header data.

The image below illustrates the extent of the crosstable feature QlikView provides and how the table appears prior to using the crosstable feature.



















As I described above, in the first box, QlikView displays your table structure as it is before changing it into a straight table and gives you the options you have to apply to the table in the second box to get a straight table. The qualifier field parameter tells QlikView the number of qualifier fields that precede the fields to be performed. In other words, if you put 2 in Qualifier Fields, QlikView will take PT1 also as a Qualifier Field, which would result in a totally wrong and meaningless data straight table. For this example, we would like to leave the first column as the Qualifier Field because it is the main driving factor for creating the straight table and the only column with different field values than the others. The other four columns are related and they represent product types and their respective sales amount for each date. It is vital that you know exactly which field to pick as a Qualifier Field. Also, each part of the table is colored to represent each of the parameters, which makes it easier for us to determine which parameter is affecting which part of the table.

We are interested in the parameters ‘Attribute Name’ and ‘Data Name’ as the Qualifier Field has the right value. The ‘Attribute Name‘ is the name of the new field we are about to create that will contain all the fields (attribute values) to be transformed. By default, QlikView picks the first column name as the new field name. We rename PT1 to ProductType and we move on to the ‘Data Name’ parameter. This parameter will contain the data of the attribute values (i.e., it will hold the sales amount values); therefore, we will rename ‘Data Name’ to ‘SalesAmount’.

We are getting closer to the final stage now. Click on the OK button after you have entered the value for data name.

 










The image above is your final straight table. You now have your date values and the product types with their respective sales amount for each date. This will be your final straight table. Now you can click on either the Finish button to go back to the load script or the Next button to change how the load statement will look or simply check the load statement structure to ensure it is the way you want it to be.

I hope this was helpful to you. As you can see, it actually is simple to get a crosstable. You can, of course, use the crosstable prefix in your load statement without using the Wizard but you should really know what you are doing.


Notes on Demo Files

Please click on the line below to download the example.

Download the file

Tuesday, June 22, 2010

Integrating Fiscal Year and Calendar Year Information in QlikView

Introduction

Many companies’ fiscal year is different from the calendar year. For example, a company may start its fiscal year in February instead of January. This presents a challenge in QlikView when it is required to convert a calendar year to a fiscal year and calendar month to a fiscal period. For the example cited above, February 2010 would correspond to the first month (or period) in 2011.

In this QlikView code example, we will illustrate a simple technique for converting data organized by calendar month to a fiscal period format, and vice versa. In addition, the example shows one technique for bringing in data organized by calendar year and month and integrating it with data organized by fiscal year and period.

In the example, two parameters will be read from an Excel worksheet that will “tell” the script:

• What the start month number is for a new fiscal year (i.e., for period 1).
• If the year will move ahead, using a “forward year” format (to be discussed below).

As an example in a company where the fiscal year starts on February 1, February 2010 is period 1 for fiscal year 2011.

The calendar years and months map to fiscal years and months for fiscal year 2011 as follows:

For company XYZ, the new fiscal year starts on October 1. So October 1, 2010, will be the start of fiscal year 2011, and October will be period 1 for fiscal year 2011.

The calendar years and months mapping to fiscal years and months for fiscal year 2011 is:


For a final example, another company’s new fiscal year starts on February 1 similar to the company in the first example, but they don’t increment the year to the future year. So February 1, 2010, was the start of fiscal year 2010, and February was period 1 for fiscal year 2010.

The calendar years and months map to fiscal years and months for fiscal year 2011 as follows:

These three scenarios can all be controlled in QlikView by the included QVW and the parameter file below, ‘Parameters.xls’. Simply put in the month number for the fiscal start month, and indicate if the year should be moved ahead when the start month is reached:

 
In this QlikView example, we will start with a data source that is already organized by fiscal period, such as in a company’s existing accounting system:


We want to append data to this data source from another data source that is organized by calendar year (for example, operations data):


In order to append the new data source to the old, the calendar year and month field (CalendarYearMonth) must be converted to fiscal year and period (FiscalYearPeriod) on each record.

After this is done, a date dimension table (FiscalDateFields) will be created that will be used for the front-end developer to choose from several kinds of date fields, both fiscal and calendar:



FiscalDateFields table



The end result is a system in which front-end developers can bring both fiscal date and/or calendar date objects into their dashboard in order to meet and exceed user requirements:



Wednesday, June 9, 2010

Getting Started With the New QlikView 10 Container Object

Introduction

As everyone is aware, QlikView 10 is now in the “beta testing” phase.

One of its new features is the Container Object. This object is used to collect and then display other QlikView objects (i.e., charts, tables, etc). The Container Object is more flexible than “Fast Change” because you can use different objects, dimensions, and expressions within it.

The Container holds the promise of saving valuable screen real-estate space. How often are you asked to squeeze one more chart on a sheet without displaying a scroll bar? The Container may be the solution to this and many more “real estate” challenges.

Getting Started

Getting started with the QlikView Container is easy. In our example, we are going to consolidate two charts below into a single Container.

The following are the QlikView charts.


 
 
 
 
 
 
 
 
 
 
 
 
 




Creating the Container

Creating the Container Object is easy. Simply select Layout, New Sheet Object and Container. QlikView will display a list of objects in the current QlikView document.

Select the objects you would like to add to the container and then select Add.


 
 
 
 
 
 
 
 
 
 
 
 
 




QlikView Container Object

Below is the QlikView Container Object in our QlikView document. Note we can easily switch between Gross Profit and Net Income.


 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Deleting the Original Objects

Most users will want to delete the original objects. The following are the steps to delete the objects.

1. Right-click on the object and select Remove.

At the prompt, select OK.


 
 
 
 
 
 
 

 
2. Select No at the prompt to remove all objects linked to the selected one. Do not select Yes as this will remove the object from the Container.
 
 
 

 
Notes on this article

This article was created with a beta version of QlikView 10. Some of the options may change in the final version.

Also, you won’t be able to access these examples without obtaining QlikView 10, either in beta or production.

Sunday, May 23, 2010

Developing a Profit and Loss Statement in QlikView

As part of my job, I am often asked to create Profit & Loss Statements in QlikView. Initially I had to develop special techniques to create aesthetically pleasing Profit & Loss Statements. My clients would often ask me to duplicate a Profit & Loss Statement from a reporting system. In addition, they wanted the ability to query the Profit & Loss Statement dynamically in QlikView.

I discovered that what you can do with QlikView is only limited by your imagination. Now, after developing Profit & Loss Statements in QlikView for the last few years, I am ready to share what I have learned. I hope to save fellow QlikView Developers time and to create a forum for future discussions.

The following is an approach which will give users the ability to create a Profit & Loss Statement in QlikView. In this example, we will read company data from a Microsoft Excel file and create a Profit & Loss Statement in QlikView. I am only using Excel to simplify the example. In a Profit & Loss Statement developed for a client, we typically use an Accounting System as the data source. In our example, we use states (NY, CA) to represent the company offices.

Your users will have a dynamic view of their Profit & Loss Statement. The ability to select combinations of time periods and states will enhance their ability to analyze data. The quick response of QlikView will save them time.

Never again will your users have to re-run a report to display a different time period or office.

Designing the Data Structure



The approach we use is to create the entire Profit & Loss statement in the PL table. We also include two pre-computed fields PLGrossProfit and PLNetIncome. These fields are included because we need to display them in the bar graphs. Set Analysis can also be used to display these values at runtime, but we usually like to create what we can in the load script.

The load script computes all the values and totals. As an example, the Gross Profit is computed in the load script as a line item. Computing all the values in the load script avoids time-consuming computations during the display.

PL Table

Periods Table

Displaying Data

The optimal QlikView object for the display of a Profit & Loss Statement is the Pivot or Straight table. Some of the challenges include displaying blank lines, totals and bold text. Our approach is to build the entire table layout in the load script and then display the table in a chart. This will insure all user selections are reflected in the Profit & Loss Statement.

There are also some options you need to set for the Pivot or Straight table. You need to make sure Show All Values is selected. This option enabled the display of the blank lines. Suppress When Value Is Null is also used to eliminate missing values.




Please click on the line below to download the example.

Download the file