Official | Mapping information (output information)

Type

Specify the field type to be output.

The field to be set varies depending on the specified field.

Input field: : Indicates the field in the input side.
Numeric operations: : Indicates numeric operations.
Query value: : Query value and dynamic query value for a virtual table.
Common components: : Uses common components for output information.
Fixed: : Indicates a fixed character string to be inserted in the output side, a dynamic character string value number specified by a runtime argument, a date character string, or a time character string.
Date operations: : Indicates a date formula.
User exit program: : Indicates a user exit program.

Field name

Specify the name of the input-side field to be added.

Start position

Specify which byte the field will be output from.

Specify a value from 1 to 99,999. You cannot omit this item.

Output size

Specify which byte the field will be output from the start position.

Specify a value from 1 to 99,999. If omitted, everything will be output from the start position.

Function

Specify the function to be run for the mapped input field value.

For configurable functions, see the following table.

Table 2.39 Functions
Function name	Description
CALC_MONTHS	Converts the date specified in the argument to a date where one or more months are added to or subtracted from the date.
CHK_LENB	Checks whether the length of character string data exceeds the maximum length, and returns the specified character string.
COALESCE	Returns the first non-null character inside the items of the argument.
DISPLAY_TO_Z	Converts a signed numeric value (in COBOL DISPLAY format) to a numeric character string.
FORMAT_DATE	Converts to the date format specified in the argument (for example, YYYY/MM/DD).
FORMAT_NUM	Converts according to the number of digits, the handling of the sign, and the decimal places as specified in the arguments.
IF	Returns the corresponding result if a condition is met. Returns the default value if no conditions are met.
ITOM	Converts the type I data to type M character data.
JULIANDAY	Converts the mapped character string to a corresponding date in the Julian calendar. If a date is specified in the argument, that date is converted to a Julian date.
LENB	Obtains the length of the data in bytes, which can be used to check whether the number of fields in a CSV matches the specified value.
MATCH_REG	Determines whether the mapped character string matches the specified condition. The character string is converted into another character string according to the analysis result.
REPLACE_DB	Issues the SQL statement specified in the argument, searches the database table, and replaces it with the value of the search result.
REPLACE_REG	Searches for the specified character string in the argument and replaces it with another character string.
SEQNO	Outputs sequential numbers based on the rule specified in the argument.
SUBSTR	Cuts out the character string of the number of bytes specified in the argument.
TO_INCODE	Converts the target character string to the input character encoding by using the method specified in the argument. External characters and user table codes cannot be used. In DataMagic Desktop grade, the TO_INCODE function cannot be used.
Z_TO_DISPLAY	Converts a numeric character string to a signed numeric value (in COBOL DISPLAY format).

Note

You can specify multiple lines of functions, and input the processing result of one function into the next function. This allows character conversion that is difficult to achieve by using a single function. For example, a function unifies characters that have specific patterns. Then, the next function replaces the unified characters with different characters.

Note that multiple relation lines cannot be used as input of functions at the same time.

Function list

Displays a list of configurable functions.

Function list help

Displays an explanation of the function that is selected in the function list.

Category

The output data categories.

You can select Fixed character string, Dynamic character string (runtime argument), Date, Time, Fixed binary value, or Dynamic binary value.

The content set in Value varies depending on the selected category.

Value (when a fixed value is selected)

Specify the fixed value selected in Category.

For details, see the table below. If a date and time are specified as fixed values in output information settings, the date and time when processing was executed are output.

Table 2.40 Categories and Values
Category	Description
Fixed character string	Specify with 1 to 9999 bytes.
Dynamic character string (Runtime argument)	Specify the dynamic character string number. Specify a number from 01 to 20. The specified field corresponds to the -dstrXX parameter of the command for executing data processing (utled). If you omit the corresponding parameter, a halfwidth space is applied. Example: If the dynamic character string 01 is specified as a fixed value and the following data processing command (utled) is executed, abc will be output in the specified dynamic character string output field. utled -i sample -dstr01 abc
Date	Specify with 255 bytes or fewer. The format is Y, M, D. All other characters are output as is. If the format is converted to right-justified and the format character string is continuous, it will be fixed from the left. Example 1: Today is YYYY/MM/DD -> Today is 2004/5/18 Example 1: Today is YYYYYY/M/D -> Today is 200404/5/8
Time	Specify with 255 bytes or fewer. The format is H, M, S. All other characters are output as is. If the format is converted to right-justified and the format character string is continuous, it will be fixed from the left. Example 1: The current time is HH:MM:SS -> The current time is 12:34:56 Example 2: The current time is HHHH:M:S -> The current time is 1212:4:6
Fixed value (binary)	Specify with 2 to 9998 hexadecimal characters. You can set only type I data for the fixed binary value.
Dynamic value (binary)	Set a dynamic binary number. Specify a number from 01 to 20. The specified binary value corresponds to the -dstrXX parameter of the command for executing data processing (utled). If you omit the corresponding parameter, a 1-byte 0x00 is applied. You can set only type I data for the dynamic binary value. Example: If the dynamic binary value 01 is specified as a fixed value and the following data processing command (utled) is executed, 123 will be output in the specified dynamic character string output field. utled -i sample -dstr01 123

Note

The fixed value that is set is converted to the kanji code on the input side, and then converted to the kanji code on the output side. For this reason, if the fixed value that is set does not exist in Kanji code on the input side, the fixed value might be converted and output to a different code or the default code.

Samples

Displayed when Date or Time is selected for Category.

Use the format entered in Value as reference.

Formula

If Numeric operations or Date operations is selected for Type, specify this item.

Table 2.41 Numeric Formula
Field	Description
Numeric	Used to represent a specific decimal value.
Left and right parentheses	Used to prioritize operations.
Basic arithmetic operation symbols	A plus sign (+) is used for addition. A minus sign (-) is used for subtraction. An asterisk (*) is used for multiplication. A forward slash (/) is used for division.
$INAME()	The item is converted to the specified field value. When occurrence numbers are specified, they are separated by a comma (,). 1 is applied when occurrence numbers are omitted or when a non-numeric value is specified. $INAME cannot be specified when Format in the input settings is CSV with no ID specified. Use $INUM instead. Example: $INAME(item1,1) When a group is specified in the extraction condition through a hierarchy format, it will be specified in the &record-name&field-name format. Example: $INAME(&RECORD&item1,1) --
$INUM()	Specify a field number inside parentheses. In the case of Excel information, specify a number from 1 to 10000. In the case of other than the above, specify a number from 1 to 1000. The item is converted to the specified field value. Example: $INUM(2)
$DNUM	Specify a dynamic numeric number. Specify a number from 01 to 20. The specified field corresponds to the -dnumXX parameter of the command for executing data processing. If you omit the corresponding parameter, 0 is applied. Example: If $DNUM01 is specified and the following data processing command (utled) is executed, 123 will be used in the dynamic value specified location of the numeric operation. utled -i sample -dnum01 123
$MINUM()	Inside parentheses, specify first the matching number for the matching file and then the item number, and separate the two numbers with a comma (,). The item is converted to the specified matching file field value. Example: $MINUM(1,2)
$LEN()	Specify a field name inside parentheses. The item is converted to the specified field output size. When occurrence numbers are specified, they are separated by a comma (,). 1 is applied when occurrence numbers are omitted or when a non-numeric value is specified. You can specify this item only if $LEN is output to a formatted type. Example: $LEN(item1)
$LENNUM()	Specify a field number inside parentheses. Specify a number from 1 to 1000. The item is converted to the specified field output size. You can specify this item only if $LENNUM is output to a multi format or a hierarchy format. Example: $LENNUM(2)
$RCOUNT	The item is converted to the current number of records in the input file.
$ACOUNT	The item is converted to the current total number of records, including cases where multiple records matched in the input file and matching file.
$HCOUNT()	Specify the matching number for the matching file inside parentheses. Specify a number from 1 to 101. The item is converted to the current number of records that matched in the matching file for 1 record in the input file. The counter is reset every time the input file record changes. Example: $HCOUNT(2)
$GCOUNT	The item is converted to the current number of grouped records in the input file. You can specify this item only if $GCOUNT is in a hierarchy format.
$VAR()	Specify a variable name inside parentheses. The item is converted to the specified variable. Example: $VAR(item1)

In numeric arithmetic formulas, you cannot specify left angle brackets (<), right angle brackets (>), double quotation marks ("), single quotation marks ('), or ampersands (&).

= Remarks =

If data of variable length cannot be retained, the data length can be retained in a custom field by using $LEN or $LENNUM, as in the following cases:

When output without setting an output Delimiter value
When output through Variable length field reference

The following is an example of a comprehensive numeric operation:

($INAME(item1,1)+$INAME(item2,2))*$DNUM02

This formula denotes a value derived by adding the values of field item1 (occurrence number 1) and field item2 (occurrence number 2) and then multiplying the sum by dynamic value 02.

Note

For specifications that contain basic arithmetic operation formulas, the output format follows the conversion format of type F. Therefore, for the formatted items, it is recommended to specify numeric types (type 9, type P, and type S), or allocated character types of 29 bytes or more. Also, for the input data type of specifications (1, $INUM(1), etc.) that do not contain basic arithmetic operation formulas, the numeric specification is type 9 and the field name (number) specification is other field types.
Specify the integral part in 19 digits or less and the decimal part in 9 digits or less. An error occurs if exceeded values are specified.

If the integral part of the calculation result exceeds 19 digits, a conversion error occurs.

If the decimal part of the calculation result exceeds 9 digits, the exceeded part will be truncated.

Table 2.42 Date Formula
Field	Description
Base point	Specifies the date that serves as the base point.
Date literal	Specify in the format YYYY/MM/DD hh:mm:ss. You can omit the time. If you do so, 00:00:00 is assumed.
today, now	Displays the data processing start date and time. The same value is applied for all records.
tomorrow	Displays 24 hours after today.
yesterday	Displays 24 hours before today.
$INAME()	Refers to the input field value. Depending on the field value, you can use this as a base point or a numeric value. Specify a field name inside parentheses. When occurrence numbers are specified, they are separated by a comma (,). 1 is applied when occurrence numbers are omitted or when a non-numeric value is specified. $INAME cannot be specified when Format in the input settings is CSV with no ID specified. Use $INUM instead. Example: $INAME(item1,1) When a group is specified in the extraction condition through a hierarchy format, it will be specified in the format &record-name&field-name. Example: $INAME(&RECORD&item1,1)
$INUM()	Specify a field number inside parentheses. In the case of Excel information, specify a number from 1 to 10000. In the case of other than the above, specify a number from 1 to 1000. The item is converted to the specified field value. Example: $INUM(2)
$DNUM	Refers to a dynamic value. Depending on the value, you can use this as a base point or a numeric value. Add a dynamic value number and specify in a format from $DNUM01 to $DNUM20. The specified field corresponds to the -dnumXX parameter of the command for executing data processing (utled). If you omit the corresponding parameter, 0 is applied.
$MINUM()	Depending on the field value, you can use this as a base point or a numeric value depending on the field value. Inside parentheses, specify first the matching number for the matching file and then the item number, and separate the two numbers with a comma (,). The item is converted to the specified matching file field value. Example: $MINUM(1,2)
$DSTR	Refers to a dynamic character string value. Depending on the value, you can use this as a base point or a numeric value. Add a dynamic character string value number and specify in a format from $DSTR01 to $DSTR20.The specified field corresponds to the -dstrXX parameter of the command for executing data processing (utled). If you omit the corresponding parameter, a halfwidth space is applied.
$VAR()	Specify a variable name inside parentheses. The item is converted to the specified variable. Example: $VAR(item1)
Operation	Performs addition and subtraction of date data.
+ (plus) - (minus)	Performs addition and subtraction of date data. Placed in front of the number of days/hours.
ago	Performs subtraction of date data the same way as a minus (-) but is placed after the number of days/hours.
next	Same as +1.
last	Same as -1.
Numeric value	Specify a numeric value. It always comes with Unit.
Unit	It always comes with Numeric value. You can use the following units: year, month, fortnight, week, day, hour, minute, second

Note

If you use $DNUM or $DSTR as a base point, regardless of whether or not you specify an input format for dates, DataMagic reads date data in the format determined automatically by DataMagic.

= Remarks =

The base point is always specified only once within the date formula.

When calculating base points, use the JULIANDAY function. For details about the JULIANDAY function, see DataMagic Operation Manual.
You can include an operation multiple times.

You cannot use ago multiple times.

When tomorrow or yesterday, and ago are specified at the same time, they are interpreted as follows: (if today is 2008/07/25)

Table 2.43 Examples of Date Operations
Date formula	Results	Interpretation
tomorrow 1 day ago	2008/7/23	tomorrow = today +1 day and 1day becomes 2 day ago
yesterday 1 day ago	2008/7/25	yesterday = today -1 day and 1day becomes 0 day ago

Time zone correction is not performed. Calculation is always made in local time.
You cannot specify Japanese calendar units (year, month, day, week, hour, minute, second) as operation units.

If specified, the operation results are not guaranteed.
Do not insert a space between the plus (+) or minus (-) sign and the numeric value.
You cannot use a hyphen (-) immediately after a base point that contains a time. Use ago instead.
When a date operation is performed in Variable Assignment Settings, only year, month, and day are output as the result data.
Hours, minutes, and seconds are not calculated in date operations in Variable Assignment Settings. If you specify hours, minutes, or seconds, the result is not guaranteed.

= Remarks =

Examples of date operations:

2007/10/1

->2007/10/1 00:00:00

$INAME(koumoku1,1) +1 month +2 day

The duration of 1 month and 2 days expressed by $INAME(field1,1)

is the same as the expression

today +3 day -1 hour 30 minute ago

->The time 3 days, 1 hour, and 30 minutes before expressed by today

is the same as the expression today +3 day 1 hour ago -30

$DSTR01 + $DNUM01 day

->When utled -i sample -dstr 2007/10/01 -dnum01 1 is executed, it is the same as 2007/10/01 +1 day.

Value list

This displays a list of values that you can use in formulas.

Query value category

If Query value is selected for Type, specify this item.

Select either Fixed character string or Dynamic character string (runtime argument).

Query value (when Fixed character string is selected for the Query value category)

Specify the SQL statement. When a 2-byte character is contained in the field name or table name, enclose it in square brackets ([ and ]).

Note

To specify the virtual table name and virtual table field name using an SQL statement, enclose them in square brackets, [ ]. If you specify a SQL statement in fixed queries, operations with virtual table names, and virtual table field names not enclosed inside [ ] are not guaranteed.

Example: select [col1] from [vt]

Query value (when Dynamic character string (runtime argument) is selected for the Query value category)

Specify a number from 01 to 20.

Virtual table name

Displays the virtual table name set in the virtual table settings.

You can select the SQL statement table name from this list.

Field name

Displays the virtual table settings field name specified in the Virtual table name.

From this list, select fields that were set by using the SQL-statement SELECT clause or WHERE clause.

Value list (when a query value is selected)

From this list, select fields that were set by using the SQL statement SELECT clause or WHERE clause.

Table 2.44 Query Value Settings (SQL Template Display)
Function Classification	SQL statement	Function Description
Summation	select max([column_name]) from [table_name]	Returns the maximum value from among the specified fields (values).
	select min([column_name]) from [table_name]	Returns the minimum value from among the specified fields (values).
	select sum([column_name]) from [table_name]	Returns the sum total of the specified fields (values).
	select count([column_name]) from [table_name]	Returns the total number of records in the table.
Average	select avg([column_name]) from [table_name]	Returns the average of the specified fields (values).
Record number	select count([column_name]) from [table_name]	Returns the number of rows in the specified fields (values).
Summation (conditional)	select max([column_name]) from [table_name] where [column_ name] = 'value'	Returns the maximum value from among the specified fields (values). (Specified conditions)
	select min([column_name]) from [table_name] where [column_ name] = 'value'	Returns the minimum value from among the specified fields (values). (Specified conditions)
	select sum([column_name]) from [table_name] where [column_ name] = 'value'	Returns the sum total of the specified fields (values). (Specified conditions)
Average (conditional)	select avg([column_name]) from [table_name] where [column_ name] = 'value'	Returns the average of the specified fields (values).
Number of records (conditional) Record number	select count([column_name]) from [table_name] where [column_ name] = 'value'	Returns the number of rows in the specified fields (values). (Specified conditions)
Read-ahead	select [column_name] from [table_name] where dm_rec_count = (select dm_rec_count from [table_name] where [column_name]='value' order by dm_rec_count)+1	Returns the row after the specified field.
Dummy table operation	select hex('value')	Returns hexadecimal data for the specified value.

Table 2.45 Virtual Table Query Value Settings (Variables that can be Dynamically Specified)
Field	Description
$VAR()	Specify a variable name inside parentheses. The item is converted to the specified variable. Example: $VAR(item1)
$INAME()	Specify a field name inside parentheses. The item is converted to the specified field value. When occurrence numbers are specified, they are separated by a comma (,). 1 is applied when occurrence numbers are omitted or when a non-numeric value is specified. $INAME cannot be specified when Format in the input settings is CSV with no ID specified. Use $INUM instead. Example: $INAME(item1,1)
$INUM()	Specify a field number inside parentheses. Specify a number from 1 to 999. The item is converted to the specified field value. Example: $INUM(2)
$DNUM	Specify a dynamic numeric number. Specify a number from 01 to 20. The specified field corresponds to the -dnumXX parameter of the command for executing data processing. If you omit the corresponding parameter, 0 is applied. Example: If $DNUM01 is specified and the following data processing command (utled) is executed, 123 will be used in the dynamic value specified location of the numeric operation. utled -i sample -dnum01 123
$DSTR	Refers to a dynamic character string value. Depending on the value, you can use this as a base point or a numeric value. Add a dynamic character string value number and specify in a format from $DSTR01 to $DSTR20. The specified field corresponds to the -dstrXX parameter of the command for executing data processing (utled). If you omit the corresponding parameter, a halfwidth space is applied.
$MINUM()	Inside parentheses, specify first the matching number for the matching file and then the item number, and separate the two numbers with a comma (,). The item is converted to the specified matching file field value. Example: $MINUM(1,2)
$RCOUNT	The item is converted to the current number of records in the input file.

No-hit behavior

If the corresponding data is 0, you can select a behavior.

The settings specified when Common components is selected for Type.

Table 2.46 The settings specified when Common components is selected for Type
Field	Description
Field name	Selects the field to be added.
ID	Click the Browse button, and from the Select Common Component Information screen that appears, specify a Common Component ID. If you cannot obtain the specified Common Component ID, an error occurs.
Comment	Displays the comment about the common component information.
Arguments	These are the arguments of the common component information. Only the necessary number of arguments from the common component information are displayed.