Count of Rows in a Chart

Once in a while someone asks me if there's a way to count the rows in a chart and put the number of rows in the chart title. I usually respond by asking, "why would anyone want to do that?" Usually, an expression that counts the distinct values of the fields used as the chart dimension is close enough. If you want a method that actually counts the rows in a straight table chart, here's something I have used that counts the rows and puts the count into a variable named chart_row_count. It will need to be modified for your environment and tested. (***make sure you also see the suggestion near the end)

Create a macro in the module code that looks like this:

Sub Count_Rows

'-------------

'/* Put your chart object id in the next line */

Set CHRT=ActiveDocument.GetSheetObject("CH70")

On error resume next

'/* in the next line, put an upper limit on the */

'/* loop bigger than your expected chart row count */

For i=1 to 500

ccontent=CHRT.GetCell(i,0).text

If Err.Number <> 0 then

Err.Clear

On error goto 0

ActiveDocument.Variables("chart_row_count").SetContent i-1, True

Exit For

End If

Next

On error goto 0

Set CHRT=nothing

End sub

The count in the variable includes the total line if there is one. You can use the variable value in a text expression in your chart title. One thing you must do that will vary from document to document depending on your requirements-- you must figure out how to trigger the macro when the number of rows in the chart might have changed. Triggering when a selection changes is easy to do. Triggering when a cycle group is changed will be harder. For testing, use a button to trigger the macro.

= = = = = = = = = = = = = = = = = = = = = = = = = = = = =

*** nrbhattad suggests this much simpler macro for finding row count:
set chart=ActiveDocument.GetSheetObject("CH01")

ActiveDocument.Variables("chart_row_count").SetContent chart.GetNoOfRows, true

.

Select Top 4 Values On Open

I was working on a document that contained a lot of data and the performance when the document was opened or when the user moved from tab to tab was a little slow. On my own laptop when I save documents like that I usually make a selection of a small amount of data so that later when I open the document again it will open quickly. I wanted to do something like that for the users -- some kind of selection that would happen automatically when the document is opened, but it had to be a selection of data that would be useful and easy for the users to understand.

The data in the document was organized by a date field named POSTDATE so I decided that I would like the document to automatically select the most recent four POSTDATE values when it was opened. Most users look in the document for the most recent data and anyone who wanted to review older data could easily add older dates to the selection through the multibox.

The method I used is to create a trigger that would fire when the document is opened. The trigger would do a Pareto Select. Now, I'm not saying that this is the best way to accomplish the selection and it certainly is not the only way, but it was easy and didn't involve writing macro code.

Pareto Select is used to select a percentage of values in a field based on ranking of a value or expression. It is commonly used select things like the top 20% best selling products. In this case I wanted to use it to select not a fixed percentage but the four most recent POSTDATE date values. This is what I did:

From the menu choose Settings->Document Properties and choose the Triggers tab.
In the document event triggers click the Add Action button for the OnOpen event.
Click Add Action and in the Add Action dialog choose Action Type=Selection and Action=Pareto Select then click OK.
In the Actions dialog type POSTDATE into both the Field and Selection boxes. POSTDATE is not only the field I want to select but also the expression value I want ranked.
Then, in the Percentage box enter =(4/COUNT(DISTINCT POSTDATE))*100
And click OK. Save the document and the next time it is opened it will automatically select the four most recent POSTDATE values.

A similar method could be used to do automatic selection of any number of top values of any field. Pareto Select does its selection based on the current Possible values. If you wanted to ignore the current selection and select a percentage of all values then you should add an extra action to the trigger to clear the field before making the Pareto Selection.

Testing trigger actions is most easily done by defining the same actions for a button and then you can test the action by clicking the button. When you're done testing, remove the button and make sure the actions are associated with the proper event.

= = = = = = = = = = = = = = = = = = = = = = = = = =
Author's Note 6Dec2011:
This method didn't work the way I wanted when the document already included selections on the POSTDATE field. I couldn't find any good way to deal with that using the available trigger actions. I eventually replaced the Pareto Select action with a macro action that selects the four most recent POSTDATE values. The macro code looks like this:
sub Top_4_POSTDATE
set fd=ActiveDocument.GetField("POSTDATE")
fd.Clear
fd.TopSelect "=if(aggr({1} rank(POSTDATE),POSTDATE)<=4,POSTDATE)",4
set fd=nothing
end sub

* * *

Replace IF in Expression with Set Analysis

We often come across chart expressions that look like this:

=sum(if(SLSTYPE='DOMESTIC',SLSTOT))

It is easy to understand and if your document is a normal size and working well then leave the expression alone. But, if the document is large and you are looking for ways to speed up chart performance then consider changing the expression by replacing the "if" in the expression with the much faster set analysis syntax. Remember that set analysis is related to the concept of QlikView selections. Set analysis syntax works by specifying temporary selections within the function. So, if we wanted to consider set analysis for our example expression we should imagine whether the expression would work ok if we removed the "if" from the expression and, instead, made a selection on the SLSTYPE field to select the value DOMESTIC. If the expression would work ok in that situation then we can change the expression to use set analysis like this:

=sum({$<SLSTYPE ={'DOMESTIC'}>} SLSTOT)

We couldn't do that if the SLSTYPE field wasn't associated with SLSTOT because selecting the value DOMESTIC would have no effect on the expression. For example, if SLSTYPE had been defined in the loadscript as a Data Island unrelated to any other data then the expression wouldn't work correctly not matter how you coded the set analysis syntax within the sum function.

Another important consideration is whether or not the report is designed for users to make their own selections on the SLSTYPE field. If users are making SLSTYPE selections then they may not like an expression that overrides their selection within the expression. If the document allows user selection on the SLSTYPE field then we should define a separate field in the loadscript that will contain the exact same values as SLSTYPE and be part of the same QlikView table and same rows. Maybe call it SLSTYPE_SA and then we can code the set analysis part of our expression using the SLSTYPE_SA field and the expression will work correctly while still respecting all other user selections.

* * *

Increasing Likelyhood of Duplicate Values While Loading Data

I was working on a QlikView document last week that loaded several kinds of sales forecast data and also actual sales for a chemical manufacturer. The forecast data came from a statistical forecasting application. The charts on the document were simple and showed the forecast data rounded to integer numbers of tons.

The document contained a lot of data and the performance was a little slow when making selections so I looked for ways to improve performance. Examining the forecast data, I saw that although the quantities in the database were expressed in tons almost every quantity included a decimal fraction. Now, QlikView is very good at compressing data and especially good at compressing data with duplicate values since it stores each unique value for a field only once. This forecast data did not contain a lot of duplicates because of the decimal fractions. I saw that if I rounded the values as they were loaded then values like 1.001, 1.00032, 1.2, etc. would all be loaded as 1. After changing the loadscript to round values to integers as the data was loaded the report file size on disk was reduced by 40% and had a corresponding reduction in memory required. The charts on the report were not affected all.

This technique would not be useful in all situations. It would cause a lot of trouble if you rounded data values on some accounting documents! But, sometimes, altering data slightly as it is loaded to increase the likelyhood of duplicate values can save memory for a QlikView document. Besides rounding (not necessarily to integers, you can round to a specific number of decimal places), you can also increase the likelyhood of duplicate values by:

• Truncating text data - keeping only the first ten characters, for example.




• Trimming text data to remove any leading or trailing blanks.




• Converting to all capital letters.
  



• Converting non-abbreviated words to standard abbreviations, like state abbreviations in address data.




• Removing the time stamp portion from date data




• Converting date data to beginning-of-week or beginning of month dates.




• Converting date data to month abbreviations if that is the level of data used in the document charts.

* * *

Using Oracle Configured for Different Countries

We load much of the data in our QlikView documents from Oracle databases. A common query technique uses the Oracle Next_Day function to aggregate data into weekly quantities. For example,

Select Next_Day(INC_DATE,2)-7, Count(distinct INCIDENT_NUM) From INC_LOG group by NextDay(INC_DATE,2)-7;

will gather a count of security incidents by week. The Next_Day function in the example should return a Monday date. But, sometimes, when the Oracle database is configured for a European business then the way Oracle numbers days of the week will be different from a database in the U.S. A European database may give Tuesday dates for that example query.

In order to make the SQL commands in a loadscript behave like they do in the U.S. I add a single SQL line to the loadscript following the ODBC Connect string that modifies the Oracle NLS setting temporarily while data is loaded into the document. The line I add after the ODBC connect string is:

SQL ALTER SESSION SET NLS_TERRITORY='America';

Does anyone have a different way of dealing with this issue?

* * *

Looking Inside a Bookmark Without Opening It

This blog post comes from a question asked on the QlikCommunity forum. The question was how do you look at the selections inside a bookmark without actually selecting and activating the bookmark?

I had never thought about how you might do that. It is not difficult to list out the possible values for a single field based on a bookmark whether the bookmark has been opened or not. For example, for a bookmark named Feb_Sales and a field named ITEM you can put a text expression like this into a text object or chart title:

=concat({Feb_Sales} distinct ITEM, ', ')
That will give you a list of the ITEM values separated by commas. Note that it is a list of Possible values, it will only be the Selected values if the bookmark includes a selection on that particular field.

This technique can be used to give the user a peek into the selected or possible field values in the bookmarks; perhaps helping a user decide which bookmark should be used.

You can also apply the selection to the ITEM field without opening the Feb_Sales bookmark. Go to a listbox or multibox entry for ITEM and type this:

=count({Feb_Sales} 1 )

and that will select the ITEM values (if none of the ITEM values exists in your current document then it will appear that all values have been selected).

If there is a field normally associated with ITEM then you can select that. For example, if the field Product_Category is normally associated with ITEM then go to a listbox or multibox entry for Product_Category and type that same bit of text and it will automatically select the Product_Category values associated with the Feb_Sales bookmark without opening the bookmark!

* * *

A Chart Expression That Works Differently for Totals Row

I built a QlikView document last week with a straight table chart where I needed to have some of the expressions work differently on the totals row than on the regular rows. One of the dimensions on the chart was date -- but the totals row needed to aggregate data for the most recent date only. I wrote an expression that takes advantage of the fact that the RowNo function returns null for the totals row. So, leaving out the actual calculation part, the expression looked something like this:
if(IsNull(RowNo()),
/* do the totals row calculation */ ,
/* else, do the regular calculation */)

A similar idea can be used if you want to change the color or bold the totals row. For example, you could use an expression like this for background color:
if(IsNull(RowNo()),yellow())

and make the totals row look like it is emphasized with a highlighter color.

Important note: If you use the RowNo() function in your column expression you will not be able to re-sort the chart by double-clicking on a column title. If that is a problem for your application then use if(Dimensionality()=0,... as a substitute.

* * *

Duplicate Rows in a QlikView Table

You may someday have a situation where you suspect that you have some duplicate rows in one of the tables within your QlikView document. It can be difficult to verify if this is true with a chart or tablebox -- you will only see a single row in the chart or tablebox even though there are duplicate rows in the table. The duplicates, though, often cause trouble with expressions. You may see an expression value that is two or three times larger than expected; this is often a symptom of duplicate rows in one of the tables.

To help identify the problem, add a row number field to each row in the QlikView table. That will give you at least one field that is unique on each of the duplicate rows. You can do this without reloading the entire document. Here's an example-- First, save a backup copy of the report that you are working on. Assume that the table to which you want to add a row number is named REDFLIGHTS. Add these rows to the top of your loadscript:

REDFLIGHTS_X:
Add Load RowNo() as REDFLIGHTS_RowNo, * Resident REDFLIGHTS;
Drop Table REDFLIGHTS;
Rename Table REDFLIGHTS_X to REDFLIGHTS;
Exit Script;

Then, from the menu, select File->Partial Reload This will run the five lines you added to your loadscript and it will build a new table inside the document that is identical to your original table but with a rownumber field, REDFLIGHTS_RowNo, added to the table. Now, in a tablebox that includes the rownumber field or in a chart using the key fields as dimensions and using a count(REDFLIGHTS_RowNo) expression you will be able to identify the duplicate rows in the table. At that point you will have to figure out how to eliminate or handle the duplicate rows when data is loaded. Remember to remove the five lines from the top of your loadscript before you next try to reload data.

* * *

Loading Multiple Files With Varying Fields

If you ever have a need to load multiple files into a single QlikView table and the files have similar but not identical fields then you might try the solution below. I’ve used this method where the files are historical data created at varying times and although the files have most fields in common, some of the fields have extra fields. For example, in a set of sales data files, the files for exported products might have extra fields for export license data.

Here is an example of the code from the loadscript. It loads a set of QVD files. It loads the first file normally, then loads all subsequent files using the Concatenate keyword. This allows the data, including the extra fields, to be loaded into the same QlikView table, SALES_TABLE.

set filectr=0;
for each sfile in filelist (e:\sales_history\QVD_SALES_MONTH*.qvd)
  If filectr=0 then
   SALES_TABLE:
   Load * from $(sfile) (qvd);
  Else
   SALES_TABLE:
   Concatenate Load * from $(sfile) (qvd);
  EndIf
  Let filectr=$(filectr)+1;
next sfile

★ ★ ★

Export All Data From All QV Tables to Text Files

Last week one of our clients asked me to provide a method that they could use to export all of the data from within a QlikView document. They had hundreds of archived documents and they wanted to be able to export the data and use it in another application.

“Sure, No problem. I’ll email you some instructions.” I sent them instructions for exporting all of the data from all QlikView tables within a document into tab-delimited text files. This is what I sent to them:

1. Create a new folder on your laptop or server. The computer where you do this must have QlikView installed.

2. Copy the QlikView .qvw document file into your folder. This will make sure you don't accidently update the original document.

3. Double-click on the .qvw file to start up QlikView.

4. From the menu, select File->Edit Script. This opens up the Loadscript Edit Window.

5. At the very top of the loadscript, before the first line, copy and paste in these lines:

/* Export all QlikView tables to tab-delimited files */
if IsPartialReload() then
for ix= 0 to 100
let tn=trim(tablename($(ix)));
if len('$(tn)')<1 then
exit script;
end if
Store "$(tn)" into "EXPORTED_QV_$(tn).TXT" (txt, delimiter is "\t");
next ix
end if
exit script;
//
6. Close the Loadscript Edit Window by clicking the OK button in the lower right corner of the window.

7. From the menu, select File->Partial Reload

8. You don't need to save the document, but if you do save it, do not copy it back to the original location.

If everything works correctly, each of the QlikView tables will be written out as tab-delimited text files into the folder. For example, if the QlikView document contains a QlikView table named HISTCUST then it would write out a text file named, EXPORTED_QV_HISTCUST.txt. The first record will contain the field names.

Note the syntax for specifying the delimiter character. If your Store command simply includes (txt) without specifying the delimiter you will get comma-delimited text files. Use the file specification, (qvd), to get QlikView QVD files.

I would've preferred a more elegant piece of code using a Do While loop but I had trouble getting the test to run correctly and I had limited time. The code above should work fine for documents with under 100 QlikView tables.

* * *

Variable That Acts Like a User-Defined Function

QlikView document variables are really just text strings with a name. In various places in the document where expressions are used you can include a variable name and QlikView will replace the name with the text string that the variable contains. It's a simple idea that can be used in many ways.

One variation on the way to use a variable is to include an argument with the variable where the argument value is provided in the larger expression where the variable is used. The technique is useful for number, currency, or date formatting. Here is an example:

Imagine that you have a document that contains some customer data including nine-digit telephone numbers and you want to show the telephone numbers formatted in U.S. style like this (123) 456-7899.

Create a variable named telno and enter this as the variable value:

'('&left($1,3)&') '&mid($1,4,3)&'-'&right($1,4)

When we use this variable in an expression, the $1 will be replaced by the argument value. It is a text replacement operation but, because it takes place within a larger expression, the effect is like a user-defined function. In our example, the customer telephone field is named CUST_PHONE. So, we can create a chart expression like this:

$(telno(CUST_PHONE))

That will show the customer telephone number in the format we need. The real value of the technique is apparent when the expression is long or complex or uses advanced techniques like user-selected formatting.

For two arguments you can use $2 and it will be replaced by the second argument. The third argument will replace $3, and so on. For example, if you had a telephone extension number as a data field then it could be used in our example chart expression like this:

$(telno(CUST_PHONE, CUST_EXT))

and then the variable value could include $2 which would be replaced by CUST_EXT. It might look like this:

'('&Left($1,3)&') '&mid($1,4,3)&'-'&right($1,4)&' ext '&$2

This kind of variable can also be used in the loadscript. The next time you are wishing for a user-defined function you can use in the loadscript, see if this concept will do the trick.

* * *

Loading Scientific or E-Notation

I was loading data into QlikView from a large, comma-delimited text file last week. I found that some of the numeric quantities were expressed in scientific notation or E-notation. Apparently, the person who had prepared the data used a reporting tool that converted some of the very large and very small numbers into scientific notation. The scientific notation numbers contain two numbers separated by an “E” character. The notation means to take the number on the left of the value and multiply by ten raised to the power of the number on the right of the value. For example, the E-notation value of 6.02E23 means that the numeric value is 6.02 times 10 to the 23rd power. It isn’t difficult to deal with in a loadscript but this example might save you some work someday. The loadscript looked something like this:

PROMO_HIST:

LOAD MATERIAL,

WHSE,

If(index(upper(FQTY),'E')>0,

subfield(upper(FQTY),'E',1)*pow(10,subfield(upper(FQTY),'E',2)),

FQTY) as FQTY

FROM D:\Tim\Promo.csv (ansi,txt,delimiter is ',',embedded labels,msq);

That IF statement in the loadscript converted any e-notation values in the data into numbers and for regular numeric values loaded them as usual.

Later in the day, I needed to load the same data into an Oracle table using Sql Loader. Interesting that the idea is the same but the syntax is different. This is what the entry in the SqlLdr control file looked like:

FQTY "DECODE(INSTR(UPPER(:FQTY),'E'),0,:FQTY, SUBSTR(:FQTY,1,INSTR(UPPER(:FQTY),'E')-1) * POWER(10,SUBSTR(:FQTY,INSTR(UPPER(:FQTY),'E'))+1))",

★ ★ ★