I'm still investigating why this happened, but I recently discovered a case of Document Trigger corruption in one of my QlikView documents. What's most worrisome is that it appears to have occurred several times over a 6 month period which indicates 1) it should be reproducible and 2) it can happen again.
I employ the QlikView-subversion integration for revision control of QlikView documents. This integration causes QlikView to generate XML representations of its internal objects (which, by the way is a great tool for debugging QlikView docs).
Now I'm not sure if something happened internal to QlikView that caused the XML to be corrupted or if subversion did something funky to the XML that caused QlikView to get corrupted. Anyway, what I'm seeing is that in several revisions of the AllProperties.xml file, which is where your document triggers are defined, the OnSelect and OnChange triggers are "reassigned" from the fields that they were defined for, to completely different fields.
My current solution for the problem is to delete all of my OnChange and OnSelect triggers and then redefine them. If you're ballsy you can probably edit the AllProperties.xml directly to accomplish this.
Lesson learned: Review your document triggers regularly.
2) OFF doesn't always mean OFF.
Define a sheet, open it's Properties, select the custom background color check box, define a color and then save. Go back in and un-select the custom background color check box and save again. Guess what? The custom color is still displayed.
This isn't the only instance where a QlikView option continues to apply even after it's been disabled. For example, you can enable the borders option for an object (like a text object), select to turn shadows on for the object and then disable the borders option. Guess what? The shadows still appear even though the borders do not.
Lesson learned: Don't assume un-selected means OFF.
3) Less uniqueness = better performance.
The fewer unique field values you have in your model, the better the performance of your QlikView document. For example, if you have a large number of records in your model and one of the columns you expose is a timestamp with millisecond granularity, if your user doesn't need that kind of granularity, then try rounding the milliseconds off of the timestamp when you load it. This will reduce the number of unique timestamp values in your model and should improve responsiveness. At the very least, if you expose the timestamp values in a list box, there will be fewer values to scroll through.
4) BLANKS IN SHEET IDS CAUSE SHEETS TO DISAPPEAR IN CHROME AND SAFARI!
5) Document's OnSelect and OnChange Triggers
OnSelect triggers fire when one or more selections of the specified field are made. Selections do not fire the OnChange triggers. Clearing a fields selections via the Clear menu button will cause the OnChange triggers fire. Clearing fields by a button's Clear All action does not fire OnChange triggers.
5b) Sheet OnActivate Triggers
Activating a sheet by clicking on it's tab will cause that sheet's OnActivate triggers to fire, however, they do not fire if you activate a sheet using an object's Activate Sheet action.
6) QlikView Ajax Javascript API Documentation
QlikView API documentation is at times incomplete and at times inaccurate; sometimes both. Do not trust it verbatim. Use experimentation/observation (or my notes) to determine actual parameters and expected behaviors.
7) Set Analysis expression can override model selections
By default the value of an expression is affected by the current model selections. However, when you use Set Analysis, you may unintentionally override model selections. For example, let's say you have a pet store and want to count your cats. You might use an expression like sum({<type={cat}>}count).
Now suppose you provide a list box of animal types and someone selected the type "dog", one might think that the above expression would return 0 since selecting "dog" effectively hides "cat" in your model. This is not true however, the specification of "type={cat}" in the set analysis will override the type selection in the model and continue to display the cat count. Situations like this can confuse your user who is expecting the displayed cat count to change because of the model selection.
8) Extensive use of functions that call into the operating system can cause script exceptions
Certain functions like the Today() and OSUser() functions call into the server's operating system via multiple software libraries. I have witnessed on several occasions where having too many of these function calls will cause the software libraries to throw an exception and result in script errors.
You can mitigate this by minimizing the number of times you call these functions. For example, Today() will generally return the same value every time you call it.. so call it once, assign it's value to a variable and then reference the variable in your document.
8a) Be very careful calling the time functions that poll!!!
Just because you define a variable to hold the value of a polling function doesn't mean that the polling doesn't happen. For example:
vNow defined as =Now(1)
Now(1) sets up a one second poll that will update your vNow every second and you know what happens when the value of a variable changes, right? Very inefficient.
9) Watch those AGGRs
I was recently asked to review the performance of a Straight Table object that was defined by one of QlikView consultants; as defined, it would take about 2 minutes for this table to render. The first thing that I noticed was that this particular table used AGGR functions within it's expressions. The second thing I noticed was that they were AGGRing over dimensions that were already defined for the table. In other words, they were AGGRing data that was already aggregated "naturally" by the table. So I removed the AGGRs and lo and behold, the table now renders the same data in 2 seconds instead of 2 minutes.
10) Map inline vs Add new dimension
Let's say you have a pet store and a fact table that identifies each of the animals in stock. You think it would be nice if you could offer a description of each animal based on type. You could do this using a mapping table (eg animal_type_to_desc_map) and add:
load *, ApplyMap('animal_type_to_desc_map', animal_type) as animal_description;
to your fact table load. If your descriptions are verbose, however, and your inventory vast, this can cause "model bloat" and greatly increase the memory footprint of your document.
A more memory-efficient solution would be to simply convert the mapping table to a non-mapping "dimensional" table and let animal_type be a common key between your fact table and the description table.
11) OnChange triggers and variables.
Even though QlikView document settings allow you to specify triggers for variable changes, the last I heard, these were disabled; i.e. useless. So don't use them and don't count on them working.
12) Schema changes and synthetic keys.
Let's say you have 12 months of a historical fact table (containing 100 fields) loaded into a qvw. Then someone changes the fact table's database schema to add a 101st field and you read this month's fact table into your qvw to merge it with the historical fact data. Because the two tables have slightly different schemas, QlikView is going to try to generate a load of synthetic keys using all of the matching fields; an operation that will probably end with you aborting the reload after about 15 minutes of waiting. This scenario can be avoided by following the suggestions under Versioning on the Tips page.
13) Layout Layer values
Ever wonder what the "Normal/Top" Layout Layer values correspond to in relation to the Custom values? Here you go:
- Bottom = -1
- Normal = 0
- Top = 1
- Custom = whatever you want to specify;
If you specify a Custom value < -1, the object will be positioned below Bottom objects,
If you specify a Custom value > 1, the object will be positioned above Top objects.
Trivia: If you specify a Custom value of -1, 0 or 1, and then close and reopen the object's properties dialog you will see the that setting has switched to Bottom, Normal or Top respectively.
17) ConvertToLocalTime() assumes GMT input
If you're going to do any localized timezone support you'll probably have to use the ConvertToLocalTime() function. What isn't clear in the documentation for this function is that it expects GMT-normalized time. This means that if you're data isn't GMT-normalized to begin with, you'll have to convert it to GMT first before you can pass it into ConvertToLocalTime() to localize it.
18) Document-level triggers are Alternate State agnostic.
In other words, if you define a Document-level OnSelect trigger for a field and then create two list boxes that reference that field, each with a different alternate state, selecting a field value in either list box will fire the OnSelect trigger.
19) Copy text from Text Objects.
Prior to QlikView 11.2, users could copy text from Text Objects. As of 11.2 that is no longer possible.
20) Dual-types as keys
If you want to use a dual-type value like a timestamp as key between two tables, you're better off converting it to a single-type value first. QlikView has some (performance?) difficulty using dual-typed values as table keys.
21) Set Analysis (Alphanumeric) Values
When QlikView tries to interpret values you specify in a Set Analysis expression, if the first character it encounters is a digit, it will try to interpret the value as a number. If that values happens to be alphanumeric, this can cause issues for the interpreter. Avoid this by enclosing alphanumeric values in quotes. See below:
count({<type={1}>} cars) - works
count({<type={A}>}cars) - works
count({<type={1A}>}cars) - problem!
count({<type={A1}>}cars) - works
count({<type={'1'}>} cars) - works
count({<type={'A'}>}cars) - works
count({<type={'1A'}>}cars) - works
count({<type={'A1'}>}cars) - works
22) Bookmark Issues
What can I say? Bookmarks seem to have many issues. Here are a couple I've encountered.
22a) Create this table:
Now create a button or text object that has a Select in Field action that selects Apple Pie in the field food_type. Click it and you'll notice that Apple Pie gets selected in your list box as expected. Now, while Apple Pie is still selected, create a bookmark. Once you've created the bookmark, clear all selections. Now load the bookmark you just created and what do you see? Are Apple, Apple Pie and Pie all selected? That's what I see! The only solution I've found to this is to remove spaces in the table values. Oddly enough this doesn't happen if you make the selection manually; ie, not with a Select in Field action.
22b) Make some selections in one of your QlikView documents, then create a bookmark of these selections. Next create a list box for one of the dimensions/fields in your model, select a value in the list box and set the list box properties to Always One Selected. Now reload your bookmark. Did it restore your model to the expected state? I've seen instances where the bookmark selections are not correctly (re-)applied because, I presume, the document doesn't know what to do about the Always One Selected list box since it's not part of the bookmark but requires a selection. Just something to look out for.
22c) Bookmarks, at least in my environment, are notoriously prone to breaking whenever we update our model with a new field or dimension. Again, just something to keep an eye out for.
23) A Multibox display vs selection.
A Multibox with an expression like if (Category='Electric', Product) will display a list of Product values, but when a selection is made, both Category and Product are selected in the model (and selection triggers fire).
14) Exporting Tables of Numbers to Excel
When you export a table to Excel, QlikView converts values using the formats specified under the table's Number tab. Unless otherwise specified, the default format for numeric values is string. Therefore, if you want your table's numbers to be exported as numbers, you have to specify a numeric format on the Numbers tab, otherwise they will be exported as strings.
15) Reusing List Boxes for Multiple Dimensions.
It's a pretty common practice to create a list box displaying the names of some of your model's dimensions and a second list box which, when a dimension in the first list box is selected, displays the values associated with that dimension.
While this is a nice way to reduce the number of objects you need to maintain, it's important to know that for dimensions with a lot (millions?) of values, displaying them in a reusable list box will result in a major performance hit for your UI.
You're much better off displaying mega-value dimensions in their own list boxes and save the reusable list boxes for dimensions that only have hundreds of values.
16) Sorting takes precedence over Dimensional Limiting
Let's say you have a million record data set, some of which you want to display in a straight table. You might set the dimensional limit on the table to say 10K records because, seriously, who's going to look at a million records anyway. Then you set some sorting criteria on the data as well. I can almost guarantee there will come a time when someone is going to complain that there's some data that they know exists, but it's not showing up in the table.
It's pretty obvious what's happening; the data they're looking for got sorted into the set of records that got omitted by the dimensional limit. Happens all the time. Mitigate this by insuring your client can (easily) filter the data set down to something that fits within the dimensional limit. You may have no choice but to increase the limit to accommodate this.
16) Sorting takes precedence over Dimensional Limiting
Let's say you have a million record data set, some of which you want to display in a straight table. You might set the dimensional limit on the table to say 10K records because, seriously, who's going to look at a million records anyway. Then you set some sorting criteria on the data as well. I can almost guarantee there will come a time when someone is going to complain that there's some data that they know exists, but it's not showing up in the table.
It's pretty obvious what's happening; the data they're looking for got sorted into the set of records that got omitted by the dimensional limit. Happens all the time. Mitigate this by insuring your client can (easily) filter the data set down to something that fits within the dimensional limit. You may have no choice but to increase the limit to accommodate this.
17) ConvertToLocalTime() assumes GMT input
If you're going to do any localized timezone support you'll probably have to use the ConvertToLocalTime() function. What isn't clear in the documentation for this function is that it expects GMT-normalized time. This means that if you're data isn't GMT-normalized to begin with, you'll have to convert it to GMT first before you can pass it into ConvertToLocalTime() to localize it.
18) Document-level triggers are Alternate State agnostic.
In other words, if you define a Document-level OnSelect trigger for a field and then create two list boxes that reference that field, each with a different alternate state, selecting a field value in either list box will fire the OnSelect trigger.
19) Copy text from Text Objects.
Prior to QlikView 11.2, users could copy text from Text Objects. As of 11.2 that is no longer possible.
20) Dual-types as keys
If you want to use a dual-type value like a timestamp as key between two tables, you're better off converting it to a single-type value first. QlikView has some (performance?) difficulty using dual-typed values as table keys.
21) Set Analysis (Alphanumeric) Values
When QlikView tries to interpret values you specify in a Set Analysis expression, if the first character it encounters is a digit, it will try to interpret the value as a number. If that values happens to be alphanumeric, this can cause issues for the interpreter. Avoid this by enclosing alphanumeric values in quotes. See below:
count({<type={1}>} cars) - works
count({<type={A}>}cars) - works
count({<type={1A}>}cars) - problem!
count({<type={A1}>}cars) - works
count({<type={'1'}>} cars) - works
count({<type={'A'}>}cars) - works
count({<type={'1A'}>}cars) - works
count({<type={'A1'}>}cars) - works
What can I say? Bookmarks seem to have many issues. Here are a couple I've encountered.
22a) Create this table:
Food:Create a list box that displays the values of food_type.
Load * inline [
food_type
Apple
Banana
Apple Pie
Pie
];
Now create a button or text object that has a Select in Field action that selects Apple Pie in the field food_type. Click it and you'll notice that Apple Pie gets selected in your list box as expected. Now, while Apple Pie is still selected, create a bookmark. Once you've created the bookmark, clear all selections. Now load the bookmark you just created and what do you see? Are Apple, Apple Pie and Pie all selected? That's what I see! The only solution I've found to this is to remove spaces in the table values. Oddly enough this doesn't happen if you make the selection manually; ie, not with a Select in Field action.
22b) Make some selections in one of your QlikView documents, then create a bookmark of these selections. Next create a list box for one of the dimensions/fields in your model, select a value in the list box and set the list box properties to Always One Selected. Now reload your bookmark. Did it restore your model to the expected state? I've seen instances where the bookmark selections are not correctly (re-)applied because, I presume, the document doesn't know what to do about the Always One Selected list box since it's not part of the bookmark but requires a selection. Just something to look out for.
22c) Bookmarks, at least in my environment, are notoriously prone to breaking whenever we update our model with a new field or dimension. Again, just something to keep an eye out for.
23) A Multibox display vs selection.
A Multibox with an expression like if (Category='Electric', Product) will display a list of Product values, but when a selection is made, both Category and Product are selected in the model (and selection triggers fire).
No comments:
Post a Comment