I needed to load a one percent random sample of a large file last week. I’m sure others have figured out this method but this was the first time I coded it like this: MYSAMPLE: Load * from MYFILE.qvd (qvd) Where Rand()<=0.01; The Rand function returns a random value between 0 and 1. In the load statement above it would only be true 1% of the time (approximately) -- that would load only 1% of the rows. If it was coded as Where Rand()<=0.20 then it would be a 20% random sample, etc. This method can be used with almost any Load statement even one of the steps in a preceding load.  ..read more

I often use Rob Wunderlich’s excellent Copy Groups utility to copy cycle groups from one QlikView document to another; or to copy a cycle group from a document back into the same document to make a duplicate (to see the utility and get your own copy, search on ”QlikView Cookbook Copy Groups Utility”). But, sometimes after copying the cycle group, the name of the group may not be ideal. Author's note:  I removed the rest of this post after seeing Andre's comment. Thanks, Andre - that was something I didn't know... and it's much simpler than what I wrote. Portion of Andre's comment: A ..read more

Some of our project teams at work are required to use a standard QlikView document for several clients and then use a client-specific loadscript .qvs file to configure custom variables for each client. The loadscript files define the variables with a set of Let statements and I often get questions about the syntax for the Let statements. Some of the variables define complex expressions and they include dollar signs and functions and quotes and some definitions use multiple lines so the Let statement syntax can get tricky. I recommend that people do their development first so that they have a ..read more

I’ve written in this blog before about how I’m often called on to validate a new data file from a client or to examine a new data extract. My first step is to load data into a copy of Steve Dark’s Data Profiler (Steve's original post is useful - search on quickintelligence data profiler). The data profiler gives me information about exactly what values exist in each of the fields. I’ve made a few modifications to Steve’s original document: Added a table box that adjusts itself to the fieldnames in the file Added a small macro that updates the Statistics Box with the fieldname auto ..read more

I work with a group of people who are all experts in SQL. They all also have varying levels of technical skills and QlikView skills. One question I get frequently is whether QlikView has a function like the SQL NVL function. For those of you not familiar with relational database SQL language, the NVL function takes two arguments: a field name or expression and a default value to be returned if the first argument is null. For example, a SQL database query may include the function like this:   NVL(ORD_DISCOUNT, 0) AS ORD_DISCOUNT And that would tell the SQL processor to look at the value ..read more

My co-worker, Naveen, had a requirement for a document that would load some financial data from a file that a client had sent to us. The client had created the file with a “screen scraping” type of application that captured the number fields as edited numeric text. For example, a field might contain  ($3,046.10)   So, that example includes a dollar sign, comma as a thousands separator, period as a decimal point, and parenthesis to indicate a negative number or credit. Naveen needed to load the data as a number. Here’s how it was done: We used two functions. A Num# function co ..read more

We had a requirement a few months back for a document that would show a table with different expressions on each row along with some subtotals and expressions that reference other rows and columns. It needed to be a single table that could be sent to Excel. It was obviously a requirement that grew out of existing, legacy reporting solutions using Excel but many corporations are wedded to Excel and the larger the corporation, the harder it is to find anyone who believes they have the authority to make changes or do something different. The solution was to build a document with a straight tabl ..read more

Here’s an example of a calendar table built with a series of preceding load statements. The preceding load technique isn’t essential – this calendar table could have been built with a set of resident tables – but it is a good illustration of preceding load where each level of the load is a major revision of the level below. This example code comes from a loadscript where the earlier part of the script has already loaded 50 million rows of transaction data. This calendar table part runs in a few seconds. The client for this example uses a July through June fiscal year and I left in the calcul ..read more

A few days ago I needed a calculation in a loadscript that would provide the number of months between two dates. This kind of calculation comes up sometimes with supply chain planning data when the difference between the month a sales forecast is developed and the month that the sales forecast is meant to be applied, sometimes called a monthly lag, can be used to help measure forecast accuracy. QlikView provides a number of date functions but not one that can calculate the number of months between two dates. I decided to make my own MonthsBetween date function using a document variable with ..read more

We have a lot of QlikView documents that measure various kinds of business planning performance. The measures are usually a calculation of the difference between planned values and the actual values. Then the calculation is normalized by dividing it either by the planned or actual values so that the measure can be shown on a document as a percentage. Key measures or key performance indicators (KPI) like that, if they are well chosen and well designed, give people an idea of how well a business process is performing. I believe that the measure can be even more useful if we add to it an indic ..read more