In the Transform dialog it is possible to filter and make advanced transformations of a table. This is particularly important for HTML table, since there is no commonly used standard for how to store data in HTML. As a result, it is impossible to make QlikView capable of reading HTML tables as easily as other more structured data formats.
In order to reach the Transform dialogs, enable the transformation step by clicking the Enable Transformation Step button. The step is not enabled by default in order to save memory.
The Transform dialog provides a variety of filters that can be used to make tables more suitable for QlikView. The dialog has six pages that are described below. The buttons to the bottom left-hand corner of Transform Table dialog are however used for all pages:
| Button | Description |
|---|---|
| Undo | Undoes the last added change. |
| Redo | Redoes the last Undo. |
| Reset | Resets the table to its original status. |
Garbage
On the Garbage tab, rows and columns containing garbage can be removed from the table. In HTML files it is not uncommon to have extra rows and columns that are there just for legibility and these must of course be removed before loading the data into QlikView.
Columns and rows can both be removed explicitly in which case their indices are stored in the script. Rows can also be removed using conditional criteria.
| Button | Description |
|---|---|
| Delete Marked | Deletes the highlighted rows and columns. |
| Conditional Delete... | Opens the Specify Row Condition dialog where the conditional criteria for deletion of rows can be set. |
Fill
With the Fill tab it is possible to replace cell values. In most cases certain cells that match a specific description are replaced with another value from the same column. A common case is that cells are empty and that the first non-empty cell above contains the relevant value.
| Button | Description |
|---|---|
| Fill... | Opens the Fill Cells dialog where it is possible to specify conditions and strategy. |
Column
The Column dialog page is used for copying the contents of a column to a new column. The copy may contain all the cells of the source column or just a selection of the cells. This feature can be useful if a column contains different kinds of values. It can also be used to duplicate a column that contains HTML links so that one column contains the link text, whereas the other column contains its URL.
| Button | Description |
|---|---|
| New... | Opens the Specify cells for new column dialog where it is possible to specify how the new column should be created. |
| Label | Sets the label for the column. |
Context
In an HTML table more information than what is visible can be stored, e.g. in the case that the content of a cell is clickable, the address to where the browser should jump must also be stored. The Transform page will show the text of the cell, but is also able to show the additional information belonging to the cells. Naturally, it is also possible to read this information into QlikView.
Tags always enclose the additional information. A tag has a name and may have attributes, which sometimes have values. The context of a cell may look like this:
Example:
<A href=www.myurl.com/mypage.html name=”MyName”>
My link text
</A>
The visible text of the cell is My link text. The context contains a start tag and an end tag. The tags specify a hypertext link. The start tag has two attributes, href and name.
The Context page can be used to read the attribute of tags instead of the main string. Other kinds of operations are also possible. Right-click the cells in the wizard and choose View context to look at the context of the cell.
| Button | Description |
|---|---|
| Expand... | Opens the Context cell expansion dialog in which you can define an expansion of the contents of one cell into several cells. Note that both a column and a row must be marked in order for this button to be enabled. However, all the cells in the column will be expanded. |
| Interpret... | Opens the Context interpretation dialog in which an interpretation of the contents of the cells can be defined. Note that both a column and a row must be marked in order for this button to be enabled. However, all the cells in the column will be interpreted. |
Unwrap
On the Unwrap tab it is possible to straighten out a wrapped table. Wrapped here means that a second part of the table continues, not below as it should, but beside the first part of the table. In the example above, the right half will thus be moved and put below the left half.
| Button | Description |
|---|---|
| Unwrap | Unwraps the table. The border between the two halves must be set with the cursor first. It is possible to split the table either vertically or horizontally. |
| Conditional Unwrap... | Defines the condition for a vertical split of the table. Opens the Specify Row Condition dialog. |
Rotate
Web designers often rotate tables to give them a nicer look. The main intention of the rotation feature is to make it possible to turn tables "back to normal", i.e. to get fields as columns, field names on the first row etc.
| Button | Description |
|---|---|
| Left | Rotates the table counterclockwise. |
| Right | Rotates the table clockwise. |
| Transpose | Transposes the table, i.e. mirrors the table along a diagonal axis, so that the top right cell becomes the bottom left cell and vice versa. The top left and bottom right cells, however, stay in place. |
