NPM Null, point, missing

1
NPM(Null, point, missing)

• Thomas Hoel
• Zagreb Octobre 2004

2
The purpose 1

• A method for commenting on or replacing the
  contents of a datacell
• Most often needed for datacells without a
  presentable value, i.e. datacells that
  traditionally would be presented as NULL, a point
  (dot) or a sign for missing value
• May also be useful for giving background
  information for certain datacells that contains a
  value

3
The purpose 2

• Typical cases a user would want to comment on
• Special circumstances For important background
  information about the value in the datacell
  (preliminary or estimated value). Also called a
  marker.
• Absolutely nil there is no value (and will not
  be any) in the datacell. Null value
• Records missing Reports for the category is
  missing. Missing value

4
The problem

• Value is a property of each of the datacells in
  the data matrices
• NPM comments should be specifiable for each cell
  in the matrices
• Preferably the possibility for calculations on
  the datacells should be preserved
• Use of the NPM system should be optional for each
  maintable

5
The NPM method 1

• In the metadata section of the database a new
  table (SpecCharacter) is created to hold a list
  of special signs
• CharacterType Code for the specialsign
• PresCharacter The string to be presented in the
  tables
• AggregPossible Can the cell participate in a
  calculation
• DataCellFilled Y/N/Any, the datacell contains a
  value
• PresText Description of the meaning of the
  special sign

6
The NPM method 2

• In the table Maintable a new column
  SpecCharExists (Y/N) shows if a particular
  maintable uses the NPM system or not
• A few minor additions in the table MetaAdm

7
Special signs

• Each special sign must have a code and a
  presentation text.
• Typical special signs are

8
Changes in the data tables 1

• The data table(s) for a maintable is/are expanded
  with a number of new columns, one new column for
  each content column, the so called NPM columns

9
Changes in the data tables 2

• If a user wants to make an NPM comment on a
  datacell, the code for a special sign is stored
  in the corresponding cell of an NPM column

10
Aggregation rules

• So far the system is not all that complicated,
  but at this point some problems appear
• To eliminate a variable you have to aggregate
  datacells with different values for that variable
• What happens when one of the datacells in an
  aggregation has an NPM value associated with it?
• Extensive aggregation rules necessary for these
  cases (and for the use of groupings)

11
Very special special signs....

• The special signs referenced in the table MetaAdm
  are
• SpecialSignSum indicates that a sum is valid
  and that more than one special sign have been
  part of an aggregation
• SpecialSignNoSum indicates that more than one
  special sign have been part of a potensial
  aggregation and that the combination of special
  signs made the aggregation impossible

12
Further work

• Two parts of the PC-Axis system must be changed
  to include the NPM system
• Reading data from an SQL database
• Converting these data to a px-file.
• Statistics Norway is rewriting parts of PC-Axis
  to handle these cases