Loading a 1% Random Sample

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

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

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.

  ★★★