QV Developer II Course QV10 PRINT

(1)

(2)

Under international copyright laws, neither the documentation nor the software may be cop-ied, photocopcop-ied, reproduced, translated or reduced to any electronic medium or machine-readable form, in whole or in part, without the prior written permission of QlikTech Interna-tional AB, except in the manner described in the software agreement.

Qlik®Tech and Qlik®View are registered trademarks of QlikTech International AB. Microsoft, MS-DOS, Windows, Windows NT, Windows 2000, Windows Server 2003, Win-dows Server 2008, WinWin-dows XP, WinWin-dows Vista, SQL Server, Excel, Access, Visual Basic, Internet Explorer, Internet Information Server, Visual C++, Visual Studio and MS Query are trademarks of Microsoft Corporation.

IBM, AS/400 and PowerPC are trademarks of International Business Machines Corporation. Firefox is a trademark of the Mozilla Foundation.

Apple, iPhone, iPod Touch, Safari and MacOS is a trademark of Apple Corporation. BlackBerry is a trademark of Research In Motion.

(3)

CONTENT

1

INTRODUCTION

7

Installing the Course Materials 7

Program versions 7 Text formats 7

2

DEVELOPER TOOLS

9

Troubleshooting 9 The Debugger 9

3

MAPPING TABLES 19

Mapping Quarters to the Orders table 19

MonthYear 23

Cleaning up the table structure 26

4

LOADING BUDGET DATA 31

Reading Cross Tables 31

(4)

4

QlikView Developer II |CONTENT

7

SCRIPTING AND DATA MODELING CHALLENGES 63

Link Tables and Concatenated Tables 63

Calculating net change within a field 63

Functions InDate, Data & DateIslands 64

Aggr() 64

Class() 65

Dynamic Aggregation 65

IntervalMatch 65

8

DATA MODEL OPTIMIZATION 81

Performance Tuning 81

Best Practices for QlikView File Optimization 83

Creating Incremental Loads 85

9

DAILY AND TRANSACTION BALANCES 95

Overview 95

Example: Balances at Specific Dates 95

Example: Balances for Transactions (in/out) 98

10QLIKVIEW SECURITY 101

Access control 101

Access levels 101

Access control database 102

Inherited access restrictions 103

Hidden script 104

Adding Section Access 104

Access control for certain commands 109

Further access control 110

Unattended Command Line Reload Considerations 111 Access restrictions on selected field values 111 Field value limitation in Section Access 112

11ADVANCED DATABASE CONNECTIVITY 115

Custom Data 115

(5)

QlikView Developer II |CONTENT

12BUSINESS CASE WORKSHOP 119

Scenario 119

(6)

6

(7)

QlikView Developer II |1 INTRODUCTION

1

INTRODUCTION

Many students will come to this course having already taken QlikView classroom training. This chapter covers formatting and coventions used in the course manual.

1.1 Installing the Course Materials

The course materials will self-extract from the file into the default directory

C:\QlikViewTraining\DeveloperII\

Make a Windows shortcut to this folder and place it on your desktop. Also make a Windows shortcut to the documentation folder and place it on your desktop.

C:\Program Files\QlikView\Documentation or

C:\Prrogram Files (x86)\QlikView\Documentation

1.2 Program versions

This course was created using the English version of QlikView 10.00 run-ning on Windows7. If other operating systems or languages are used, minor differences may be noted in the visual appearance of windows and dialog boxes.

1.3 Text formats

Exercises and actions to be completed by you, the student, will be set-off with a logo, as you see, below:

Exercise/Do:

This is a sample of instructions you would see to complete an exercise con-taining a sequence of steps.

(8)

8

QlikView Developer II |1 INTRODUCTION

Tips and Notes are outlined in a highlighted box, as you see below: This sample sentence is used to illustrate important points in the text, tips and notes to consider as you complete the course materials

(9)

QlikView Developer II |2 DEVELOPERTOOLS

2

DEVELOPERTOOLS

2.1 Troubleshooting

Debugging is an art. There are best practices and guidelines that can help in the debugging process. The key point to take away from debugging is to change one item at a time and then examine the impact of that change. If more than one item is changed without an examination, debugging becomes difficult. This portion of the class will not teach you how to debug an docu-ment but will point you in the direction of where to look if your docudocu-ment does not operate or look like you expected it to. These basic tools and pro-cesses are the best place to start when debugging a QlikView document. Also, during script execution in debug mode, the debugger allows for moni-toring of variables. This is useful for validation of variable state at different stages of script execution.

Remember, always make sure the Generate Logfile checkbox is checked under Settings|Document Properties|General. Also remember, missing or

misplaced commas and semicolons are the source of many error messages. Check for these common problems first.

2.2 The Debugger

Where is that Breakpoint, Check the Variables

Breakpoints stop script processing to give you a chance to troubleshoot. They can be placed in your script with the Debug option in the QlikView Script Editor. Breakpoints only stop script processing if placed on the line of the first statement in a code block that is terminated by a semi-colon. Setting a breakpoint on any other statement in the code block has no effect on

Objectives

• Understand the importance of troubleshooting • Use the debugger

(10)

10

You will notice in the Figure above that three breakpoints are set at lines 38, 39 and 44. Note that the debugger will not stop at line 39 since it is not the first statement in the code block.

This could be a LOAD statement, a SQL LOAD with a preceding LOAD, or a grouped LOAD statement. If you trace each statement back from the semi-colon you will see that while the key word MAPPING on line 39 is part of a code block ending in a semi-colon, MAPPING is not the first line of the code block – Shippers: is.

2.2.1 Variables

If you have created variables in the load script, you can view the value of the variable as the script is loading. You might be surprised that it does not hold the expected value(s). Create additional variables to investigate more states during script loading.

(11)

QlikView Developer II |EXERCISE

Exercise: Debugging

Do:

1 Navigate to the C:\QlikViewTraining\DeveloperII\Chapter02 folder. 2 Load the file Debugging.qvw into Qlikview and try loading data into

QlikView after setting multiple breakpoints, step through the code and see where the debugger stops. Also, check the values of the variables 3 Open the file \Debugging\Variable.qvw. Enter the debugger from the

script editor and check the breakpoints that have been set in the script 4 Use the Run button to execute the script up until the following

break-point.

5 Note that the variable mySetVar is not listed in the bottom right variable monitor pane.

6 Hit Run again and note that the value of the mySetVar variable has been set.

7 Hit Run once more and exit the script.

8 Repeat steps 3 to 6 and note on step 4 that the variable mySetVar still retains the value from the previous execution.

9 Go to Settings | Variable Overview and note that the script variable

mySetVar (once created) is stored and accessible from within

QlikViewDesktop.

Remember, intermittent values of script variables can be monitored through the debugger. The scope of script variables is not limited to a single script execution, which is to say the variables persist between executions.

(12)

12

2.2.2 Using the Table Box Sheet Object

The Table Box is the developer’s best friend. It is great for identifying how data relates and where data might be missing.

The table object is great for viewing row-level data relationships between different fields. For ease of use, a table box may have dropdown select enabled from the Presentation tab enabling the developer to quickly navi-gate each field / column and make text query selections.

Creating a Table Box is easy and one can add columns from multiple tables as well as add dropdown selects to the columns (fields) in the table. Note the Dropdown Select checkbox in the Figure below; the dropdown select allows you to select and search for data in the Table Box. This will help you under-stand how the data relates across your tables.

You can also see where NULL values happen across tables and within fields. NULL values can adversely affect a document and the selection of data.

(13)

Exercise

Do:

1 Navigate to the C:\QlikViewTraining\DeveloperII\Chapter02 folder. 2 Open the document KeyFields.qvw and go to the Main tab where you

will see a table. Select a cell that holds data and note that any row with an identical field value will be displayed with the corresponding columns of data with which it is associated.

3 Next, clear the selections (the Clear button) and select a cell that has a null (the default representation is a “–“ character). Note that nothing happens.

Note: You are seeing disjointed information, which can be problematic.

You can explore the causes of this condition by examining key fields.

Remember, Table boxes are great for understanding and validating data

relationships. Only included / possible values are displayed in a table box.

(14)

14

2.2.3 Making Copies of Key Fields

How might this disjointed condition have been created? • A LEFT JOIN

• A MAPPING table operation • Bad data

• Key field mismatches

Key Fields connect tables in QlikView, but examining them will not always display the information you might expect. Showing a Key Field in a Sheet Object such as a Table Box, displays all possible values from the Key Field in any and all connected tables – any table(s) could contain distinct values that do not match any values in any other table(s).

To get an understanding of what is happening in every table on every side of a Key Field, load a copy of the Key Field in every table and uniquely rename each Key Field copy with an AS clause.

Reload your document, create List Boxes for all fields, then do a SELECT

ALL on one of the fields that is an alias of the Key Field.

Finally, SELECT EXCLUDED on the Key Field by right clicking the Key Field List Box. You can see, through the selection and exclusion, which items are joined through the Key Field and which are not.

This problem usually happens when there is missing data. You can also return erroneous results if you are looking at different discreet levels of information in the model. This condition is covered later in the course.

(15)

Exercise

Do:

1 Navigate to the C:\QlikViewTraining\DeveloperII\Chapter02 folder. 2 Open the document KeyFields.qvw

3 Comment the script on the Standard Join tab and uncomment the script on the Copy of Keys tab.

4 Save and reload.

5 Note that the key field in each table has been renamed uniquely, i.e., Key

AS Key1_Table1.

6 Create List Boxes for all fields.

7 SELECT ALL on one of the aliased key fields.

8 SELECT EXCLUDED on the Key field (right click in the Key field List Box and choose SELECT EXCLUDED).

9 Observe the matches and mismatches between the keys in each table.

Debugging: Examples and Steps

When building and debugging complex equations, take two approaches: 1 Take a sample of the data and do not create a data model that is more

that two tables, if possible. There should not be more than three col-umns in each table or more that fifteen rows. Use the inline wizard or Excel to create the tables.

2 When building complex expressions, use a Straight Table chart and build expressions a single column at a time.

Do:

1 Navigate to the C:\QlikViewTraining\DeveloperII\Chapter02 folder. 2 Open the ComplexEquations.QVW.

Note: By the use of the 'Select all', 'Select possible' and 'Select excluded'

selection modes on key copy fields, it is possible to investigate joined and disjointed data within your data structure.

(16)

16 QlikView Developer II |EXERCISE

Expression Label

Qty/Lines AvgQty

Sales/Qty UnitPrice

Here you see that you can type an expression directly or you can use an expression label as an alias to the expression to get the same results.

5 Build your complex expression one column at a time and then move the expression to the placeholder of the Label that is referenced in the expression. This is the best approach for slowly building complex expressions.

Dates

In many cases, date fields are key fields. To join tables from different sys-tems, there are cases where dates need to be converted to a common format before dates from disparate datasources will match properly. It is always a good idea to check the key field using a Table Box to verify that the date for-mat is consistent throughout the entire key field.

Do:

1 Navigate to the C:\QlikViewTraining\DeveloperII\Chapter02 folder. 2 Open a working copy of the Dates.qvw file and load the data from the

Dates.XLS file in the Datasources folder. Create two tables in the script.

Your script should look like this:

Note: You can also reference an expression by its Table Column number

(zero-based index) but DON’T DO IT!

Note: You cannot nest aggregations (aggregate an aggregation) except by

(17)

3 Next, create a Table Box including all columns, sort by Date ascending, and scan the data. You should see something like the Table Box below. Note that the dates look the same but similar dates do not link.

4 To determine what is causing the problem, create a Text Object that dis-plays the expression

(18)

5 Next fix Date2 with the floor() function (floor(Date2)) changing Date2 from a timestamp to a date. (Note the floor() function truncates a deci-mal number and returns the next lower integer value, i.e., 2.7 becomes 2). Now reload the data. The result should look like this where you have rows that now link.

6 As an alternative, try using the daystart() function on the dates in both tables. Verify the results are the same. Hide the fact that both dates are now timestamps starting at midnight.

Note: Be wary when comparing dates. Ensure that the granularity is the same and stored in the same manner in each date field. (e.g. Day level dates stored without a time component, month level dates stored as the first day of each month, etc.

(19)

QlikView Developer II |3 MAPPINGTABLES

3

MAPPINGTABLES

Sometimes you need to add an extra field to a table to use a combination of fields from different tables, or you want to add a field to clean up the data structure. QlikView has an effective way to add single fields to a table called mapping tables. In this chapter, we will take a look at how mapping tables work.

3.1 Mapping Quarters to the Orders table

The Quarters table is useful, in that it links our Month data in the Orders table with the correct Quarter. However, the Month field is now a key field, and this will probably cause problems later. The following illustrations give us a visual of this dilemma:

Objectives

• Understand mapping tables

• Use mapping tables to add Quarters to the Orders table • Clean up the table structure

(20)

20

By changing our Quarters table into a MAPPING table, we will be able to integrate the Quarters field into the same table as Month (the Orders table). The MAPPING prefix is used on a LOAD or SELECT statement to create a mapping table. Tables read via MAPPING LOAD or MAPPING SELECT are treated differently from other tables. They will be stored in a separate area of memory and used only as mapping tables during script execution. After script execution they will be automatically dropped.

A mapping table must have two fields, the first one containing comparison values and the second the desired mapping values. The two fields must be named, but the names have no relevance in themselves. The field names have no connection to field names in regular input tables. When mapping tables are used to map a certain field value or expression, that value will be com-pared to the values in the first field of the mapping table. If found, the origi-nal value will be replaced by the corresponding value in the second field of the mapping table. If not found, no replacement is made.

The syntax is:

mapping ( load statement | select statement )

(21)

Exercise

Do:

1 Launch QlikView and save a working copy of the MappingTables.qvw QlikView file in the working directory for this chapter

(C:\QlikViewTraining\DeveloperII\Chapter03). 2 Open the Edit Script dialog.

3 Now, let us change the Quarters table load into a mapping load. 4 On the Main tab uncomment the Quarters table load statement you

want to use. Make sure that the other table is still commented so that you do not read from two Quarters tables.

5 Add _Map to the table name.

6 On the next line, type MAPPING in front of the LOAD statement. 7 When complete, verify that this section of your script resembles the

fol-lowing:

Quarters_Map: MAPPING LOAD

rowno() as Month,

'Q' & Ceil(rowno()/3) as Quarter Autogenerate(12);

Do not save and close just yet. If you reload the data now, you will lose the Quarters table and field, since mapping tables are temporary. How-ever, we can use the Quarters_Map table in our script (as long as we use it after it is defined in the script). To do this, we will use the applymap function.

The syntax is:

applymap( 'mapname', expr, [ , defaultexpr ] )

The applymap function maps any expression on a previously loaded mapping table. Mapname is the name of a mapping table previously loaded by a MAPPING LOAD or MAPPING SELECT statement. The name must be quoted with single quotes. Expr is the expression whose result will be mapped.Defaultexpr is an optional expression, which will be used as the default mapping value if the mapping table does not

(22)

con-22 QlikView Developer II |EXERCISE

9 Save and Reload the document.

10 Open the Table Viewer to verify the Quarters table is gone and that there is now a field called Quarter in the Orders table.

(23)

3.2 MonthYear

We will complete our time dimension fields by creating a new field that makes every month unique. There are, of course, several ways to accomplish this. In this course, we will create the field MonthYear by using QlikView date functions based on the OrderDate field, along with a date formatting function to provide the correct display format for our new month field.

(24)

Exercise

Do:

1 Return to the MappingTables.qvw file you have been working on in this chapter.

2 Open the Edit Script dialog from the menu or toolbar. 3 Locate the Orders table LOAD statement.

4 Immediately following the applymap… as Quarter field line, create a new field named MonthYear in the LOAD statement for the table Orders, as follows:

date(monthstart(OrderDate),'MMM-YYYY') AS MonthYear,

The monthstart function returns the first day of the month of the

Order-Date value. The date function then formats this value into a 3-character

month name, followed by a 4-digit year. Since QlikView stores this field as both a text string (the format we just specified) and as a numeric, it can be sorted numerically, as you would expect.

The complete Orders table LOAD statement should now look as follows. Be sure your script syntax matches this. Note, your Quarter and

Month-Year fields line will likely fit on a single line instead of wrapping as seen

below. //*************** Orders table *************** Orders: LOAD CustomerID, EmployeeID, EmployeeID AS EmployeeSalesID, Freight, OrderDate, year(OrderDate) AS Year, month(OrderDate) AS Month, day(OrderDate) AS Day, applymap('Quarters_Map', num(month(OrderDate)), null()) AS Quarter, date(monthstart(OrderDate), 'MMM-YYYY') AS MonthYear, OrderID, OrderID AS OrderIDCounter, ShipperID; SQL SELECT * FROM Orders;

(25)

5 Save and Reload the script.

(26)

26 QlikView Developer II |3 MAPPINGTABLES

3.3 Cleaning up the table structure

You will most likely want to minimize the number of tables in QlikView. (Omit needless tables.) It takes computational resources to make calcula-tions between tables. If you have tables with only two fields, map those tables to another table and so minimize the number of tables. Let us look at the Table Viewer and see if there are any tables than can easily be mapped to another table.

(27)

Exercise

Do:

1 Return to the file you have been working on in this chapter. 2 Open up the Table Viewer.

As we can see in the Table Viewer, some tables have only two fields. These tables could be mapped to the connecting tables. Let us start by mapping the Shippers table to the Orders table.

3 Open up the Script Editor and scroll down.

4 Change the table referring to the script for the Shippers table as follows.

Shippers_Map: MAPPING LOAD

ShipperID,

CompanyName AS Shipper; Figure 4. The Table Viewer

(28)

6 Verify that your Orders table script should resemble the following;

//*************** Orders table *************** Orders: LOAD CustomerID, EmployeeID, EmployeeID AS EmployeeSalesID, Freight, OrderDate, year(OrderDate) AS Year, month(OrderDate) AS Month, day(OrderDate) AS Day, applymap('Quarters_Map', num(month(OrderDate)), null()) AS Quarter, date(monthstart(OrderDate), 'MMM-YYYY') AS MonthYear, OrderID, OrderID AS OrderIDCounter, ShipperID, applymap('Shippers_Map', ShipperID, 'MISSING') AS Shipper; SQL SELECT * FROM Orders;

7 Save the document and Reload the script.

8 Take a look in the Table Viewer to see that in fact the Shipper field is now a part of the Orders table.

(29)

Extra Credit Exercises

Do:

1 Launch QlikView and save a working copy of the

AdditionalMap-pingExercises.qvwQlikView file in the working directory for this chapter

3 Create a tab after the Main tab and name it Mapping Loads. 4 Move the Shippers_Map and Quarters_Map script to the Mapping

Loads tab.

5 Map the Divisions table to the Customers table. Make sure to remove (or comment out) the Divisions table from the Dimensions tab and cre-ate a mapping table on the Mapping Loads tab.

Although script examples are at the bottom of this page, they are for ref-erence only should you need help. We encourage you to try to add the

Mapping Load and ApplyMap script on your own.

6 Are there any other tables that can be mapped to another table? Check the Table Viewer. Make sure to look for tables with only two fields. Dis-cuss with the course Instructor.

//*************** Divisions *************** Divisions_Map: MAPPING LOAD DivisionID, DivisionName; SQL SELECT * FROM Divisions; //*************** Customers *************** Customers: LOAD Address, City, CompanyName, ContactName, Country,

(30)

StateProvince; SQL SELECT *

(31)

QlikView Developer II |4 LOADINGBUDGETDATA

4

LOADINGBUDGET DATA

In our example data, there is a Budget table for Employees and Offices. We are going to load this into our QlikView document. The Budget table is built as a cross table and we need to convert this when we read it into QlikView. We will also add a field to the Budget table that allows us to change the val-ues of the budget to help in planning.

4.1 Reading Cross Tables

First, we will open the Budget table that is contained in the Excel file. This table does need some rework to read it into QlikView. Fortunately, QlikView has excellent functionality to interpret and change tables so that we do not need to alter the original look of the Excel file. We will use the crosstable wizard to load the data initially, and then make adjustments to our script. We will also use an input field to load the data initially, and then make adjustments to our script.

Objectives

• Understand cross tables

(32)

(33)

Loading Budget Data Exercise

Do:

LoadingBudget-Data.qvw QlikView file in the working directory for this chapter

3 Create a new tab following the Sales Person tab and call it Budget. 4 Click on the Table Files button and open the Budget.xls file from the

data folder for this chapter.

5 In the File Wizard, start by setting the Header Size to one line. 6 Next, we need to make sure that there are no empty rows in the Office

field. Click on the Next button and then click on Enable

Transforma-tion Step to transform the table and then click the Fill tab.

7 Click the Fill… button and then Cell Condition. We want the cell to fill if it is empty. Click OK, OK and Next to return to the File Wizard. 8 Click on Crosstable… to change the table from a cross table to a

nor-mal table.

9 Click on the Qualifier Fields and type 2 to expand them to include both the Office and EmployeeID fields in purple.

A qualifying field in a cross table, is a field that should not be altered during the Cross table load.

2006 is not a qualifying field. This is the first of the fields we want to

transform so that the years are placed in one field and the budget values are placed in another field.

10 Name the Attribute BudgetYear. 11 Name the data BudgetAmount. 12 Click OK.

(34)

34

13 Click Finish. You should have the following table in the script.

CROSSTABLE(BudgetYear, BudgetAmount, 2) LOAD Office, EmployeeID, [2006], [2007], [2008], [2009], [2010]

FROM Datasources\Budget.xls (biff, header is line, embedded labels, table is [Sheet1$],

filters(Replace(1, top, StrCnd(null)))) ;

14 Name this table BudgetTemp. 15 Save and Reload the document.

Open the Table Viewer. As you can see, there is a synthetic key between the BudgetTemp and the Employees table. We want to remove this syn-thetic key. We will adjust our script to get the data we need from the

BudgetTemp table, and then we will drop it.

(35)

17 For simplicity’s sake, change the BudgetTemp table to the following script by replacing the individual field names with the asterisk. This will allow your script to run properly even when the labels in the data change. BudgetTemp: CrossTable(BudgetYear, BudgetAmount, 2) LOAD * FROM

[Datasources\Budget.xls] (biff, embedded labels, header is 1 lines, table is Sheet1$,

filters(Replace(1, top, StrCnd(null)))) ;

18 To fix the first part of the synthetic key issue, go to the File Data tab and add the following line to the top of the Employees table.

Office&'-'&EmpID AS BudgetKey,

19 Save and Reload the script.

Do:

1 Now we will address the synthetic key field in the Budget statement and add the input field to set the budget prognosis. If it is not already open, launch the QlikView document you have been working on in this chap-ter.

2 Go to the Script Editor and place the cursor right after the SET state-ments on the Main tab.

3 Enter the following statement.

INPUTFIELD BudgetPrognosis;

The INPUTFIELD statement tells QlikView that the field will be an INPUT field. You have to state this in the script before you actually read the field in a table.

(36)

36

4 Go to the Budget tab and create a Budget table, as follows:

Budget: LOAD

Office &'-'& EmployeeID AS BudgetKey, BudgetYear,

BudgetAmount AS BudgetPrognosis, BudgetAmount

RESIDENT BudgetTemp ;

DROP TABLE BudgetTemp;

Now, we can use the INPUT field BudgetPrognosis to set different bud-get values if we need to alter the budbud-get to correspond to the actual val-ues.

6 Create a new Table Box titled Sales Budget consisting of the following fields: SalesPerson, BudgetYear, BudgetAmount, and BudgetPrognosis. 7 Move your mouse cursor over the BudgetPrognosis column in the table

box. An entry arrow icon will appear.

8 Click on the Input icon on any row and enter any number.

9 Right-click on the BudgetPrognosis column. Notice the related options of Restore Single Value, Restore Possible Values, and Restore All

(37)

QlikView Developer II |

Now, we can use the INPUT field BudgetPrognosis to set different bud-get values if we need to alter the budbud-get to correspond to the actual val-ues.

(38)

(39)

QlikView Developer II |5 ADVANCEDSCRIPTING

5

ADVANCEDSCRIPTING

There are several key measures to create in this chapter. We need to calculate these in the script. They are OrderLineAmount, CostOfGoodsSold and

Margin. To make these calculation fields, we need to do some advanced

scripting. There is also a key field, CategoryType, which we need to create.

5.1 Condition on a field in a table

The CategoryType field can be created by using the CategoryID field. If the

CategoryID is 5 or 6, the CategoryType should be Footwear, otherwise the

type should be clothing. Let us create this field in the script.

5.2 Aggregating Data

One of the key measures for this chapter is the OrderSalesAmount. We need to calculate this in the script. At the moment we already have the

LineSale-sAmount, but we want to have a total amount for each Order. To

accom-plish this, we need to aggregate the LineSalesAmount.

To group or aggregate data, we will use the GROUP BY clause in the LOAD statement. In this case, we need to aggregate the data in the OrderDetails table by OrderID.

5.3 Joining tables

We want to add the OrderSalesAmount field to the Orders table. To do so we can add the values of this table to the Orders table. To use two tables together like this, we must begin by combining them into a single table. Here, the JOIN between tables can be performed against the source

data-Objectives

Learn and use

• Conditions in tables • Aggregation • Joining tables

(40)

40

5.4 Concatenation

Another way to join data together from multiple tables is to use concatena-tion. There are two ways to concatenate data. We will explore each of these methods.

5.4.1 Automatic Concatenation

If the field names and the number of fields of two or more loaded tables are exactly the same, QlikView will automatically concatenate the results of the different LOAD or SELECT statements into one table.

Example:

LOAD a, b, c FROM Table1.csv; LOAD a, c, b FROM Table2.csv;

The resulting logical table has the fields a, b and c. The number of records is the sum of the numbers of records in table 1 and table 2. Rules:

- The number and names of the fields must be exactly the same. - The order of the fields listed in each statement is arbitrary. - The order of the two statements is arbitrary.

5.4.2 Forced Concatenation

If two or more tables do not have exactly the same set of fields, it is still pos-sible to force QlikView to concatenate the two tables. This is done with the

Concatenate prefix in the script, which concatenates a table with another named table or with the last previously created logical table.

Example:

LOAD a, b, c FROM Table1.csv;

Concatenate LOAD a, c FROM Table2.csv;

The resulting logical table has the fields a, b and c. The number of records in the resulting table is the sum of the numbers of records in table 1 and table 2.

The value of field b in the records coming from table 2 is NULL. Rules:

- The names of the fields must be exactly the same.

- The order of the fields listed in each statement is arbitrary.

- Unless a table name of a previously loaded table is specified in the con-catenate statement the concon-catenate prefix uses the last previously created logical table. The order of the two statements is thus not arbitrary.

(41)

5.4.3 Prevent Concatenation

If two tables have the same set of fields and thus would normally be auto-matically concatenated, you can prevent the concatenation with the

NoCon-catenate prefix. This statement prevents concatenation with any existing logical table with the same set of fields.

The syntax is:

NoConcatenate ( LoadStatement | SelectStatement )

Example:

LOAD a, b FROM Table1.csv;

Noconcatenate LOAD a, b FROM Table2.csv;

In our data, we have been provided with an additional set of new employees that are not yet contained in the EmpOff.xls file. In order to add this data, we need to modify our load script.

5.5 Preceding Load on Preceding Load

The next key measure we are going to add is the CostOfGoodsSold. To cal-culate this value, we need to add the UnitCost field from the Products table to the OrderDetails table. We are going to do this by using a mapping table and apply this to the OrderDetails table.

(42)

(43)

Exercises

Do: Condition on a Field in a Table

AdvancedScript-ing.qvw QlikView file in the working directory for this chapter

(C:\QlikViewTraining\DeveloperII\Chapter05).

2 Open the Edit Script dialog and go to the Dimensions tab.

3 Find the Categories table and place the cursor after the last field of this table.

4 Type a comma and press ENTER to get to a new row. Type the following to create the CategoryType.

IF(CategoryID = 5 OR CategoryID = 6, 'Footwear', 'Clothing') AS CategoryType;

The IF statement in QlikView uses the following syntax:

if( condition , then , else )

The condition should be evaluated to be either true or false. If the condi-tion is true, the then part will be processed. However, if the condicondi-tion is false, the else portion of the statement will be processed.

5 Save the script and Reload.

6 Look at the fields. You can now see that we have a new field named

Cat-egoryType.

Do: Aggregation

1 Open the QlikView file you have been working on in this chapter. 2 Open the Script Editor and place the cursor after the OrderDetails table

on the Orders tab.

3 Add the following statement to your script:

LOAD OrderID,

sum(LineSalesAmount) AS OrderSalesAmount

RESIDENT OrderDetails GROUP BY OrderID;

(44)

44

Do: Joining Tables

1 Go to the Script again and place the cursor just in front of LOAD in the table just created.

2 Type LEFT JOIN (Orders) in front of the LOAD statement. The result should be as below.

LEFT JOIN (Orders) LOAD …

Here we use a LEFT JOIN load because we want to make sure that we do not get any values of Orders that do not exist in the Orders table. In QlikView, the default join behavior is a full outer join. Therefore, if there are no matching fields between the two joined tables, you will get a Car-tesian product of the records. Since we are specifying OrderID in both tables, and we are specifying Left, only the records matching OrderID included in the Orders table will be included. We include the

OrderSale-sAmount field because that is what we want to add to the Orders table.

Do: Concatenation

1 Open the Edit Script dialog in the QlikView file you have been working on in this chapter.

2 Position your cursor on the File Data tab directly after the Employees table has been loaded. We need to duplicate the fields we currently have for Employees, so we will not use the File Wizard in this case. Instead, copy the Employee LOAD statement, and paste the copied text after the original text.

3 Since the new file data format matches our first file, we only need to change the source of the data. Revise the From clause in the new load statement to read as follows:

FROM Datasources\Employees_New.xls (biff, embedded labels, table is [Employee$]);

4 Click OK and Save the QlikView document. 5 Run the script.

If you notice a number of new Synthetic Keys, or a new $Table value of

Employee-1, you know something did not work correctly with

auto-matic concatenation.

You can avoid a number of potential problems with automatic concate-nation by using the concatenate prefix on load statements that you know should be concatenated.

6 Add the concatenate prefix to the new Employee LOAD statement, and specify the Employees table.

(45)

This will always concatenate these two tables together, even if inadver-tent script changes are made later to one of the loads, but not the other. The new Employee LOAD statement should now begin as follows: Concatenate (Employees) Load

7 You may have noticed that there are very few differences between our two Employee LOAD statements. In fact, we can use another QlikView feature to load the same data in just a single load statement. By using a wildcard specification on the FROM file name, QlikView will automati-cally load from all files matching that specification, and concatenate the data into a single logical table for you. Since both our file names start with “Emp”, and have the “.xls” file extension, we can use the wildcard “Emp*.xls” in the FROM clause. If we make this change, and comment the second Employee LOAD statement, the script should now read as fol-lows:

Employees:

Load Office & ‘-’ & EmpID as BudgetKey, EmpID AS EmployeeID,

//[Last Name], //[First Name],

[First Name] & ' ' & [Last Name] AS Name, Title,

[Hire Date],

Year([Hire Date]) AS [HireYear], Office,

Extension, [Reports To], [Year Salary]

FROM Datasources\Emp*.xls (biff, embedded labels, table is [Employee$]);

//Employees:

//Concatenate (Employee) Load

//Office & ‘-’ & EmpID as BudgetKey, //EmpID AS EmployeeID,

(46)

46

//Extension, //[Reports To], //[Year Salary]

//FROM Datasources\Employees_New.xls (biff, embedded labels, table is [Employee$]);

8 Save the revised script and the QlikView document. Then Reload, and verify the Employee data has not changed.

9 As an optional exercise, you may want to try to determine why the employees listed in the Employees_New.xls file are not assigned e-mail addresses (field e-mail is null for these employees). What do you need to do to correct this problem?

Do: Preceding Load on Preceding Load

1 In the QlikView file you have been working on in this chapter, go to the

Script Editor and place the cursor at the bottom of the Mapping Loads tab.

2 Create the following table either by typing it from scratch or by using the Select button.

UnitCost_Map: MAPPING LOAD ProductID, UnitCost; SQL SELECT * FROM Products;

3 Go to the Orders tab and add the following script line to the bottom of the OrderDetails table just above the SQL SELECT * line. Remember to remove the semi-colon from the line above and replace it with a comma.

applymap('UnitCost_Map', ProductID, 0) * Quantity AS

CostOfGoodsSold;

We combine the applymap function with a calculation and create the

CostOfGoodsSold field directly in the preceding LOAD of the OrderDe-tails table.

The last of the remaining key measures that we need to create in the script is the Margin. The Margin is calculated as the LineSalesAmount –

CostOfGoodsSold. The easiest way to do this is to place a preceding

load on top of the preceding load in the OrderDetails table. You can add several preceding loads on top of each other and they will be evaluated from the bottom and up. This means that you can use a field created in a preceding load in a new preceding load on top of the first one.

(47)

4 We will use this functionality to create the Margin field. 5 Put the cursor after the OrderDetails label.

6 Create a preceding load by adding the following script.

LOAD

LineSalesAmount - CostOfGoodsSold AS Margin,

* ;

7 The full OrderDetails script should look like this:

//*************** Order Details table OrderDetails:

LOAD

LineSalesAmount - CostOfGoodsSold AS Margin, * ; LOAD Discount, LineNo, OrderID, ProductID, Quantity, UnitPrice,

UnitPrice * Quantity * (1 - Discount) AS LineSalesAmount,

applymap('UnitCost_Map', ProductID, 0) * Quantity AS

CostOfGoodsSold;

SQL SELECT * FROM `Order Details`;

LEFT JOIN (Orders) LOAD OrderID,

sum(LineSalesAmount) AS OrderSalesAmount RESIDENT OrderDetails

(48)

(49)

Extra Credit

Do:

AdditionalExer-cises.qvw QlikView file in the extra cradit folder for this chapter

(C:\QlikViewTraining\DeveloperII\Chapter05\ExtraCredit_Chapter05). 2 To clean up the script a little more, Join the Categories table with the

Products table. Make sure not to get any Categories that do not exist in

the Products table.

//************** Categories table ************** Categories:

LEFT JOIN (Products) LOAD CategoryID, CategoryName, Description AS CategoryDescription, IF(CategoryID = 5 OR CategoryID = 6, 'Footwear', 'Clothing') AS CategoryType; SQL SELECT * FROM Categories;

3 Create a pivot table with CategoryType and CategoryName as dimen-sions.

4 Create the following four expressions:

Sales Sum(LineSalesAmount) COGS Sum (CostOfGoodsSold) Margin Sum (Margin)

Margin % Sum (Margin)/ Sum (LineSalesAmount)

(50)

(51)

QlikView Developer II |6 ADVANCEDCALCULATIONSINSHEETOBJECTS

6

ADVANCEDCALCULATIONS INSHEET OBJECTS

6.1 Set Analysis

QlikView has always been good at calculating aggregates for the current selection of data. However, when you wanted to compare results for differ-ent selections in the same chart, you needed to either prepare data in the script or resort to rather complicated expressions with if clauses.

Set analysis changes all that, by making it possible to modify any aggrega-tion funcaggrega-tion with an arbitrary selecaggrega-tion set.

The set may be defined as a bookmark, as an on-the-fly selection in one or more fields, as a function of current selections, the inverse of current selec-tions, previous selections or all data.

The possibilities are endless and yet the syntax is fairly simple and straight-forward.

Indirect Set Analysis

Selections in a field can be stated based on selections in another field, such as

Select all possible values in Customers based on Sales last year. 6.1.1 Overview

Sets can be used in aggregation functions. Aggregation functions normally aggregate over the set of possible records defined by the current selection. But an alternative set of records can be defined by a set expression. Hence, a set is conceptually similar to a selection.

Objectives

• Introduce advanced calculations in charts and tables, including: • Set Analysis

• Dollar-Sign Expansion • AGGR Function

(52)

52

The $ sign represents the records of the current selection. The set expression

{$} is, therefore, the equivalent of not stating a set expression at all. {1-$} is

all the more interesting as it defines the inverse of the current selection, that is, everything that the current selection excludes.

Selections from the Back/Forward stack can be used as set identifiers, by use of the dollar symbol: $1 represents the previous selection and is equivalent to pressing the Back button. Similarly, $_1 represents one step forward and is equivalent to pressing the Forward button. Any unsigned integer can be used in the Back and Forward notations. $0 represents the current selection. Finally, bookmarks can be used as set identifiers. Either the bookmark ID or the bookmark name can be used, BM01 or MyBookmark.

6.1.3 Set Operators

Several operators are used in set expressions. All set operators use sets as operands, as described above, and return a set as result. The operators are as follows:

+ Union. This binary operation returns a set consisting of the records

that belong to any of the two set operands.

– Exclusion. This binary operation returns a set of the records that

belong to the first but not the other of the two set operands. Also, when used as a unary operator, it returns the complement set.

* Intersection. This binary operation returns a set consisting of the

records that belong to both of the two set operands.

/ Symmetric difference (XOR). This binary operation returns a set

consisting of the records that belong to either, but not both of the two set operands.

The order of precedence is 1 Unary minus (complement)

2 Intersection and Symmetric difference 3 Union and Exclusion.

Within a group, the expression is evaluated left to right. Alternative orders can be defined by standard brackets, which may be necessary since the set operators do not commute, i.e. A + (B – C) is different from (A + B) – C which in turn is different from (A – C) + B.

Set Operator Examples: sum( {1-$} Sales )

(53)

sum( {$*BM01} Sales )

returns the sales for the intersection between the current selection and bookmark BM01.

sum( {-($+BM01)} Sales )

returns the sales excluded by current selection and bookmark BM01.

6.1.4 Set Modifiers

A set can be modified by making an additional or a changed selection. Such a modification can be written in the set expression.

The modifier consists of one or several field names, each followed by a selec-tion that should be made on the field, all enclosed by < and >as in

<Year={2007, 2008}, Region={US}>

Field names and field values can be quoted as usual, e.g. <[Sales Region]={’West coast’, ’South America’}>.

There are several ways to define the selection:

A simple case is a selection based on the selected values of another field, e.g. <OrderDate = DeliveryDate>. This modifier will take the selected values from “DeliveryDate” and apply those as a selection on “OrderDate”.

The most common case, however, is a selection based on a field value list enclosed in curly brackets, the values separated by commas, e.g. <Year =

Note: The use of set operators in combination with basic aggregation

expressions involving fields from multiple QlikView tables may cause unpredictable results and should be avoided. E.g. if “Quantity” and “Price” are fields from different tables, then the expression

sum($*BM01} Quantity * Price) should be avoided.

Note: If there are many distinct values – more than a couple of hundred –

(54)

54

Further, the selection within a field can be defined using set operators and several element sets, such as with modifier

which will select all years beginning with “20” in addition to “1997”,

except for “2000”.

The above notation defines new selections, disregarding the current selec-tion in the field. However, if you want to base your selecselec-tion on the current selection in the field and add field values, e.g. you may want a modifier <Year = Year + {2007, 2008}>. A short and equivalent way to write this is <Year += {2007, 2008}>, i.e. the assignment operator implicitly defines a union.

Also implicit intersections, exclusions and symmetric differences can be defined using “*=”, “–=” and “/=”.

Finally, for fields in and-mode, there is also the possibility of forced exclu-sion. If you want to force exclusion of specific field values, you will need to use “~” in front of the field name.

6.2 Dollar-Sign Expansion

Dollar-sign expansions are definitions of text replacements used in the script or in expressions. This process is known as expansion - even if the new text is shorter. The replacement is made just before the script statement or the expression is evaluated. Technically, it is a macro expansion.

Note: Searches are case-insensitive and are made over excluded values

too.

Tip: Empty element sets, either explicitly e.g. <Product = {}> or implicitly

e.g. <Product = {"Perpetuum Mobile"}> (a search with no hits) mean no

product, i.e. it will result in a set of records that are not associated with

any product.

Note: A set modifier can be used on a set identifier or on its own. It

can-not be used on a set expression. When used on a set identifier, the modi-fier must be written immediately after the set identimodi-fier, e.g. {$<Year = {2007, 2008}>}. When used on its own, it is interpreted as a modification of the current selection.

(55)

A macro expansion always begins with $( and ends with ) and the content between brackets defines how the text replacement will be done. To avoid confusion with script macros we will henceforth refer to macro expansions as dollar-sign expansions.

6.2.1 Dollar-sign Expansion using a variable

When using a variable for text replacement in the script or in an expression, the syntax

$ (variablename)

is used. $(variablename) expands to the value in variablename. If

variable-name

does not exist the expansion will be the empty string.

For numeric variable expansions, the syntax $ (#variablename)

is used. $(#variablename) always yields a legal decimal-point reflection of the numeric value of variablename, possibly with exponential notation (for very large/small numbers). If variablename does not exist or does not con-tain a numeric value, it will be expanded to 0 instead.

6.2.2 Dollar-Sign Expansion with Parameters

Parameters can be used in variable expansions. The variable must then con-tain formal parameters, such as $1, $2, $3 etc. When expanding the vari-able, the parameters should be stated in a comma separated list.

If the number of formal parameters exceeds the number of actual parame-ters, only the formal parameters corresponding to actual parameters will be expanded. If the number of actual parameters exceeds the number of formal parameters, the superfluous actual parameters will be ignored.

The parameter $0 returns the number of parameters actually passed by a call.

Note: Macro expansion is unrelated to script macros (VB or Java script

(56)

56

$(=Year(Today())) returns the calendar year based on the system date, so if your system date is 28 May 2009, 2009 would be returned

$(=Only(Year)-1)returns the year before the selected one

6.3 AGGR Function

AGGR is a powerful QlikView function that returns a set of values of expression calculated over dimensions. The result can be compared to the expression column of a local chart, evaluated in the context where the aggr function resides. Each dimension must be a single field and cannot be an expression (e.g. a calculated dimension).

If the expression argument is preceded by the nodistinct qualifier, each com-bination of dimension values may generate more than one return value, depending on underlying data structure. If the expression argument is pre-ceded by the distinct qualifier, or if no qualifier is used at all, each combina-tion of dimension values will generate only one return value.

By default, the aggr function will aggregate over the set of possible records defined by the selection. An alternative set of records can be defined by a set

expression.

By using this function in calculated dimensions it is possible to achieve nested chart aggregation in multiple levels.

When aggr is used in chart expressions it is possible to achieve sum-of-rows totals in a pivot table.

Examples:

aggr( sum(Sales), Country )

aggr( nodistinct sum(Sales), Country ) aggr( sum(Sales), Country, Region ) count( aggr( sum(Sales), Country ))

(57)

Advanced Calculations Exercises

Do: Set Analysis Exercise

Create a Straight Table chart that displays a comparison of annual sales by CompanyName based on the year selected by the user.

1 Navigate to the c:\QlikViewTraining\DeveloperII\Chapter06 directory and open the Set_Analysis.qvw file.

2 Save a copy of the file to preserve the original in case you want to start again from the beginning later. Do this by using the File | Save As com-mand. There is also a QlikView file ending in “_Solution.qvw” contain-ing the completed exercise for your reference.

3 Double check to be sure there is a list box on the sheet for the Year. If not, add one.

4 Right-click in a blank area of the sheet and New Sheet Object | Chart from the context menu.

5 Click on the Straight Table icon (the lower right corner of the Chart Types) and type Annual Comparison in the Window Title. Click on the

Next

button.

6 Add Customer to the Used Dimensions and click Next

7 Create the following three Expressions using the Labels provided:

8 Click Finish

9 Save

your QlikView file and then continue to edit the Annual

Compari-Label Expression

1 =Only(Year) Sum({$<Year={$(=Only(Year))} >} LineSalesAmount)

2 =Only(Year)-1 Sum({$<Year={ $(=Only(Year)-1)}>} LineSalesAmount)

3 =Only(Year) & ' vs '

& (Only(Year)-1)

Sum({$<Year={$(=Only(Year))} >} LineSalesAmount) - Sum({$<Year={ $(=Only(Year)-1)}>} LineSalesAmount)

(58)

58

11 On the Visual Cues tab, make the negative values for the year-to-year comparison red and the positive values green.

(59)

12 Return to the General tab and add a Calculation Condition to ensure that the user selects a Year to begin the comparison by entering the fol-lowing into the Calculation Condition box

Count(distinct[Year])=1

13 Click on the Error Messages button on the General tab and then on Cal-culation Condition Unfulfilled in the Standard Messages list.

14 Type: Select a Year to compare with a previous year in the Custom Mes-sage box and click OK.

(60)

60

16 With 2009 selected in the Year list box you added at the beginning of the Exercise, your straight table should look something like the one below:

17 Save your work.

Do: Advanced Set Analysis

1 Continue working in the file you have been using in this exercise chapter so far.

2 Create a chart that compares sales of products in the category Babywear with products in the category Children´s Wear over time for the Nice sales office.

3 To do this you will need to create an expression using Set analysis and $

Expansion instead of traditional if() statements.

4 Set the category name to find Babywear and the office to be 4. 5 Pay attention to the <> and {}.

(61)

Do: AGGR Function Exercise

1 Continue working in the file you have been using in this exercise chapter so far.

2 Create a table that shows if there is any link between the number of orders placed by customers and the average order value. The table should provide information on how many customers have placed one order, two orders etc, and also the average order value.

3 There are three steps to this process. First, create a calculated dimension for the number of orders (as in “how many customers had one order,

Solution:

sum({$<CategoryName={'Babywear'}, Office={4}>} LineSalesAmount) sum({$<CategoryName={'Children´s wear'}, Office={4}>} LineSalesA-mount)

(62)

62

4 Next you need a count of customers to populate the # of order “buck-ets” we created in the first step.

Count(distinct CustomerID)

5 And, finally, we need to find the average order amount.

Sales is from the Sales Detail Table and is a line item for every product sold. Thus one order could have several products and thus several lines with Sales data, so we need to aggregate by OrderID to get the total sales amount for an order

aggr(sum(LineSalesAmount),OrderID)

gets you that number and adding the avg() gives you the requested aver-age order amount

(63)

QlikView Developer II |7 SCRIPTINGANDDATAMODELINGCHALLENGES

7

SCRIPTINGANDDATAMODELINGCHALLENGES

7.1 Link Tables and Concatenated Tables

Link tables combine information that has different levels of detail or fre-quency. Generally, they are best used when the metrics being measured do not have the same base of time, perhaps one is an aggregation over a time period, i.e., by Month. Link tables are also used when data does not meet the business requirements of a calculation. So you can think of them in terms of their name. They are useful to link information that would not oth-erwise be automatically linked with QlikView’s associative logic.

In other words, when loading data, certain scenarios can occur whereby you need to store fact type information in different tables, such as transactions separated from budgets. Each type of fact may need to join with multiple common dimensions (such as Time and Product) which would then cause loops in the datastructure. To circumvent such scenarios, we create linking tables that store the individual ways that each fact table joins to the common

dimensions. 7.1.1 Concatenation

Concatenation can be used to reduce the number of fact tables in a structure if there is sufficient commonality between the fact tables to validate such a combination. This can make a data model more efficient.

When fact tables contain the same granularity of information and a com-mon key is found between both facts, an outer join operation is preferable to a concatenate operation, as the output records would be linked on a record-by-record basis and hence single record operation will be possible.

Objectives

• Understand the use link tables • Calculate net change within a field • Use advanced functions for handling time

(64)

64

We can also see what has been loaded in the load process with the

previ-ous() function. The previous()function can only be used to look one record back in the load process. If you need to look further back than one record in the load process, you have to nest the previous() function as in:

previous(previous(myRecord))

This can be problematic and cumbersome when you want to see the total net change or percent change.

Let us assume we need to count the number of units that are at a given site within a week and need to find the percent of utilization for a unit at a given site.

If the units are at multiple sites but return to the same site within the same week, peek()and previous() can work but require complex coding with

FOR loops or RESIDENTloads. However, we can get around that by grouping the information from a tracking table and Left Joining the result back into the tracking table.

The steps in the exercise demonstrate the difference.

7.3 Functions InDate, Data & DateIslands

This section includes concepts used to create documents: InDate and Data/ DateIslands.

Data/DateIslands are tables that are not directly linked with other tables (tables that are independent of any explicit links). You can think of this as an array of values that can change with a selection by the user and will force new results to be displayed in the Qlikview Data Model via a non-key field connection.

However, when using this approach you will not change the selection state in the underlying QlikView Data Model. This is usually used for KPIs so that, when moving to a tab with more detail, the user has a full view and selection of data for filtering.

If there is a selection made in the QlikView Data Model outside of a Data/ DateIsland

,

that select will remove data used in the calculation of a KPI and could result in blank or misleading KPI displays.

The InDate() functions are used in conjunction with the Data/DateIsland.

7.4 Aggr()

Note: If you have already completed Chpater 6, skip this explanation. Aggr() is an advanced function that allows you to calculate an aggregation of multiple dimensions. As a rule, you can not aggregate an aggregated

(65)

expression (nested aggregations). The exception to this restriction is to use

aggr()

; the format is:

aggr([distinct | nodistinct] expression {,dimension})

The best way to think of aggr()is that, by using this function, you can cre-ate a mini QlikView data model on the fly, without needing to edit your data load script or reload from the source data for your QlikView file. It allows for nested aggregation. You can add more dimensions than are represented in charts such as a Pivot Table.

Aggr() is used in the user interface, rather than in the load script. Typically, it is used in a chart object expression, as we saw in the previous chapter.

7.5 Class()

Class() is an aggregation function that can be used to create buckets of information similar to what you would create in an Accounts Receivable document showing aged accounts. Another way of thinking of Class() is that you can group your dimension values.

7.6 Dynamic Aggregation

In QlikView, you can assign a field value to a variable. Subsequently, you can use the value that the variable holds in an expression. This is similar to a pointer in C-programming or other similar programming languages. QlikView calculates information on the fly. With this approach and using a table to hold expressions, you can create a reference to the formulas located in the table which, in turn, effectively creates a library of expressions that can then be used to dynamically change your display of information. The user will be able to cycle through the KPIs by selecting choices in a List Box.

7.7 IntervalMatch

The IntervalMatch script statement facilitates the mapping of dates to peri-ods or records to slowly changing dimensions, useful for creating a fully

(66)

66

memory and User Interface performance can suffer. One solution to explore is to LEFT JOIN the IntervalMatch() table into a parent table.

Here is a small example of IntervalMatch(). We are maintaining the approach of keeping the examples small so that they can be easily verified.

In this exercise we will use IntervalMatch() to find the hours that an employee is working on-site.