Table Box

The table box is a sheet object that shows several fields simultaneously. The content of every row is logically connected. The columns may be fetched from different internal tables, letting the user create tables from any possible combination of fields.

Right-click on the table box to display the Object menu.

Using the Table Box

Sorting

It is possible to sort the table box by any column: simply right-click on the column and choose Sort from the context menu. Double-click the caption of the column to sort by. Choosing Sort from the context menu or double-clicking the same column caption will invert the sort order.

Changing the Column Order

The order of the columns can be changed by means of the drag-and-drop method. Point at the column title, then keep the mouse button depressed while dragging the column to its new position. Disable this feature by deselecting the Allow Drag and Drop check box in the Table Box Properties: Presentation page.

Object Menu

The table box Object menu has two different versions. If the caption area is clicked the float menu will contain commands that are applicable to the whole box, if an individual field is under the pointer the menu will be extended to include commands that apply to that field. The combined commands of the menus are:

• Properties...: Opens the Table Box Properties dialog where a number of parameters can be set.
• Notes: Allows creating and sharing notes about the current object.
• Fit Columns to Data: Adjusts the width of all columns in the tables to the widest data in each column. The header is included in the calculation.
• Equal Column Width: Sets the column width in the table equal to that of the column on which you clicked.
• Sort: Sorts the records by the field on which you click.
• Custom Format Cell: Opens the Custom Format Cell dialog which lets you format cells in the column and stripe which you clicked upon. This cascade menu is only available when the Design Grid command of the View menu is activated.
• Order: This cascade menu is only available when the Design Grid command of the View menu is activated or when the Always Show Design Menu Items under User Preferences: Design is checked. It contains four commands to set the layout layer of the sheet objects. Valid layer numbers are -128 to 127.
  • Bring to Front: Sets the layout layer of the sheet object to the largest value currently used by any sheet object on the current sheet.
  • Send to Back: Sets the layout layer of the sheet object to the smallest value currently used by any sheet object on the current sheet.
  • Bring Forward: Increases the layout layer of the sheet object by one. Maximum value is 127.
  • Send Backward: Decreases the layout layer of the sheet object by one. Minimum value is -128.
• Change Value: Only available for table box columns displaying input fields. Sets the cell clicked on to input mode. Equivalent to clicking the input icon in the cell.
• Restore Values: Only available for table box columns displaying input fields. Opens a cascade menu with three options.
  • Restore Single Value: Restores the value in the field value clicked on to its default value from the script.
  • Restore Possible Values: Restores the values of all possible field values to their default values from the script.
  • Restore All Values: Restores the values of all field values to their default values from the script.
• Select Possible: All non-excluded values of the field are selected.
• Select Excluded: All excluded values of the field are selected.
• Select All: All values of the field are selected.
• Clear All Selections: Clears selections in all fields displayed in the table box.
• Clear: Clears all the current selections of the field.
• Clear Other Fields: Selects all possible values in the current field, then clears the selections in all other fields.
• Lock: Locks the selected value(s) of the field.
• Unlock: Unlocks the locked value(s) of the field.
• Print...: Opens the Print dialog where print settings can be specified.
• Print as PDF...: Opens the Print dialog with the Microsoft Print to PDF printer pre-selected. After pressing the Print button you will be prompted for a file name for the PDF output file. This command is only available if a PDF printer is available on the system.
• Send to Excel: Exports possible (including selected) values to Microsoft Excel, which is automatically launched if not already running. The exported values will appear in a new Excel worksheet. For this functionality to work Microsoft Excel 2007 or later must be installed on the computer.
• Export...: Exports the contents of the table to a file of your choice. The file can be saved as any of the following formats: Comma Delimited, Semicolon Delimited, Tab Delimited, Hypertext (HTML), XML and Excel (xls or xlsx). The default format is *.qvo (QlikViewOutput), a tab separated file. Images in a table box will not be included when exporting to HTML format.
• Copy to Clipboard: This cascade menu contains the various copy options for the sheet object.
  • Full Table: Copies the table to the clipboard, complete with header and selection status.
  • Table Data Area: Copies only the values of the table to the clipboard.
  • Cell Value: Copies the text value of the list box cell right-clicked upon (when invoking the Object menu) to the clipboard.
  • Image: Copies an image of the sheet object to the clipboard. The image will include or exclude the sheet object caption and border depending on the settings in the User Preferences: Export page. Tables generated by the hidden part of the script will not be represented by name in the $Table system field.
  • Object: Copies the entire sheet object to the clipboard for pasting elsewhere in the layout or in another document opened within the current instance of QlikView.
• Linked Objects: Opens a menu with the following commands for linked objects. Adjust Position of Linked Objects: All linked objects on all sheets are adjusted to the same position and size as the one/ones highlighted.
  Unlink This Object/Unlink Objects: This destroys the link between the objects, making them different objects with different object IDs.

 

• Minimize: Iconizes the object. Clicking on the icon in the object caption (if shown) produces the same result. This command is available only if minimizing is allowed in the object's Properties dialog on the Caption page.
• Maximize: Enlarges the object to fill the sheet. Clicking on the icon in the object caption (if shown) produces the same result. This command is available only if maximizing is allowed in the object's Properties dialog on the Caption page.
• Restore: Restores a minimized or maximized object to its previous size and location. Double-clicking the icon of a minimized object or clicking the icon in the object caption (if shown) of a maximized object produces the same result. This command is available only for minimized or maximized objects.
• Help: Opens QlikView help.
• Remove: Removes the sheet object from the sheet.

Custom Format Cell dialog

This dialog lets you apply custom formats to one or more groups of table cells. It can be invoked from the object menus of a table box, a straight table or a pivot table, provided that the Design Grid command of the View menu is activated or when the Always Show Design Menu Items under User Preferences: Design is checked.

The group of cells to be affected by the formatting is determined by where you right-click in the table to invoke the command. The smallest group of cells to be formatted as a unit is one field (normally column) in a table box or one expression/dimension in a table chart. If striping is used, each stripe is formatted separately.

Whenever this dialog is used to custom format any part of a table, the Style as set in the Style page of the table's Properties dialog will be set to [Custom]. If you change the Style setting back to one of the pre-defined styles, the custom formatting will be lost.

• Table Preview Pane: The preview pane shows format changes made to the group/groups of table cells. Unlike the smaller preview to the right, Sample Text, it shows the changes in context and after the application of visual cues and/or attribute expression formatting.
  Within this pane it is possible to freely move about the table and apply formats to the different groups of cells. Just click in a cell to move formatting focus.
• Undo: The Undo button can be used for undoing the changes made within the Custom Format Cell dialog step by step.
  After leaving the Custom Format Cell dialog all changes made in the dialog may be undone with just one click on the Undo button in the main toolbar.
• Redo: The Redo button can be used for reapplying changes previously undone with the Undo button within the Custom Format Cell dialog step by step.
• Sample Text: This is the preview cell which shows the settings made.
• Background Color: Defines the cell background color.
• Text Color: Defines the cell text color.
• Border before cell: Here you define the border to precede the cell. There is a drop-down for setting border style and a button to set border color. Note that cell borders will be merged between cells, which may cause the actual border in the table to look differently from the preview.
• Border after cell: Here you define the border to succeed the cell. There is a drop-down for setting border style and a button to set border color. Note that cell borders will be merged between cells, which may cause the actual border in the table to look differently from the preview.
• Text settings: In this group you fins a number of text modifiers applicable to the cell.
  • Text Size: Use the drop-down to apply a size modification to the general font used in the table.
  • Bold: Mark this check box for bold text.
  • Italic: Mark this check box for italic text.
  • Underlined: Mark this check box for underlined text.
  • Drop Shadow: Mark this check box for text with drop shadow.
• Apply change to: Changes are normally applied only to the expression, dimension or field (table boxes) in which you right-clicked to access the dialog. With the help of this drop-down you may choose to apply the same formatting to other expressions, dimensions or fields.

General

The Table Box Properties: General page is opened by a right-click on a table box and choosing the Properties command from the float menu, or by selecting Properties in the Object menu when a table box is active.

• Title: In the Title window the table box can be given a name which will be displayed in the window caption. The title can also be defined as a calculated formula for dynamic update of the label text. Click the ... button to open the Edit Expression dialog for easier editing of long formulas.

  Expression syntax for calculated formulas

  
  
• Available Fields: In this column the names of the data source fields are shown. Initially all fields (excluding System fields) appear in this column. To include the system fields, check Show System Fields. Select the items to be used/removed by clicking them. Use the Add > or the < Remove button to move them to the desired column.
• Fields Displayed in Tablebox: In this column the names of the fields selected from the Available Fields list to be included in the table box are shown. Initially no fields are in this column.
  When a field is selected in this list, you may give it a custom Label in the edit window.
• Show System Fields: Check this box to make the system fields appear in the Available Fields column.
• Show Fields from Table: From here, you control what fields appear in the Available Fields list. The drop-down list displays the alternative All Tables by default. If you want the list to show fields from a specific table, select the table name in the drop-down list.
  The alternative All Tables (Qualified) shows the fields qualified by the name of the table(s) they occur in. This means that key (connecting) fields will be listed more than once. (This alternative is only used for viewing purposes and has nothing to do with Qualify fields in the load script.)
• Promote: Moves a field upwards in the display order.
• Demote: Moves a field downwards in the display order.
• Count Order: Sorts the fields in the Fields Displayed in Tablebox column in numeric order.
• Load Order: Sorts the fields in the Fields Displayed in Tablebox column in load order, i.e. the order in which they are read from the database.
• Text Order: Sorts the fields in the Fields Displayed in Tablebox column in alphabetical order.
• Alternate State:

  Choose one of the available states in the list. The following Alternate States are always available.

  • Inherited: The sheets and sheet objects are always in the inherited state unless overridden by the QlikView developer. This setting is inherited from the object on the level above, a chart in a sheet gets the same settings as the sheet if inherited is chosen.
  • Default state: This is the state where most QlikView usage occurs and is represented by $. The QlikView document is always in the default state.
• Object ID: This is used for macro purposes. Every sheet object is assigned a unique ID. We recommend that you use alphanumeric characters only in the ID. For table boxes, the ID starts with TB01. Linked objects share the same object ID. You may edit this ID number later on.

  Internal Macro Interpreter

• Calculation Condition: Specify a Calculation Condition which must be fulfilled for the table box to be displayed. As long as the condition is not met, the message "Calculation condition unfulfilled" will be displayed instead.
• Error Messages: The standard error messages in table boxes (and charts) can be customized in the Custom Error Messages dialog which is opened from the Error Messages button.
  

  Custom Error Messages
  

Sort

The Table Box Properties: Sort tab is opened by a right-click on a table box and choosing the Properties command from the float menu.

In the Sort Priority list the fields of the table box are displayed. The order of the fields determine the sort order applied when the Sort command is executed. The order of the fields can be changed by using the Promote and Demote buttons. For each field in the list the criteria to be used in the sorting procedure can be specified. The sort criteria are described below.

Sort by:

• Expression: Field values will be sorted according to an arbitrary expression that is entered into the text edit box below this sort option.
• Frequency: Toggles the status whether or not the sorting procedure of values should be made by frequency.
• Numeric value: Toggles the status whether or not the sorting procedure of values should be made by numeric value.
• Text: Toggles the status whether or not the sorting procedure of values should be made in alphabetical order according to the ASCII standard.
• Load order: Toggles the status whether or not the sorting procedure of values should be made in load order.

If more than one sort order is specified, the sort order will be expression, frequency, numeric, text, load order.

Presentation

The Table Box Properties: Presentation tab is opened by a right-click on a table box and choosing the Properties command from the float menu.

In this dialog you determine the layout of the table box.

• Alignment: The default alignment of field values can be set separately for Text and Numbers by selecting the field in the Fields list and the appropriate option in the Alignment group. As default, text values are left aligned, numbers values are right aligned.
• Omit Rows Where Field is NULL: If checked, rows containing a field value that is NULL from the selected field are removed. By NULL "no value" is understood.
• Dropdown Select: If checked, a dropdown arrow icon is added to the left side of the column header of the selected field. Click the icon to access the field values from a dropdown field list. This works exactly like making selections in a multi box.

 

• Advanced...: This button opens the Advanced Field Settings dialog which offers settings for image representation of field values and special text search options.
• Multiline Settings: In this group the table header and data cells may be set to display values in multiple rows, which is useful for long text strings.
  • Wrap TextHeader Height n Lines: With this option selected, the header will display its contents in more than one row. Here the desired limits to the number of header lines is specified.
  • Wrap TextCell Height n Lines: With this option selected, a cell will display its contents in more than one row. Here the desired limits to the number of cell row lines is specified.

 

• Vertical Labels: If checked, all field labels will be rotated to the vertical position.
• Allow Drag and Drop: If checked, it will be possible to sort the table box fields by clicking and dragging the headers.
• Sort Indicator: If checked, a sort indicator icon is added to the right side of the column header of the field that the table box is currently sorted by. The icon is flipped to reflect ascending or descending sort order.
• Selection Indicators: If checked, a sort indicator icon is added to the right side of the column header of the field that the table box is currently sorted by. The icon is flipped to reflect ascending or descending sort order.
• Suppress Header Row: If checked, the table will be displayed without header (label) row.

Style

This style page applies to all QlikView tables; table boxes, pivot tables and straight tables. Here you make settings for the table formatting style.

 

• Current Style: Choose an appropriate table style from the drop-down list. If the value [Custom] appears in the drop-down control a custom style has been applied to the table. If you change the setting back to one of the pre-defined styles, the custom formatting will be lost.
• Stripes every _ Rows: Here you can specify if and at how long intervals shaded stripes should appear.
• Indent Mode: This setting is only valid for pivot tables. With this alternative checked, you can achieve a slightly different table style that is especially useful when you need to accommodate a number of dimension labels within a limited table width.
  • Use Only First Dimension Label: This setting is only available for pivot tables already in Indent Mode and modifies the style of the pivot table further.
• Vertical Dimension Cell Borders: This setting determines whether vertical cell borders are displayed for dimension columns.
• Vertical Expression Cell Borders: As above, but for expression columns.
• Border Above Spacing: Provided that a Spacing has been determined in the Advanced Field Settings dialog, the table style can be slightly modified by checking this alternative.

  Advanced Field Settings

• Background...: Opens the Background Settings dialog.
• Cell Background Color Transparency: If a color or an image has been applied in Background Settings, you can adjust the transparency of that color or image in the cell background here.
• Cell Borders Transparency: Sets how pronounced the cell borders should be.

Font

Here the Font, Font style and Size of the font to be used can be set.

The font can be set for any single object (Object Properties: Font), or all objects in a document (Apply to Objects on Document Properties: Font).

Further, the default document fonts for new objects can be set on Document Properties: Font. There are two default fonts:

1. The first default font (List Boxes, Charts, etc) is used for most objects, including list boxes and charts.
2. The second default font (Text Objects and Buttons) is used for buttons and text boxes, which are objects that usually need a larger font.

Finally, the default fonts for new documents can be set on User Preferences: Font.

For charts, buttons and text objects (except search objects) a font Color can also be specified. The color may Fixed or it can be dynamically Calculated from an expression. The expression must be a valid color representation, which is created using the color functions. If the result of the expression is not a valid color representation, the font color will default to black.

Color functions

Additional settings are:

• Drop Shadow: If this option is checked a drop shadow will be added to the text.
• Underline: If this option is checked the text will be underlined.

A sample of the selected font is shown in the preview pane.

Layout

A Layout setting will apply to the current object only, if it is made from the Object Properties page.
A Layout setting will apply to all objects of the specified type(s) in the document, if it is made from the Document Properties page.

Use Borders

Enable this setting in order to use a border around the sheet object. Specify the type of border by selecting it in the drop-down menu.

• Shadow Intensity: The Shadow Intensity drop-down menu makes it possible to set the intensity of the shadow that surrounds the sheet objects. There is also the choice of No Shadow.
• Border Style: The following predefined border types are available:
  • Solid: A solid unicolored border.
  • Depressed: Border giving the impression of depressing the sheet object from the background.
  • Raised: Border giving the impression of raising the sheet object from the background.
  • Walled: Border giving the impression of a wall around the sheet object.
• Border Width: This option is available for all border types. The width can be specified in mm, cm, inches (", inch), pixels (px, pxl, pixel), points (pt, pts, point) or docunits (du, docunit).
• Color: Click this button to open a dialog in which an appropriate base color can be chosen from the color palette for all border types.
• Rainbow: Creates a rainbow colored border for all border types. The rainbow will start with the selected base color on top of the sheet object.

When Simplified is the chosen Styling Mode in Document Properties: General, there is no choice of border type, there is only the Shadow Intensity drop-down menu and the Border Width setting.

Rounded Corners

In the Rounded Corners group the general shape of the sheet object is defined. These settings allow for drawing of sheet objects ranging from perfectly circular/elliptical via super elliptical to rectangular. Rounded Corners is only available if you have selected Advanced Styling Mode in Document Properties: General.

• Rounded Corners: Checking this option makes the alternatives for rounded corner shapes possible.
• Corners: Corners for which the check box remains unmarked will be drawn rectangular instead.
• Squareness: A variable number between 2 and 100 where 100 defines a rectangle with perfectly square corners and 2 corresponds to a perfect ellipse (a circle for a 1:1 aspect ratio). A squareness between 2 and 5 is usually optimal for achieving rounded corners.
• Corner Radius: This setting determines the radius of the corners in fixed distance (Fixed) or in percent of the total quadrant (Relative (%)). This setting lets you control the extent to which the corners will be affected by the underlying general shape set under Squareness. The distance can be specified in mm, cm, inches (", inch), pixels (px, pxl, pixel), points (pt, pts, point) or docunits (du, docunit).

Layer

In the Layer group an object can be defined as residing in one of three layers:

• Bottom: A sheet object with the Bottom layer property can never obscure sheet objects in the Normal and Top layers. It can only be placed on top of other sheet objects in the Bottom layer.
• Normal: When created, sheet objects reside in the Normal (middle) layer. A sheet object in the Normal layer can never be obscured by sheet objects in the Bottom layer and can never obscure sheet objects in the Top layer.
• Top: A sheet object in the Top layer can never be obscured by sheet objects in the Normal and Bottom layers. Only other sheet objects in the Top layer can be placed on top of it.
• Custom: The Top, Normal and Bottom layers correspond to internally numbered layers 1, 0 and -1 respectively. In fact all values between -128 and 127 are accepted. Choose this option to enter a value of your choice.

Theme Maker...

Opens the Theme Maker wizard where you can create a layout theme.

Apply Theme ...

You can apply a layout theme to the object, sheet or document.

Layout Themes

Show

In the Show group it is possible to specify a condition under which the sheet object is displayed:

• Always: The sheet object will always be displayed.
• Conditional: The sheet object will be shown or hidden depending on a conditional function which will be evaluated continuously depending on e.g. selections etc. The sheet object will only be hidden when the condition returns FALSE.
  

  Conditional functions

Options

In the Options group, it is possible to disallow moving and resizing of the sheet object. The settings in this group are only relevant if the corresponding check boxes are enabled in Document Properties: Layout and Sheet Properties: Security.

• Allow Move/Size: If this option has been deselected it will be impossible to move or resize the sheet object.
• Allow Copy/Clone: If this option has been deselected it will be impossible to make a copy of the sheet object.
• Allow Info: When the info function is in use an info icon will be displayed in the window caption whenever a field value has information associated to it. If you don't want the info icon to be displayed in the caption, you can uncheck this option.
  
  Info
• Size to Data: Normally the borders around all table sheet objects in QlikView will shrink when selections cause the size of the table to be less than allocated size for the sheet object. By deselecting this check box this automatic adjustment of size will be turned off leaving any surplus space blank.

Scrollbars

Various controls for changing the scrollbar layout are located in the Scrollbars group:

• Preserve Scroll Position: With this setting enabled, QlikView will try to preserve the scroll position of tables and charts with a scroll bar when a selection is made in another object. The setting must be enabled in User Preferences: Objects as well. The scroll position is not preserved when you close the document.
• Scroll Buttons: Sets the scroll button color. Select a color by clicking the button. Note that medium gray tones often render the best results for scroll bars. Either color can be defined as a solid color or a gradient via the Color Area dialog that opens when you click the appropriate button.
• Scroll Background: Sets the scrollbar background color. Select a color by clicking the button.
• Scrollbar Width: This control affects both the width and the relative size of the scrollbar symbols.
• Scroll Style: Sets the scroll bar style. Select a style in the drop-down control. The Classic scroll bar style corresponds to QlikView 4/5 scroll bars. The Standard scroll bar style gives a more modern look. The third style is Light, which is a thinner, lighter bar.
  The Styling Mode must be set to Advanced for the scroll bar style to be visible. This setting can be found on the General tab by opening the Settings drop-down menu and selecting Document Properties.

 

• Apply To...: Opens the Caption and Border Properties dialog where you can set where to apply the properties that have been set on the Layout page.

Caption

A Caption setting will apply to the current object only, if it is made from the Object Properties page.
A Caption setting will apply to all objects of the specified type(s) in the document, if it is made from the Document Properties page.

On the Caption page, specify layout options that are completely different from the general layout of the object.

• Show Caption: When this option has been checked a caption will be drawn at the top of the sheet object. List boxes and other "box objects" will have the option switched on by default whereas buttons, text objects and line/arrow objects will not.
• Title Text: In the text box you can enter a title to be shown in the caption of the sheet object. Use the Font... button to change the caption font.

Set the colors of the caption in its different states. The settings for Active Colors and Inactive Colors can be made separately from each other.

Click the Background Color or Text Color button to open the Color Area dialog. The Background Color can be defined as a solid or a gradient color on the Color Area dialog. Text Color can be defined a Fixed or a Calculated color using color functions.

Color functions

• Wrap Text: If this option is checked, the caption will be displayed in two or more rows.
• Caption Height (Lines): Set the number of caption lines in this edit box.

The precise size and position of the QlikView object can be determined and adjusted by the size/position settings for the Normal or Minimized QlikView sheet object. These settings are measured in pixels:

• X-pos: Sets the horizontal position of the left side of sheet object in relation to the sheet's left edge.
• Y-pos: Sets the vertical position of the top side of sheet object in relation to the sheet's upper edge.
• Width: Sets the width of the QlikView sheet object.
• Height: Sets the height of the QlikView sheet object.

The orientation of the caption label can be altered with the Caption Alignment options:

• Horizontal: The label can be horizontally aligned: Left, Centered or Right within the caption area.
• Vertical: The label can be vertically aligned: Top, Centered or Bottom within the caption area.

Special Icons
Many of the object menu commands of the sheet objects can be configured as caption icons. Select commands to be shown as caption icons by marking the check box to the left of each command in the list.

• Allow Minimize: When this option has been checked a minimize icon will be displayed in the window caption of the sheet object, providing that the object is minimizable. Furthermore, this will make it possible to minimize the object by double-clicking the caption.
• Auto Minimize: This option becomes available when Allow Minimize is checked. When Auto Minimize is checked for several sheet objects on the same sheet, all but one will be automatically minimized at any time. This is useful e.g. for alternately displaying several graphs in the same sheet area.
• Allow Maximize: When this option has been checked a maximize icon will be displayed in the window caption of the sheet object, providing that the object is maximizable. Furthermore, this will make it possible to maximizing the object by double-clicking the caption. If both Allow Minimize and Allow Maximize are checked, double-clicking will effect minimizing of the object.
• Help Text: Here you may enter a help text to be displayed in a pop-up window. The help text may be specified as a calculated formula. This option is not available at document level. Click the ... button to open the Edit Expression dialog for easier editing of long formulas.
  

  
  Expression syntax for calculated formulas
  

  Enter e.g. a description of the sheet object. A help icon will be added to the window caption of the object. When the mouse pointer is over the icon, the text will be displayed in a pop-up window.