PQ Systems Knowledge Base

Revision Date: 2008-05-30

Problem:

How can I have CHARTrunner fetch the specification limits for a chart from the data source?

Solution:

CHARTrunner 2.0 and above has the ability to treat a column from the data source as a specification limit. This is done using the "Treat as" column of the grid on the "Data definition" tab of the chart definition. A specification column can be treated as: Upper spec, Target spec, or Lower spec.

If you are going to fetch the specification limits from the data source, then the data source for the chart must provide the columns containing the specification limit values along with the columns containing the other chart data and identifiers.

CHARTrunner uses the last non-null specification value found in the rows of data from the data source as the specification limit value that will be used for the chart. If the chart definition specifies a specification limit value and you also map a column from the data source as a specification limit value, the mapped column value from the data source will be used.

The data source shown below contains the Data1 column that can be treated as a Measurement, the LowerSpec column that can be treated as the Lower spec, the TargetSpec column that can be treated as the Target spec, and the UpperSpec column that can be treated as the Upper spec.

It can be impractical to actually store the specification limit columns in the same table as the chart data. Typically the chart data is stored in one table and the specification limits are stored in another table.

Let's assume that the chart data is stored in the following ChartData table.

And let's assume that the specification limits for many processes within the organization are stored in the following SpecLimits table.

Each row of specification limits are uniquely identified by the SpecID value. In this case, the specification limits in the row with SpecID = 10 are the ones we want to use with the data from the ChartData table.

Somehow we need to "glue together" the specification columns (LowerSpec, TargetSpec, UpperSpec) from the row where SpecID = 10 of the SpecLimits table to each row of the ChartData table. As it turns out, the SQL query language has a means of doing just that - it is called a LEFT OUTER JOIN operation, which is sometimes shortened to just LEFT JOIN. The result of a LEFT JOIN for tables A and B always contains all records of the "left" table (A), even if the join-condition does not find any matching record in the "right" table (B). In our case, we want the left table (A) to be ChartData and the right table (B) to be SpecLimits, and we always want to match exactly one row from the SpecLimits table, i.e. the row where SpecID = 10.

The SQL SELECT statement that specifies the LEFT JOIN operation can be placed directly into a chart definition as a "Custom query" on the Data definition tab. Or, you can use a "Query or view", or a "Stored procedure" that uses a LEFT JOIN as the data source for the chart definition.

Now let's look at some representative SQL SELECT statements that use the LEFT JOIN operation for several common database engines. Your results with other database engines will likely vary. Each example does a join of the ChartData table with the SpecLimits table to result in a data source that contains the columns of the Example Data Source show above.

Oracle

select A.*, B.LowerSpec, B.TargetSpec, B.UpperSpec 
	from ChartData A LEFT JOIN SpecLimits B
	on B.SpecID = 10

SQL Server

select A.*, B.LowerSpec, B.TargetSpec, B.UpperSpec 
	from ChartData as A LEFT JOIN SpecLimits as B
	on B.SpecID = 10

Microsoft JET 4.0 Database Engine (used for Access, Excel, dBase, etc.)

The Microsoft JET 4.0 database engine is used by CHARTrunner to fetch data from Microsoft Access, Microsoft Excel, Paradox, dBase, Lotus 123 and text files.

One would hope that a SELECT statement similar to the following would work, since it works with SQL Server and Oracle.

[This select statement doesn't work with JET 4.0]
select A.*, B.LowerSpec, B.TargetSpec, B.UpperSpec 
	from ChartData as A LEFT JOIN SpecLimits as B
	on B.SpecID = 10

Sadly, the JET 4.0 database engine seems to require that the "ON expression" portion of the statement explicitly refer to both the A and B tables, rather than just the B table as shown above. If "B.SpecID = 10" is used anywhere in the expression then an error results, apparently because it refers only to B.

The following SELECT statement works around the peculiarities of JET, but it is certainly not elegant, and it has limitations. If someone comes up with a better method, please let us know.

select A.*, B.LowerSpec, B.TargetSpec, B.UpperSpec 
	from ChartData as A LEFT JOIN SpecLimits as B
	on (A.ID <= B.SpecID or A.ID >= B.SpecID)
	where B.SpecID = 10

The "B.SpecID = 10" has been moved from the ON clause into the WHERE clause.

In the ON clause, the "(A.ID <= B.SpecID or A.ID >= B.SpecID)" expression was chosen because it always evaluates to being true, and both A and B are used in each comparison expression which seems to make JET be happy. In your SELECT statement you must specify a similar ON clause that uses a column from each of your tables. In most cases you will have a numeric column in each table, and an expression of this form will always evaluate to true for any two numeric columns - as long as the numeric column doesn't contain a NULL value. That's a limitation of this approach. In the example shown above if the second row of ChartData contained a NULL value in the ID column, then that row WOULD NOT be included in the data source results. The JET implementation of LEFT JOIN seems to be flawed - in theory each row of the left table should be included in the results no matter how the ON clause evaluates (i.e. true, false or NULL). But in practice we find that a NULL result in the ON expression results in that row from the left table being omitted.