QlikView Maven

Loading a 1% Random Sample

2018-05-04T16:39:00.001-04:00

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.
Note that we're talking about random numbers here so the total number of rows loaded using this technique will not be exactly 1% of the total rows in the file, although it will be close especially when the total number of rows in the file is very large. Be aware too that the rows that get loaded will not be an evenly distributed sample of rows although it will be close.

★★★

Rename a Cycle Group

2018-03-14T16:54:00.000-04:00

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 group can be renamed by going to Document Properties | Groups | Select Group | Edit | Enter New Name | OK

This changes where the group is used in a chart dimension as well

Utility That Can Extract and Show All of the Variables from Your QlikView Document

2018-01-19T16:36:00.003-05:00

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 working document with all of the desired features and variables that are required. Then, they can use a QlikView document I wrote to be used as a utility program to help people with managing their variables.

You can download a working copy of the utility program, named List_Out_Variables.qvw, by clicking HERE

The utility program has a tab with some text instructions and an input box where people type in the complete pathname to the custom .qvw document that they’ve been working on. Then, they do a reload of the utility program and it extracts all of the variables from the custom document.

The input box stores the pathname in a variable named vDocumentPath. Part of the loadscript in the utility program looks like this:

VARIABLES:
LOAD
[Name] as VARIABLE_NAME,
[RawValue] as VARIABLE_VALUE
FROM $(vDocumentPath) (XmlSimple, Table is [DocumentSummary/VariableDescription]);

That loadscript code extracts the variables from the document and makes the variable names and values available to be shown in charts. (I borrowed the loadscript code from a Rob Wunderlich comment I found in QlikCommunity.)

The second tab on the utility is pretty simple, it is table box showing the VARIABLE_NAME and VARIABLE_VALUE fields along with list boxes for those two fields so that people can select the variables they are interested in. The utility is pretty useful right there as a way to analyze variables, sort them, print them, copy and paste to other documents, etc.

There is a third tab with a chart that helps people with the Let statement syntax that I mentioned. The chart on that tab uses VARIABLE_NAME as the dimension but I found it helps to have the dimension column hidden. Then, there is one expression that converts the VARIABLE_VALUE into a Let statement. The expression looks like this:

'Let '&VARIABLE_NAME&'='&chr(39) &
Replace(Replace(Replace(VARIABLE_VALUE,vQUOTE,vQR),vDOLLAR,vDR),vCR,vCRR)
& chr(39)&';'

An example of how that looks in the actual chart is:

Let MoneyFormat=''&chr(36)&'#,##0.00;('&chr(36)&'#,##0.00)';

To make that work I had to define a few variables in the utility program. It made that text expression much simpler to write. The variables in the utility program are:

vDollar is a variable containing a single dollar sign

vQuote is a variable containing a single quote

vCR is a variable containing a carriage return (really simple, when defining this one just put your cursor in the variable definition window and click your Enter key).

vQR is '&chr(39)&'

vDR is '&chr(36)&'

vCRR is '&chr(13)&'

If you download a copy of the utility program then you don't have to worry about any of that coding as it is already done for you.

After loading a set of variables extracted from your special document, my instructions tell people to make their selections and then, from the chart, they can right-click and export the chart or copy the data to the Windows clipboard so it can be pasted into a .qvs file or pasted into a loadscript or pasted in some other document.

Having an easy method to define a set of variables as Let statements can be used to copy variables from document to document or to accompany a version control process where variables and their values need to be stored as text in a way that allows them to be analyzed or restored later.

Some folks familiar with this blog may remember that several years ago I wrote about an Excel spreadsheet containing a macro that could extract QlikView variable names and values. It worked great but is no longer useful in our corporate environment as the corporate anti-malware software considers it a “suspicious program” and refuses to let it run. The List_Out_Variables.qvw utility is a good replacement.

★★★

Data Profile Tool

2017-05-27T14:15:00.001-04:00

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 automatically as different fieldnames are selected. A trigger detects the change and automatically configures the Statistics Box
Added a tab to help search for duplicate values in either a field or combination of fields
A check in the loadscript to see if the file exists and if it does not exist then it opens a msgbox from the loadscript using a function

You can download an example copy of the file data profiler that I use by clicking HERE

I also keep a version of the data profiler that is tailored for loading data from an Oracle table and a version of the profiler tailored for loading data from a qvd file. I think I use the data profiler tools almost every day to examine data in files and tables to help with QlikView document design and other reporting requirements.

These two examples have no data loaded - you will have to do a reload yourself from your own data:

You can download an example copy of the profiler I use for qvd files by clicking HERE
You can download an example copy of the profiler I use for Oracle tables by clicking HERE

★★★

Have you got a function similar to SQL NVL ?

2017-05-14T11:29:00.000-04:00

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 of ORD_DISCOUNT and if it is null then return 0 as the function value otherwise return ORD_DISCOUNT.

When people ask about achieving the same thing with QlikView, I usually start by telling them that they can code an “if” statement like this:

If(IsNull(ORD_DISCOUNT), 0, ORD_DISCOUNT)

and then I explain that there is a built-in QlikView function that can be used similar to NVL as long as the field you are checking is supposed to be numeric. It is the Alt function.

The Alt function accepts any number of arguments and looks at each of them going from left to right and returns whichever one is a valid number. So, repeating our example, if ORD_DISCOUNT is null then the following function will return a zero but if ORD_DISCOUNT is a valid number then the function returns ORD_DISCOUNT:

Alt(ORD_DISCOUNT, 0)

The Alt function will treat the last or rightmost argument as an “else” condition and return that value if none of the preceding arguments are numeric. The rightmost value need not be numeric so you could code something like this:

Alt(ORD_DISCOUNT, 'Discount is missing')

The Alt function may be used in the loadscript code or in chart expressions.

If you are interested, the QlikView Help (search in Help for Conditional Functions) shows an interesting example for how the Alt function can be used to identify a date when the date value may be any one of several different date formats.

★★★

Converting Edited Number Text into a Numeric Field

2017-04-05T12:25:00.001-04:00

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 converted the text string into a dual field containing both a text portion and a numeric portion. Then, an outer Num function extracted just the numeric portion. For example, one of the number fields was named AUG. In the loadscript, the line that converted the edited number into a simple numeric field looked like this:

Num(Num#(AUG, '$#,##0;($#,##0)'))

The edit string or format code, the part within the single quotes, can vary depending on your requirement. The edit string I used in the example is a good one for the kind of editing you might find in a financial spreadsheet.

Here's a few other examples of edited number text and how it looks after using this expression:

AUG	Num(Num#(AUGUST,'$#,##0;($#,##0)'))
$0	0
$1	1
$1,251	1251
$1,465	1465
$1,466	1466
$3,724	3724
$4,158	4158
($11)	-11
($132)	-132
($2)	-2
($4)	-4
What now?	(this is a null)
19	19
25.12	25.12
25.1234	25.1234
25.3	25.3
2,070	2070

★★★

Straight Table with Different Expressions on Each Row

2016-12-28T13:03:00.000-05:00

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 table with a single dimension that is loaded in the loadscript with an inline load like this:

TBL_KPI_TYPE:

LOAD * INLINE [

KPI_NO, KPI_TYPE

1, Transportation:

2, Intermodal % of Miles

3, Sea %

4, LTL %

5, Avg Pallets/Truck

7, Total Diesel Cost

8, Diesel $/Gallon

9, Diesel # of Gallons

10, Average distance to Customers

];

The straight table used KPI_TYPE as the dimension so those text values get listed in the leftmost column like any other dimension. The chart expression had to be slightly different for each column -- some columns were fiscal months, some fiscal quarters, one was a year-to-date column -- but they all looked something like this:

Pick(KPI_NO,

' '.

$(v.intermodel_pcent_miles),

$(v.sea_pcent),

$(ltl_pcent),

$(avg_palletspertruck),

' ',

$(total_diesel),

$(diesel_price),

$(diesel_tot_gallons),

$(avg_dist_to_cust)

)

This is simplified from the actual document.

Note that the Pick function in the expression uses KPI_NO which is defined in the inline load. The Pick function avoids “if” statements that can affect speed performance for the table as users change their selections.

The dimension isn’t a real data field. Each line in the expression must use the appropriate field names and set analysis to satisfy the requirement for the row. In the example above, variables are used for each row to help make the overall expression understandable at a glance.

Because each row is a different expression, the formatting must be done within the expression in the variable using the Num function. Some of those rows are integers, some are in thousands, some are money and some are percentages.

Because some of the expressions referred to other rows and columns (using the Above and RangeSum functions), I had to turn off the ability to drag and drop the columns. I also had to lock in the selection of all KPI_TYPE values so that the user wouldn’t accidentally make selections on KPI_TYPE. If the expressions were not using functions like Above then allowing the user to make selections on KPI_TYPE would make the chart more flexible.

Note that some of the KPI_TYPE values can be labels and the corresponding row in the expression is just a blank line. (Make sure the chart properties are not going to hide a row of all blanks.)

As Aaron mentions in a comment below, something similar can be achieved with a synthetic dimension using ValueList. I think ValueList is better suited to a small number of values but it offers the advantage that a user can't accidentally select one of the values by clicking on the chart; a downside is that it may consume more resources (versus an actual field dimension) when used with a large data model.

★★★

Calendar Table and Preceding Load

2016-12-04T12:13:00.001-05:00

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 calculations for fiscal month, fiscal quarter and fiscal year (they use a label like FY15/16 for fiscal year).

Remember to read the comments starting at the bottom since that is how the preceding load logic works. Note the Dual function used to make sure the fiscal months sort properly from July to June in charts.

//Calendar table is for all Monday WeekStart dates for the transaction data
TBL_CALENDAR:
LOAD
FDATE,
Month(FDATE) as CAL_MO,
Year(FDATE) as CAL_YEAR,
dual(Month(FDATE),if(num(Month(FDATE))>6,num(Month(FDATE))-6,num(Month(FDATE))+6)) AS FISCAL_MO,
dual('FY' & if(num(Month(FDATE))>6, right(Year(FDATE),2)&'/'&right(Year(FDATE)+1,2), right(Year(FDATE)-1,2)&'/'&right(Year(FDATE),2)),
if(num(Month(FDATE))>6, right(Year(FDATE),2),right(Year(FDATE)-1,2))) as FISCAL_YR,
Pick(Month(FDATE),'Q1','Q1','Q1', 'Q2','Q2','Q2', 'Q3','Q3','Q3', 'Q4','Q4','Q4') as QTR,
Pick(Month(FDATE),'Q3','Q3','Q3', 'Q4','Q4','Q4', 'Q1','Q1','Q1', 'Q2','Q2','Q2') as FISCAL_QTR;
//
//now, make a complete set of FDATEs between min and max
//without missing any weeks. Autogenerate keyword not needed
Load
Date(MINFDATE+(IterNo()*7)) as FDATE
While MINFDATE+(IterNo()*7) <= MAXFDATE;
//
//get the minimum (oldest) FDATE and maximum (newest) FDATE
Load
Weekstart(Min(FDATE)) as MINFDATE,
Weekstart(Max(FDATE)) as MAXFDATE;
//
//First, get all of the distinct FDATE values that have been loaded
Load date(FieldValue('FDATE', RecNo())) as FDATE
AutoGenerate FieldValueCount('FDATE');

I didn’t use it here but a WHERE clause will work with preceding load (WHERE EXISTS… is often useful) The ORDER BY clause seems to cause an error with proceding load but you can use logic like this example where the FDATE values are built and loaded in ascending order.

★★★

MonthsBetween Function

2016-11-18T13:29:00.000-05:00

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 input arguments. A variable like that can be used like a user defined function. I created a variable in the QlikView document and named it MonthsBetween. This is what I typed for the content of the variable (you, of course, can copy and paste from this blog post):
((Year($2)*12 + Month($2)) - (Year($1)*12 + Month($1)))

The $1 and $2 get replaced by two input arguments (which some people might call parameters) when the variable is used. The arguments must be dates. My loadscript (I was loading data from an Oracle database table) looked something like this:

BUDGET_HIST:
Load BUSINESS_UNIT,
BUDGET_AMOUNT,
CREATE_DATE,
APPLY_DATE,
$(MonthsBetween(CREATE_DATE, APPLY_DATE)) AS MONTH_LAG;
Select * from BUDGET_HIST_TABLE;

For example, if the CREATE_DATE is 1-APRIL-2016 and the APPLY_DATE is 1-JULY-2016 then MONTH_LAG is 3. If the second date is later than the first date then the result will be a positive integer number of months. If the two dates are from the same month then the result is a zero. For my data, the APPLY_DATE is always later than or equal to the CREATE_DATE so MONTH_LAG is always 0 or greater. If CREATE_DATE was later than APPLY_DATE then MONTH_LAG would be a negative number.

The calculation is nothing special -- it must have been re-created thousands of times. But, putting it into a variable makes the calculation easy to use in many places and easy to share. Test it out in a chart expression or advanced search expression.

When a variable contains expression code like that and the variable might be used as part of a larger expression in a chart, then it is a good idea to enclose the entire variable contents within parentheses as I did above. It helps prevent QlikView from processing the operators in the larger expression in the wrong order.

★★★

Is My KPI Getting Worse or Getting Better

2016-08-31T16:24:00.000-04:00

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 indication of whether the measure has been getting “better” or getting “worse” over time.

Most of the performance measures we use are aggregation calculations that can be used with various dimensions like customer, product, or geographic region. When you show a measure like that in a line graph where the x-axis is time (for example, weeks or months) the line is usually jagged with up and down measures and maybe a “hump” or two.

The first thing you can do to help someone figure out if the measure is getting better or worse is to use a trendline. In a line graph, go to the chart properties and pick the expression where you’d like to see the trend, find the trendlines section of the expression dialog and click the Linear check box. Now, click OK and you should see that QlikView has drawn a straight line across your jagged line graph. The upward or downward slope of the line can give a person some idea of whether the measure has been getting better or worse.
Note: I usually modify the Presentation tab of the chart properties and set Trendline Width to a narrow line (maybe 0.5) so it doesn’t compete with the main graph line for attention.

If you stop reading this post at this point, then you’ve already picked up something useful.

Some users of these documents say that they would like the document to point out the areas where a particular measure is getting very bad so they know where to spend their time or resources. We can use a function already built in to QlikView to provide us with a number that would, if it was a trendline in a graph, be the slope of the line.
If the line is going uphill from left to right, then that would be a positive slope and if it is going downhill then that would be a negative slope. A flat, level line would have a slope of zero. I’m going to describe here how to build a calculated dimension for a chart that can show the 20% of things where a measure has been deteriorating the worst over time.

First, to simplify the description, imagine that you have taken the performance measure expression from your straight table and put it into a variable. We’ll call the variable vKPI.
The time dimension field in the example is PWEEK.
And we are going to use this calculated dimension in a straight table where it will be labeled 20% of Projects Where KPI Has Deteriorated the Most
The linest function is used to calculate the slope of the imaginary graph line.
The Rank function will help identify the 20% of worst performing PROJECTs.
Aggr is used to create an array of PROJECT values for our chart dimension. In this example, the higher values indicate a deteriorating measure but you can flip the calculation around if your logic is reversed.

=aggr(if((Rank(
linest_m(
aggr(
=$(vKPI)
,[PROJECT],PWEEK)
,PWEEK)
,1)-1) / Count(distinct total [PROJECT]) < 0.20, [PROJECT]), [PROJECT])

I know this is one of the more complex topics I’ve tackled in this blog but the result in a document that analyzes key performance indicators is valuable. In the documents where I’ve used this concept I usually provide a method for the user to choose what dimension field they would like to use, so it is not limited to PROJECT so the user could choose PRODUCT, SALESPERSON, or REGION, etc. And then for even more flexibility, I put the calculated dimension into a cycle group made up of other calculated dimensions that identify things that need attention.

In my next post, I’ll show how a related calculation could be used to color code items in a chart so that the items where performance has been getting worse are tagged with red and items where performance has been getting better are tagged with green.

★★★

Chart Adjusts to Whatever Fieldnames are Loaded

2015-12-24T13:52:00.004-05:00

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 (search on quickintelligence data profiler). I’ve made a few modifications to Steve’s original document – here are a couple of the most useful.

I added a line to the loadscript that uses preceding load to add a record number to the rows being loaded. My line is added between the table label and the first line of the regular Load statement. It looks like this:

DATAFILE: //table label
Load *, recno() as zrecno; //this is the preceding load line
Load //this is the beginning of your "regular" Load statement

For the second set of modifications, I added a new sheet or tab which I named Table.
On the new tab, I added a multibox and then I added, not fieldnames, but 20 expressions. You can add any number you like but 20 seemed like plenty. These are mostly copy and paste so they are not a lot of typing and you can start by copying the expression shown in this blog and pasting it into your document. The first multibox expression looks like this:

[$(=only({1<$FieldNo={1}>} $Field))]

That expression evaluates to the fieldname corresponding to field #1. Then I add a second expression to the multibox:

[$(=only({1<$FieldNo={2}>} $Field))]

You can see that in the second expression, I changed $FieldNo={1} to $FieldNo={2} so that the second expression evaluates to the fieldname corresponding to field #2. I did that for each of the 20 expressions so that the 20th expression in the multibox contains $FieldNo={20}

Then, I added a straight table chart. I used zrecno as the dimension. Then, I added 20 expressions. For the first expression, I typed this:

[$(=only({1<$FieldNo={1}>} $Field))]

And for the label, I typed this:

$(=only({1<$FieldNo={1}>} [$Field]))

Again, the rest of the expressions are similar, each time changing the $FieldNo={1} to be 2 for the second expression and its label, $FieldNo={3} for the third expression and its label and so on for each of the 20 expressions. Click OK when you’re done.

Now, you have a multibox and a straight table chart that appear as though you typed in the 20 fieldnames. But, these objects will adjust themselves to whatever fieldnames are loaded. If you load a file with less than 20 fieldnames, the “extra” ones will just be null.

You can copy and paste these objects into other QlikView documents and they will adjust themselves to whatever the first 20 fieldnames are in the document (see important info in first blog comment below). But, I think these are most useful in a document where different fieldnames can be loaded each time.

I checked Dropdown Select for all columns in the Presentation tab to make it easier for the document user to make selections on the chart.

In order that we don’t burn up all of the memory on the computer, I added a calculation condition to the straight table chart with this expression =if(count(zrecno) < 200000,1,0) so that the chart will refuse to appear if there are more than 200,000 rows. I customized the error message for the calculation condition to say, “There’s more than 200k rows. You have to make some selections to reduce the amount of data in this chart.” A calculation condition like this is a good idea whenever you have an object that might try to show more data than is possible with the available memory.

★★★

Easy to Add Multivariate Analysis Feature

2015-12-01T16:20:00.000-05:00

Here is a technique that I’ve used several times over the past year to quickly add a multivariate analysis feature to a document. We have numerous documents that have a multibox for selections and usually a graph and at least one chart; usually a straight table with a cycle group for the dimension. I usually start with one of these existing documents.

This feature uses QlikView alternate states. I normally steer people away from using alternate states in documents used by business users as it can be confusing. But, in this technique, it seems to be easily used and understood.

The first step, is to add an alternate state to the document. Using the menu, choose Settings->Document Properties, then choose the General tab and click the Alternate States… button (if you don’t see the button then you may be using an older version that doesn’t have the Alternate States functionality). On the Alternate States dialog box that appears, click Add and then simply type a B and then click OK. This defines an alternate state in the document named B. It’s not a very descriptive name but it is perfect for this technique.

Now, we’re going to duplicate the multibox. Right-click on the multibox and choose Copy to Clipboard -> Object. Then, from the menu choose Edit->Paste. QlikView will paste the new copy right on top of the old one so you need to drag the copy of the multibox off to the right. This is easiest if the multibox properties Show Caption option is checked so you can click on the title bar at the top of the multibox and drag it. Make the caption of the original multibox say Selections: and make the caption on the new copy of the multibox say B Selections: It may help to make the caption font on the new copy in italics.

Easy, so far? Now, right-click on the new copy multibox and choose Properties. Choose the General tab and look for the Alternate State field at the top. Click the little down arrow on the Alternate State box and click to choose B and then click OK. You now have two multiboxes: one that controls the usual selections and one that controls an entirely different set of selections in alternate state B.

Here's the part that makes it useful. In your charts, you probably have a column something like this: Sum( Sales ) with the label Sales on it. Add a new column, label it Sales B, and put it right next to the original column. For the expression, type Sum({B} Sales)

The syntax is the same as for set analysis. But, this is simpler, just use the {B} inside the argument for any aggregation function and it makes the aggregation use the selections that are defined in your B Selections multibox. No need to change the chart dimensions or change anything in the load script.

Now, you can compare the column for Sales with the Sales B column with the numbers side by side. I assume the fields most useful for the data were already defined in the multibox. So, you can change the B selections to easily compare, for example, June sales next to March sales; or compare tier 1 customers versus tier 2 customers; or profit from the 1kg size versus the 500g size, or inventory turns at the Memphis warehouse versus turns at the combined California warehouses or total cost of the cheesy-beans business versus costs with administrative costs unselected,… etc.

As quickly as you can add a new field to the two multiboxes, you can have a side by side comparison of data for different selections on that field.

I’ve found that graphs that use this technique should use a similar but noticeably different appearance for the two expressions. Using the example from above, a graph of Sales could use a red line in the line graph and use a dotted red line for Sales B.

You can use color in the chart to point out the biggest differences between the regular aggregation and the B aggregation. I’ve sometimes used the Colormix wizard to color the rows with the biggest differences in bright yellow and the smaller differences with less bright shades of yellow (the Colormix wizard is described in the manual and it is well worth your time to become familiar with it).

It would be just as easy to also add C Selections but, so far, I haven’t found a case where it adds value.

★★★

How Many Days Since That Document Reloaded?

2015-03-07T11:43:00.000-05:00

I was challenged to write a simple blog posting. Here it is, although it still has some code in it.

A recent project required a document to “know” how many days ago a separate .qvw document had been reloaded. This statement in the loadscript uses the pathname to the separate document and puts the answer to “how many days ago” in the variable vDaysAgo:

LET vDaysAgo = Now() - FileTime('e:\ventures\PriceAnalysis.qvw');

★★★

General File Loading Technique

2015-01-31T17:36:00.000-05:00

Here’s a technique I first used several years ago when I started a project where we had several dozen data files, all of them containing different data, in a delimited format and each of them with a header record as the first record containing the field names. The loadscript code below shows how we could load each of the files into different QlikView tables using the same loadscript.

The code below has the filename in the code but it could be loaded into the QlikView document by including the filename as a variable in the command line execution of QlikView (for an example, search on "qlikview maven command line and automation"). Other options include building a loop and loading each of files in a folder (search on “qlikview maven loading all of the files from a folder”); or, if you are comfortable with macro module code, you could use a browse-for-file technique to make it easy to find and load a filename (search on “qlikview maven browse-for-file macro button”).

Here’s the loadscript example code: (some of the long lines are wrapped around as you view the code in the blog window but they will go back to normal if you copy and paste the code into a text file or loadscript edit window)

Set xfile='customer_data_12.csv'; //file pathname
Let xfile=Upper('$(xfile)'); //optional convert to upper case

FIELDDAY: //load first record only
Load * from $(xfile)
(txt, no labels, delimiter is '|')
where recno()=1; //note "no labels"

Set loadstatement='Load '; //now, build the Load statement
For i=1 to 100
Let fn=trim(SubField(peek('@$(i)',0,'FIELDDAY'),';',1));
if '$(fn)'='' then
exit for
end if;
if $(i)=1 then
Let loadstatement='$(loadstatement) @$(i) as [$(fn)]';
else
Let loadstatement='$(loadstatement), @$(i) as [$(fn)]';
end if
Next

$(xfile): //QlikView table name
$(loadstatement)
FROM
$(xfile)
(txt, no labels, header is 1 line, delimiter is '|'); //change delimiter character as needed

Drop table FIELDDAY;

Store $(xfile) into $(xfile).qvd (qvd); //optional save as qvd
//Drop table $(xfile); //optional drop table

Change the file attributes as needed for your situation: for example, Excel files, skip over unneeded header records, different delimiter, etc.

There are two important ideas illustrated in the example: first, the idea of loading the header record of a file and building a custom load statement on the spot; second, the idea that you can construct any loadscript statement or part of a statement in a variable and execute it just by putting the variable in the right place in the loadscript.

This technique helped me in a recent project where the data files were supplied with field names in the header record but all of the field names needed a little modification in order to use them. Some of them I converted to upper case and some of them needed to have special characters removed. It was easy to add the necessary text functions to edit the field names as the code assembled the load statement.

It will help you debug and validate this technique if you set the document properties option to write out a loadscript log file when the loadscript runs. The log file will show all of the lines in the loadscript as they were executed with variables fully expanded.

★★★

A Little More Optimized Load

2015-01-11T16:04:00.000-05:00

This is a continuation of last week’s blog topic (search on "QlikView Maven Optimized QVD Load With a Where Clause and Dates").
I had a load statement in a loadscript loading from a qvd file that (simplifying a bit) looked like this:

INVOICE_DATA:
Load CUST_NO,
ApplyMap('custprimap',CUST_NO) as CUST_PRIORITY,
INVOICE_NO,
INVOICE_AMT
from invoice_data.qvd (qvd)
where exists(INV_DATE);

It was written using a mapping table feature for customer data to avoid a left join load for performance reasons. But, the load from the qvd file wasn’t an optimized load because of the ApplyMap function. The table contained many millions of rows and using an optimized load would save quite a bit of time.
I was able to get an optimized load and still have the ApplyMap functionality by recoding that portion of the loadscript to also use a Join and a super-fast load from FieldValue like this:

INVOICE_DATA:
Load CUST_NO, //optimized load
INVOICE_NO,
INVOICE_AMT
from invoice_data.qvd (qvd)
where exists(INV_DATE);

Join (INVOICE_DATA) load //preceding load
CUST_NO,
ApplyMap('custprimap',CUST_NO) as CUST_PRIORITY;
Load FieldValue(CUST_NO,IterNo()) as CUST_NO
Autogenerate(1) while not isnull(FieldValue(CUST_NO,IterNo()));

______________ _________________ ________________

Note to Santhosh: You can add special comments to a chart expression that will make it easier to find specific expressions with the Settings->Expression Overview, Find feature. For example, you might use an expression like this:
sum(CONTRCT_S1 * CNTRY_ADJ) //per NATO procurement 1987.36.c
and then it would be easier to find the expressions that need to be changed when procurement rules change by doing an Expression Overview Find on "NATO procurement".
Our otherwise well-intentioned friends who believe all chart expressions should be contained within variables cannot easily use this suggestion.

★★★

Optimized QVD Load With a Where Clause and Dates

2015-01-07T22:37:00.000-05:00

If you are working with large quantities of data and especially if you are working with large data sets of historical data where you add a portion of new data to the historical data each week (an incremental load), then you will want to be working with qvd files in your loadscript. And if you are working with qvd files then you certainly want to load data from the qvd files with an optimized load. The optimized load is many times faster than loading the same data with an unoptimized load.

A common problem is that adding a where clause to the load statement will cause the load to be unoptimized. Some types of where clauses though, can be used with an optimized load. A simple where exists type of where clause, for example, can usually be used with an optimized load.

Every incremental load process is a little different but if your process involves dates then this idea may help. This idea is for situations where you want to load data from a qvd file and you want your where clause to refer to a date within the data. Here’s an example:

Imagine that you have a date like 01/01/2014 in a variable named start_dt and another date like 12/31/2015 in a variable named end_dt and you want to load invoice data where the invoice date, inv_date, is between start_dt and end_dt. First, build a little table of all possible dates between start_dt and end_dt. Use this in the loadscript before loading any other data containing a INV_DATE field:

/* Build table of dates just for the "where exists" clause */
TMP_DATE_TABLE:
Load date(date('$(start_dt)')+IterNo()-1) as INV_DATE
AutoGenerate(1)
While date(date('$(start_dt)')+IterNo()-1) <= date('$(end_dt)');

That code can build a large table of dates in your loadscript in just a few seconds. Now, you can code the load from qvd like this:

INVOICE_DATA: //this is an optimized load
Load * from invoice_data.qvd (qvd)
where exists(INV_DATE);
Drop table TMP_DATE_TABLE; //no longer needed

That table of dates can be varied as needed for your specific application. For example, using (IterNo()-1)*7 could give you a set of week start dates. Use whatever method makes sense to create a list of the dates that you would want to load if they exist within the qvd file data. Remember, the basic idea here is to build a table of values you would like to load from the qvd file. It is not limited to dates and not all of the values in your temp table have to actually match something in the qvd file.

____________ _________________ _________________

Note to Santhosh: If you need to quickly find the maximum or latest invoice date from the qvd file in order to use it with date arithmetic then you can get it in a variable by doing something like this in your loadscript before loading any other tables with the INV_DATE field:

TMP_INV_DATE: //this is an optimized load
Load INV_DATE FROM invoice_data.qvd (qvd);
TMP_MAXINV_DATE:
Load Max(INV_DATE) as maxinvdate; //preceding load
Load FieldValue('INV_DATE',IterNo()) as INV_DATE
AutoGenerate(1)
While not Isnull(FieldValue('INV_DATE',IterNo()));
Let var_maxinvdate = Peek('maxinvdate',-1,'TMP_MAXINV_DATE');
Drop tables TMP_INV_DATE, TMP_MAXINV_DATE; //not needed

★★★

Testing the Aggr Function Used in an Expression

2015-01-02T20:26:00.001-05:00

I’m often asked to take a look at someone's QlikView document with the explanation that “the expression doesn't work”. If it is a complex expression with multiple parts or multiple functions I suggest taking each part of the expression and testing it to make sure it is performing the expected function. When one of the parts is something simple like sum(InvoiceAmt) then most people get the idea that they can put that little part of the expression into a temporary chart column or text box and then, based on the current selections and/or chart dimension, check that the function is really showing the sum of the invoice amounts.

If the part of the expression that needs to be tested is an aggr function then you need to know how to check it because the aggr function does not return a single value like the sum function; it returns an array of one or more values. An array of multiple values will usually show up as a null in a chart expression or text expression. If the array happens to have only one value or if all of the values in the array are exactly the same then you will be able to see the result of the expression but its no guarantee that the function is working as intended.

In order to test the aggr function itself, I recommend surrounding the aggr function with a concat expression that will help you analyze the array of the values returned by aggr. For example, if you had a straight table chart with a dimension of geographic region, you might have an expression like this to calculate average sales rep invoice amount totals:
Avg( Aggr( sum(InvoiceAmt), SalesRep) )

In order to view the values returned by the aggr function, you could create a temporary column and copy and paste the aggr portion of the original expression and surround it with a concat function that might look like this:
Concat( Aggr( sum(invoiceamt), SalesRep), ' / ' )

That would create a text string showing the individual values in the array separated or delimited by ' / '
Another useful delimiter is ', ' which creates a text string showing the individual values separated by commas.

When using this technique with a chart column there might not be enough space to show all of the array values separated by the delimiter. If that happens, right-click on the chart cell you would like to examine and choose Copy to Clipboard - Cell Value (in a text box you may right-click and choose Copy to Clipboard - Text) and then paste the value into a text file or spreadsheet or other convenient place to review a long text string.

If the array of values you see using this method is what you expected then check out all of the other sections of your expression – if all of them are working as you expect then assembling them into a single expression should work in your application. I often recommend building and testing each of the parts as a development technique when building a complex expression.

One other method for examining the array of values returned by the aggr function is to use it in a temporary chart and use the aggr function as a calculated dimension. A calculated dimension is, by definition, an array of values and it may help you review the values.

_______________ ___________________ ___________________

Note to Santhosh: You can remove leading zeros from a numeric identifier like a SAP 18-digit material number in the loadscript with a line like this:
replace(ltrim(replace(MATNR,'0',' ')),' ','0') as MATNR_NO_ZEROS,

If you foresee the need to do a left join load using the original identifier then keep it as a separate field. If you would like to turn this into a user-defined function like I wrote about three years ago (search on "qlikview maven user-defined function") then do this:

Create a variable named DropZeros
Paste this into the variable: replace(ltrim(replace($1,'0',' ')),' ','0')
Now, you have something that works like a user-defined function. It could be used in your loadscript line like this:

$(DropZeros(MATNR)) as MATNR_NO_ZEROS,

★★★

Function With Flexible, Choosable Dimensions

2014-12-04T20:30:00.000-05:00

I worked on a document last week that calculated a performance indicator metric based on budget amounts compared to actual spending amounts. The chart expression for the calculation used the Aggr function and two dimensions: spending program and geographic region. Simplifying from the actual expression, it looked something like this:

Sum( Aggr( Fabs( Sum(spendqty) – Sum(budgetqty) ), SProgram, SRegion))

The business requirement had changed and I needed to provide a method so the person using the document could choose the level at which to calculate the differences and the level could involve one or two different fields and a choice of weekly or monthly time period.

I decided to use a method that would eliminate the need for if statements in the expression and also provide an easy way to add more choices in the future. First, I built an inline load of the choices in the loadscript (a data island since the field names do not appear in any other table):

CALC_LEVEL:
Load * Inline
[CALCLVL_DESC : CALCLVL_CODE
Program, Region, Weekly : SProgram, SRegion, WEEK_start_date
Program National, Weekly : SProgram, WEEK_start_date
Region, Weekly : SRegion, WEEK_start_date
Program, Region, Monthly : SProgram, SRegion, Fiscal_period445
Program National, Monthly : SProgram, Fiscal_period445
Region, Monthly : SRegion, Fiscal_period445
] (delimiter is ':');

Note that I specified a colon as the field delimiter so that I could include commas in the field values of CALCLVL_DESC.

In the document, I created a Listbox for the CALCLVL_DESC field and titled it “Please choose a calculation level:” So, the business user can see the possible choices using familiar words that make sense in a business context. The listbox was configured for Always One Selected Value. When the user makes a selection of CALCLVL_DESC it means that there is only one available value of CALCLVL_CODE which is important for making the expression work correctly.

The dimension part of the Aggr function was changed so that the expression now looks like this:

Sum( Aggr( Fabs( Sum(spendqty) – Sum(budgetqty) ), $(=CALCLVL_CODE) ))

That makes the dimension part of the Aggr function take on the specific field names listed in the CALCLVL_CODE value. It allows the user’s choice to automatically control the fields used in the dimension. Adding new choices in the future would be a matter of adding new lines to the inline load.

This is a powerful technique, it is not limited to the Aggr function or an inline load data island. It can replace an entire expression or any part of an expression based on the current selection (as you read that sentence, keep in mind how flexible and powerful the concept of selection is for QlikView).

★★★

Problem: Drag and Drop Straight Table Columns Not Working

2014-11-25T21:24:00.001-05:00

One of the other consultants at work came to me last week with a seemingly odd problem. She had a document with a straight table that originally had one dimension column. She added two more dimension columns and QlikView added the new columns on the right-hand side of the table. But, my co-worker wanted all three dimension columns to be on the left of the table which is the way we usually set up a straight table. Normally, dragging the new columns over to the left should be quick and easy… but, no matter how many times she tried, the new columns could not be dragged and stayed stubbornly on the right.

After close examination, some trial and error, and a bit of logical thinking; the source of the problem became clear. The thing that was interfering with the drag-and-drop of the dimension columns was the few columns in the chart that were not visible. In this case, there were two kinds: a hidden column, and three conditional columns that would only be visible under specific conditions.

The source of the problem suggested a solution: right-click on the straight table, select Properties and choose the Presentation tab. One by one, click on each of the columns shown on the Presentation tab and make sure the Show Column radio button is selected for all columns (not Hidden or Conditional). Then, when all columns are visible, the drag-and-drop will work normally. After rearranging the columns as desired, use the chart Properties and Presentation tab again to restore the hidden columns and conditional columns.
One of the blog comments below suggests there is another type of non-visible column that would affect a Table Box: a column referring to a field no longer available in the document. You would have to edit properties for the Table Box and remove the field that no longer exists to restore drag-and-drop capability.

★★★

Note About Dollar-sign Expansion

2014-04-24T17:23:00.000-04:00

Katrina, from the Marketing Group, came to me yesterday with a QlikView document she was working on. It had three scatter plot charts arranged side-by-side and she wanted to make the side-by-side comparison easier by making them all have a similar Y-axis. She knew she wanted something for the static max property of the charts so that each would have the same maximum value for the Y-axis. She knew the basic expression to calculate the maximum value but the charts have a cyclic or cycle-group in the dimension and she needed an expression that would work with the cycle group.

I knew that she needed a dollar sign expansion (to handle the cycle group) and an aggr function (to find the maximum value by dimension) so I scribbled an expression like this on a sheet of note paper and Katrina left to try it out:

Max( aggr( your-calculation-of-y-value, $(=getcurrentfield( your-cycle-group-name )))

An hour later, I got an email from Katrina saying that the expression wasn't working. I decided to take a closer look at her document. The cycle group dimension in the charts is named “Scatter ExErr”. It contains a space or blank in the middle of it—so when it is used in an expression it must be surrounded by square brackets like this [Scatter ExErr] (Note that double-quotes should work as well as square brackets.)

But, the expression still wasn't working correctly and it returned a null value. When I examined the field names used in the cycle group I saw that some of those also contained a space inside the field name. When you use a dollar sign expansion with a getcurrentfield function in your expression you can think of it as though the dollar-sign expansion becomes the field name before QlikView calculates the expression. Since the field name contains an embedded space then it must also be surrounded by square brackets and in this expression that means surrounding the dollar-sign expansion in square brackets. Katrina’s expression now has two pairs of square brackets and looks something like this:

Max( aggr( your-calculation-of-y-value, [$(=getcurrentfield([Scatter ExErr] )] ))

In documents that I develop, I avoid putting embedded spaces into any names that are not actually visible in the charts and I use an underscore character if it is needed to make a field or object name easier to read. If you decide to name things with names containing embedded spaces remember that you usually have to surround the name with square brackets or double quotes when the name is used in a function or expression.

★★★

Divide a Group of Dates into 2 Groups (so you can tell if the business measurement is getting better or worse)

2014-04-11T16:07:00.000-04:00

Author's Note: This is an interesting technique but I believe a simpler and more general purpose method is in a blog post from Aug 2016. Search on "QlikView Maven Is My KPI Getting Worse or Getting Better"

I did a weekend project several weeks ago where I was working with a database containing some business measurements taken each day over a long stretch of time. I wanted an easy way to visualize whether the aggregate measurements were getting better or worse over time. In order to satisfy the “easy” part of that objective, I decided to build expressions that would automatically split the available measurement dates into two groups: the earliest half of the dates and the latest half of the dates. That would allow me to create charts with a heat map feature that easily identifies areas for further investigation.

I won’t complicate this story with details of the business measurements or with details of how to create heat map colors (for more info, search for “QlikView Colormap Wizard”). We’ll call the measurement sorg_delta and do a Sum of those values over the dates.

I made a simplifying assumption that I would always have a contiguous span of dates. So, calculating the median date would give me the middle date that splits the group of dates into the two half groups that I need. Using set analysis syntax to make the sum function operate over the early half of the dates looks like this:

Sum({<MDate={'<=$(=Date(median(MDate)))'}>} sorg_delta)

But, I can’t do something a simple as that for the other half of the dates because I may be working with an odd number of days and any comparison of the data must be done for the same number of measurements otherwise it would affect a comparison of the aggregate business measurement between the two groups. So, the other half of the expression that includes the later half of the dates looks like this:

Sum({<MDate={'>$(=Date(median(MDate)+if(odd(count(distinct MDate)),1,0)))'}>} sorg_delta)

The complete expression used in my charts subtracted the sum of earlier date measurements from the sum of later date measurements. Whether that difference is a positive or negative number tells whether the business measurement is getting “better” or “worse” and the size of the difference tells us how much better or worse the measurement has become. That logic was used with a heat map feature to color bars in a graph either reddish or greenish and it helped turn a simple bar graph into a relatively simple-to-understand multivariate analysis chart.
★★★

Inline Load with Where Clause

2014-04-05T13:17:00.000-04:00

You wouldn’t ordinarily use a Where clause with an inline load… after all, if you didn’t want all of the rows why bother typing them? But, as an example, I had a project two weeks ago where an inline load with a Where clause helped solve a problem. It was a QlikView document that could load data from any one of several dozen companies. The data included a company code but not the company name or any other friendly details for me to use in the document.

I coded an inline load with a row for each of the possible companies. In the loadscript, after loading the company data, I coded an extract of the company code from the data and stored it in a variable named var_cmp_code.

The inline load, simplified here, looked something like this:

COMPANY_INFO:
Load * inline
[cmp_code, company_name, company_industry
BA, Boeing, Aerospace
CCC, Chesterfield Coal Co, Mining and Minerals
LPL, London Pastry Ltd, Stale Biscuits]
Where cmp_code = '$(var_cmp_code)';

Since each load of the document is loading data for one company, the Where clause on the inline load makes it load one row only and each field name, therefore, has only one value. That allowed me to use company_name or any of the other field names in chart titles and text boxes. For example, I used a text expression in a chart title like this: ='Future Health Care Risk by Year for ' & company_name

Remember that an inline load is like any other load except that the data is typed directly into the loadscript instead of being loaded from a separate file or database. In addition to a Where clause, you could code a While, Autogenerate, mapping table, Concatenate, Qualify, or the various types of joins available in a loadscript.

★★★

Oracle Hint in the Loadscript

2014-04-02T19:03:00.001-04:00

People who load data from an Oracle database into QlikView may sometimes want to use an Oracle hint in the SQL query code. An Oracle hint tells Oracle about how you would like it to plan and execute the query in order to get faster, more efficient execution.

The Oracle hint syntax is actually a comment embedded in the SQL query. For example, the hint to tell Oracle to use up to four parallel processors might look like this in the first line of the query:
SELECT /*+ parallel(4) */ CUSTOMER, SUM(ORD_QTY) FROM . . .

If you simply code a query like that in the loadscript you won’t get an error or a warning, the query will return data, but you won’t get the performance improvement you were hoping for either. That’s because QlikView doesn’t ordinarily pass comments through to the SQL processor. Oracle would never see your hint.

What is needed, is for you to tell QlikView not to strip out the comments before passing the query into the SQL processor. Code a line like this before your loadscript table definition:

Set StripComments = 0;

ORDER_TABLE:

SELECT /*+ parallel(4) */ CUSTOMER, SUM(ORD_QTY) FROM . . .

The StripComments variable is a system variable that controls QlikView behavior. Setting the variable to zero tells QlikView not to strip out the comments. It is a good practice to turn StripComments back on after the query with a line like this:
Set StripComments = 1;

One thing to look out for is any other comments in your code. If you have a regular loadscript comment //(a comment beginning with two slashes) mixed in with your SQL code then it will cause a SQL error when it gets passed in to the SQL processor.

★★★

Maven's Sliding Window

2013-06-16T15:18:00.000-04:00

Expanding on the example from last time about loading unstructured text data (to read about it, search on QlikView Maven Loading an Unstructured Text File); here’s one way to examine the data. I call it Maven’s Sliding Window. My requirement was to design a method that would allow a straight table to review the rows of text even though there may be a million rows or more. It had to work even though there may be a special selection applied (for example, a selection of all rows containing the word “program”). The straight table had to show just a very small subset of the rows at any one time to avoid the memory issue that would occur if the straight table tried to show many thousands of rows.

[If there was no selection already applied then this task is easy and no special object is required—just select a contiguous group of 20 rows and you can page though the data 20 rows at a time with the Page Up or Page Down keys on your keyboard.]

First, create a variable named vFinger with a current value of 1 and a constraint so that it contains positive integers. Now, create a Slider Object to update the value of vFinger. In the General tab set Mode to Single Value, set Min Value to =min(recno), set Max Value to =max(recno), click the static step box and put 1 in the static step box. I made the Caption tab title say “Window Pointer”. Make the slider have a vertical orientation to help make the usage a little more intuitive.

Now, set up a straight table object. Set up a calculated dimension named recno with an expression like this:
=If(aggr(rank(-if(recno>$(vFinger),recno)),recno)<=20,recno)

And set up one expression defined simply as logtext (remember that we are still working with the data loaded in the example from 6May2013)
On the Sort tab specify that the dimension recno is sorted ascending numeric.
In my simple example, I made the object title a text expression like this:
='Sliding Window Text from $(File_Pathname):'
so that the title uses the variable containing the file pathname to show where the data came from.

That gives us a straight table that is showing only the records surrounding the record number specified by the vFinger variable. Using the slider that controls vFinger controls which section of text appears in the straight table. You could make the “window” bigger by changing the numbers in the calculated dimension.

Add a Page Down button that updates the value of vFinger to move the window “down”. The metaphor of a sliding window means that sliding down goes toward larger recno values and sliding the window “up” goes toward the lower recno values. The button action would be Set Variable to update the vFinger variable. The action Value would be =max(If(aggr(rank(-if(recno>$(vFinger),recno)),recno)<=20,recno))
That adds to the vFinger variable while staying within the recno values that are selected.
A similar Page Up button would contain a Set Variable action to update vFinger with a value like this =min(If(aggr(rank(if(recno<$(vFinger),recno)),recno)<=20,recno))

If I’m working with long text records then I often set up slider objects to control which text columns appear in the straight table. For example, you could set up a slider with horizontal orientation to control the value of a variable named startcol and a slider to control a variable named endcol. Then, in the expression, instead of simply specifying logtext, code this text expression: Mid(logtext,startcol,(endcol-startcol)+1)
That will allow you to control which text columns appear in the straight table and you can slide that part of the “window” left and right.

Note that you can save the document and later when you re-open it, the sliding window will still show the same group of records since it is driven by variable values that are saved along with the document.

My example here had a record number field to use as a dimension for the straight table. But, any data that forms a series would work just as well; for example, dates, time of day or timestamps, degree/minutes/seconds of longitude, hours of operation of a machine, measurements from the Hadron Collider, etc.
The next blog post will discuss using the power of QlikView selection and set analysis to help us examine the text data.

★★★

Loading an Unstructured Text File

2013-05-06T09:51:00.000-04:00

This is the start of a series of postings about dealing with unstructured text data. I've used QlikView to help analyze message log files and error files and even program source code – there are some things QlikView can do that Notepad style text editors cannot.
First, we need to load the data. It isn’t always a completely unstructured file like my example, often the text is one or more fields inside a structured file or database. There are various ways to load a text file; this is what I usually put into the loadscript:
TEXTFILE:
LOAD
RecNo() as recno,
@1 as rectext
FROM
[$(File_Pathname)]
(txt, no labels, delimiter is \x7, no quotes);

That code will load each record from the text file into the rectext field. I use a variable for the file pathname because I usually use a loadscript like this with a document where the user can supply a filename through an inputbox. Alternatively, you could add browse-for-file functionality like I wrote about in March 2009 (search on QlikView Maven Browse-for-File Macro Button). Because the data is unstructured and the document objects are meant to be used with unstructured data, the document works well as a general-purpose tool that can be used with many types of data in varying situations.

I defined the file as though it is delimited and made the specified delimiter an uncommon character I don’t expect to find in the data.

The record number, loaded as recno, from the input file is a useful piece of data and we’ll be using it in some of the example objects over the next few postings.

★★★