Creating a virtual table

Overview

A [highlight txt=’Virtual Table’] contains columns and rows, just like a managed table. However, the fields (columns) in a Virtual Table are the result of a SQL query referencing one or more managed tables. This article will explain how a SolveXia user can create Virtual Tables by writing their own SQL queries.

[message type=”warning”]For the purposes of this article we are going to use a simple and fictitious Managed Table named “AirlineInfo”. You would this Table with one of your own.[/message]

Before we get started

The Managed Table has the following details and design:

The data in the Managed Table looks like this:

Suppose we want to create a new column of data (named ‘New Amount’) to the right of the ‘Amount’ column. Suppose further that we want ‘New Amount’ to contain data in the ‘Amount’ column, but 100 times greater.

Steps

1. Ensure that the Process List pane is visible.

2. [highlight txt=’Right-click’] the ‘Managed Tables’ folder.

3. The default name of this Virtual Table will be “New_virtual_table”. Rename it to “AirlineInfoNew”.

4. Type the following query into the Expert Query Builder: SELECT *, ([Amount]*100) AS ‘New_Amount’ FROM [AirlineInfo]

5. Click [highlight txt=’Save’].

6. To view the format of the Virtual Table, double-click its icon in the Process List Pane.

7. The format will look like this:

8. You can view the data in the Virtual Table by creating a tabular report over it or exporting it to Excel. The data will look like this:

Recent Posts

Leave a Comment