Column Formats and Mapping

How to:

Reference:

When you add transformations using either the Source or Target Transformations window, you can assign formats to the columns that you are adding. There are four types of column formats:


Top of page

x
Reference: Rules for Mapping and Column Formats

Columns that you map to each other must have the same format, or DataMigrator must be able to translate the source query format into the target format automatically. Details are shown in the following table:

If the Target format is...

...and the Source query format is...

Can you map?

A11

A10

Yes

A10

A11

No. Use the EDIT or SUBSTR functions to make this work.

D6.2

I5

Yes

D6.2

Yes, but you will lose the numbers after the decimal.

A10

D6.2

No



x
Procedure: How to Format a Column as Alphanumeric or Numeric

If you are creating a new or virtual column, you need to assign a format to the column. You can assign a format in two ways:

To use the Define Properties dialog box:

  1. From the Transformation Calculator, click the ellipsis (...) button to the right of the Format field.

    The Define Properties dialog box opens.

  2. Select a format from the Type drop-down menu.
  3. Click OK.

Top of page

x
Reference: Define Properties Dialog Box

The Define Properties dialog box is available from the Transformation Calculator by clicking the ellipsis (...) button to the right of the Format field.

Define Properties

The Define Properties dialog box has the following fields and options:

Format

The format name of the column.

Type

The format type of the column.

Note: The options available will depend on the type of format selected.


Top of page

x
Reference: Numeric Format Options

When using a numeric format (Integer, Float, Double, or Decimal Packed) the following options are available:

Negative

These are the display options for negative numbers:

Default - Minus sign at the left

Positions the minus (-) sign at the left of the number.

- - Minus sign at the right

Positions the minus (-) sign at the right of the number.

B - Bracket negative

Encloses a negative number in parentheses.

C - Credit (CR) negative

Adds the characters CR after a negative number.

Comma

These are the display options for commas:

Default - Suppress

Suppresses the use of a comma in the number.

c - Suppress

Suppresses the use of a comma in the number.

C - Insert

Inserts a comma after every third significant figure. This option takes effect automatically for Double format.

L - Leading zeroes

Adds leading zeros to the full field length.

S - Print blank for zero

Suppresses leading zeros; displays a space if the value is 0.

% - Percent Sign

Displays a percent sign along with the numeric data.

E - Scientific notation

Displays the number in scientific notation.

Currency Symbol

These are the extended currency symbol display options, which allow you to select a currency symbol regardless of what is set as the default:

  • N - Fixed $ sign
  • M - Floating $ sign
  • !e - Fixed Euro symbol
  • !E - Floating Euro symbol
  • !l - Fixed pound sterling sign
  • !L - Floating pound sterling sign
  • !y - Fixed Japanese yen symbol
  • !Y - Floating Japanese yen symbol
  • !d - Fixed dollar sign
  • !D - Fixed dollar sign
Currency

Specifies a field as a currency denominated value.

The Define Properties dialog box for the Double format looks like this:

Define Properties dialog box


Top of page

x
Procedure: How to Format a Column as Date, Time or DateTime (Timestamp)
  1. In the Transformation Calculator, click the ellipsis (...) button to the right of the Format field.

    The Define Properties dialog box opens.

  2. Select Date, Time or DateTime (Timestamp) from the FORMAT drop-down menu.

    The Date Format allows you to choose the display for dates (YYMD, MDY, and so on).

    The Time Format allows you to choose the display for time (seconds, milliseconds, and so on) and am/pm.

    The DateTime Format allows you to choose the display for both date and time portions.


Top of page

x
Reference: Date Type Format Options

The following choices are available from the Define Properties dialog box for the date format (the examples illustrates August 21, 2004):

Format

Description

Example

YYMD

Four-digit Year/Month/Day.

2004/08/21

MDYY

Month/Day/Four-digit Year.

08/21/2004

DMYY

Day/Month/Four-digit Year.

21/08/2004

YYQ

Four-digit Year/Quarter

2004 Q4

QYY

Quarter Four-digit Year.

Q4 2004

YYM

Four-digit Year/Month.

2004/08

MYY

Month/Four-digit Year.

08/2004

YYJUL

Four-digit Year with the month in Julian format. The last three digits are the number of days since January 1.

2004/234

YY

Four-digit year.

2004

Y

Two-digit year.

04

Q

Quarter.

Q3

M

Month.

08

MT

Short month in uppercase.

AUG

Mt

Short month in mixed-case.

Aug

MTR

Full month in uppercase.

AUGUST

Mtr

Full month in mixed-case.

August

W

Three-letter day of the week abbreviation in uppercase.

SAT

w

Three-letter day of the week abbreviation in mixed-case.

Sat

WR

Day of the week in uppercase.

SATURDAY

wr

Day of the week in mixed-case.

Saturday

D

Day.

21



x
Reference: Time Type Formats

The following choices are available from the Define Properties dialog box for the time format (the example illustrates 2:05 a.m.):

Format

Description

Example

HH

Hours.

02

HHI

Hours/Minutes.

02:05

HHIS

Hours/Minutes/Seconds.

02:05:27

HHISs

Hours/Minutes/Seconds/Milliseconds.

02:05:27:123

HHISsm

Hours/Minutes/Seconds/Milliseconds/Microseconds.

02:05:27:123456

HI

Minutes.

05

HIS

Minutes/Seconds.

05:27

HISs

Minutes/Seconds/Milliseconds.

05:27:123

HISsm

Minutes/Seconds/Milliseconds/Microseconds.

05:27:123456

HS

Seconds

27

HSs

Seconds/Milliseconds.

27:123

HSsm

Seconds/Milliseconds/Microseconds.

27:123456

Hh

Hours without leading zeros.

2

HhI

Hours without leading zeros/Minutes.

2:05

HhIS

Hours without leading zeros/Minutes/Seconds.

2:05:27

HhISs

Hours without leading zeros/Minutes/Seconds/Milliseconds.

2:05:27:123

HhISsm

Hours without leading zeros/Minutes/Seconds/Milliseconds/Microseconds.

2:05:27:123456

Hi

Minutes without leading zeros.

5

HiS

Minutes without leading zeros/Seconds.

5:27

HiSs

Minutes without leading zeros/Seconds/Milliseconds.

5:27:123

HiSsm

Minutes without leading zeros/Seconds/Milliseconds/Microseconds.

5:27:123456



x
Reference: DateTime(Timestamp) Type Formats

The DateTime(Timestamp) type format combines date, time, and a separator, which are reflected in the format name. For example, HYYMD-H describes a four-digit year, month and day separated by dashes plus hours.

The following choices are available from the Define Properties dialog box for the date component:

Format

Description

Example

HYYMD

Four-digit Year/Month/Day.

2004/08/21

HYMD

Two-digit Year/Month.

04/08

HMD

Month/Day.

08/21

HD

Day

21

The time component is added to the date format name as follows:

Format

Description

Format Name

Example

H

Hours.

HYYMDH

2004/08/21 02

I

Hours/Minutes.

HYYMDI

2004/08/21 02:05

S

Hours/Minutes/Seconds.

HYYMDS

2004/08/21 02:05:27

s

Hours/Minutes/Seconds/Milliseconds.

HYYMDs

2004/08/21 02:05:27:123

m

Hours/Minutes/Seconds/ Milliseconds/Microseconds.

HYYMDm

2004/08/21 02:05:27:123456

The following choices are available from the Define Properties dialog box for the separator, which is added to the format name.

Format

Description

Format Name

Example

/

Slash.

HYYMDH

2004/08/21 02

.

Period.

HYYMD.H

2004.08.21

-

Dash.

HYYMD-H

2004-08-21

Blank

Space

HYYMDBH

2004 08 21

None

No separator

HYYMDNH

20040821

U

International default.

HYYMDU

 

iWay Software