Value Range Functions (CO, PF)

ABS: To determine the absolute value of a number.

Objective

• To determine the absolute value of a number.

Information

• The ABS function provides the absolute value of a number. The absolute value of a number is its value without prefix.

Rule

• Syntax: ABS (Operand)

Example

• The expression ABS(-2) provides the value 2.

ATOI: Conversion of a String into a Numeric Value

Objective

• To convert a string (type X) into a numeric value.

Rule

• ATOI (<alpha numeric DI, variables>)

Example

Note

• If the content of the string cannot be interpreted as a number, the result of ATOI is the value 0.

CONST: Constants in Value Ranges

Objective

• To fetch a constant name, possibly on a language-dependent basis

Rule

• CONST (Language, Constant ID)

  • CONST: Function

  • Language: @19, 0000, 1000

  • Constant ID: ID of the constant

Example

Note

• The constant name is automatically trimmed.

EXIST: Existence Check without Relations

For information

• If it is not possible to carry out a check using relationships, the existence of a data item in a data table can still be checked using this function.

• If a corresponding record does not exist in the specified data table, any user entry is rejected with the Please check input message.

Rule

• EXIST (DI, DT)

  • EXIST: Function

  • DI: Target DI which is to be checked (usually DI with VR)

  • DT: DT in which it is checked for the existence of records.

Example

• The EXIST function to check for pre-existing records.

• EXIST function to check for non-existent records.

Note

• The check

  • can be done for IDs which are compiled from a DI;

  • does not have to check ID data items themselves, as these are checked via the relationship function;

  • can only access IDs with DB type = X.

  • cannot access compound IDs.

EXP: To exponentiate numbers

Objective

• To exponentiate numbers

Information

• The EXP function provides exponentiated values of a number.

Rule

• Syntax: EXP(Operand 1;Operand 2)

  • Operand 1 = Basis

  • Operand 2 = Exponent

Example

• expression EXP(2;3) provides the value 8.

• expression EXP(4;0.5) provides the value 2.

• expression EXP(-3;3) provides the value -27.

Notes

• The exponent cannot be a rational number. Fractions such as ½ must be replaced by the value 0.5.

• In the case of roots of positive numbers, only the positive number is provided. For example, the result of EXP(4;0.5) is +2 and -2. The software returns the value +2.

• Calculations with a negative basis are only permitted with whole-number exponents. Calculations with the EXP function should therefore only be carried out after conversion into an absolute value.

Expand: Returning the Value of a Variable

Objective

• To read out the current value of a variable

Example

Note

• Possible variables are "@xxx", "@Gxxx", "@Dxxx", and "@Uxxx"

FMTDATE: Conversion of a Date DI into a String

Objective

• To convert a date DI into a string, for example to include it in a character string

Rule

• FMT(date DI, <Format ID>)

  • Date DI: Date data item with DB type = N4

  • Format ID:

    • ID number of the format as a character string (with "") or

    • "" (blank string): Output in the default date format for the user interface (Date format)

FMT: Conversion of a Numeric DI into a String

Objective

• To convert a numeric DI into a string, so that it can, e.g., be included in a string

Rule

• FMT(DI, <format string>)

• Format string: "[-][W][.P][e|E]"

  • -: left-aligned (default: right)

  • W: min. width

  • P: max. number decimal place

  • E: Exponential display

Example

• Format --> result, value: 298123

  • "8.2" → " 298.12"

  • "-10" → "298.12 "

  • ".1" → "298.1"

Notes

• All numeric DB types are permitted.

• Corresponds to the man fprintf UNIX function.

FR: Appends a String

Objective

• To fetch an operand value and add in strings before and/or after the fetched value

Rule

• Fr(<character string>, <DIxxxxxx or @xx>, <character string>)

• Operands can be data items and variables.

• The character strings

  • can be of any length.

  • are trimmed beforehand.

Note

• This function cannot be used with I-texts.

IIFS: Determines Values in Accordance with a Condition

Objective

• To combine strings in different ways depending on conditions.

Rule

• IIFS (Condition, String1, String2)

  • Condition: logical expression, the result of which is either TRUE or FALSE.

  • String1: Value which the checking DI takes on if the condition is fulfilled.

  • String2: Value which the checking DI takes on if the condition is not fulfilled.

Notes

• The number of IIFS conditions in a value range construction has been increased to 15. Up to 15 IIFS conditions may be created.

• Applies only for X, AL fields, but not for numerical ones.

LENGTH: To determine the number of characters of a character string

Objective

• To determine the number of characters of a character string

For information

• The LENGTH value range functions provides the number of characters in a string.

• Blanks at the start and end are not counted.

Rule

• Syntax: LENGTH (Operand)

Example

• The expression LENGTH("12345678") provides the value 8.

Note

• The function can only be applied to strings ( DB type =X, Class =0). It cannot be applied to I-texts and continuous texts.

NB: Input Check for Alpha Data Item According to Numerical Rule (From, To)

For information

• An alphanumerical data item is converted into a number.

• The system then checks whether this number is between from and to.

Rule

• NB (data item, from, to).

Example

Note

• An input is rejected with the Please check input error message if the check

  • is not a valid number, or

  • the figure is not in the range of from/to.

NCHILD: Output of the Direct or Indirect Child Records

Note

• In 39 it is carried out via Python API

Information

• This function returns the number of immediate child records from the first child data area, in N4 format.

Rule

• NCHILD()

Example

NORMSINV: Quantiles of the Standard Normal Distribution

Information

• The NORMSINV function provides the quantiles of standard normal distribution.

Rule

• NORMSINV (Operand)

  • The operand is the probability belonging to standard normal distribution.

  • An operand <0 or >1 is not permitted.

Example

• The expression NORMSINV (0.75) provides the value 0.674490.

OBS: Object Protection Overwriting

Objective

• To overwrite object protection data items with the value specified.

Example

Rule

• OBS<OS share > (value for share1, value for share2, ...)

  • OBS: Function

  • OS part:

    • 1: Overwrites (rwd owner, rwd group, rwd any)

      • Example: OBS1 ("r--","r--","r--")

      • Result: After export, the records can no longer be changed or deleted

    • 2: Overwrites (creating user [1-4],creating user [5-8])

      • Example: OBS2 ("z", " ")

      • Result:

        • after they are exported, the system user for the records is "z".

        • in the corresponding modules, records can be searched for "without z". This means that the records with system user “z” are not displayed and can no longer be changed or deleted.

Notes

• This function

  • can only be used when exporting from the PLANTA software;

  • is assigned to virtual DIs with DB type X and DB length = 2 in the data tables in question.

• The data area which contains the DIs with these VR functions may not contain data fields in any other data tables.

• The records are changed directly within the database. As a result,

  • the object protection data fields which are to be changed are only updated after a new search;

  • any available exits are not executed.

PCT: Calculation of Deviation in Percent

Objective

• To calculate the percentage difference between two data items.

Rule

• PCT<DBtype> (DI-1, DI-2)

  • PCT: Function

  • DB type:

    • DB type of the data items specified in parentheses.

    • Possible values: N2, N4, N8

  • DI-1: e.g. actual value

  • DI-2: e.g. target value

Notes

• This function can only be used with DIs which have the same DB type.

• If a % deviation is to be applied between DIs with different DB types, then

  • a DI with SQL type = CS must be converted into one of the two DB types.

  • this value calculation must always be carried out with VR type = CO.

PERIOD: Determines a Period

Objective

• To determine the start or end date of a period relative to a date.

Information

• Function for determining a period:

  • Result

    • The date of the day which is the first or last day of the period determined by the parameters input for the function.

  • Possible application

    • Start and end dates for date bars

    • Start and end dates for date bars

    • As dynamic variables

    • As dynamic variable in value ranges

Details

• Starting from a given date, PPMS is to determine the start or end date of the period which contains this date, or of a preceding/following period:

  • calendar week containing the date

  • nth week before that (not implemented)

  • nth week after that (not implemented)

  • month containing the date

  • nth month before that

  • nth month after that

  • quarter containing the date

  • nth quarter before that

  • nth quarter after that

  • year containing the date

  • nth year before that

  • nth year after that

Rule

• PERIOD (<date>, <grid>, <date position>, <interval>)

  • Date

    • The date relative to which the period is to be determined.

    • This parameter can be a date data item or a dynamic, global, user or system variable which contains a date.

  • Grid

    • Defines the period of time for which the start or end date is to be found.

    • Values:

      • 3: week

      • 4: month

      • 5: quarter

      • 6: year:

  • Date position

    • Values:

      • 0: the start date is determined

      • 1: the end date is determined

    • This parameter can be a data item with DB type = N2, or user variable, or a dynamic, global variable, containing the value 0 or 1.

  • Interval

    • Defines which period is determined before or after.

    • Values:

      • 0: the period containing the date itself

      • < 0: Number of same periods before

      • > 0: Number of same periods after

    • This parameter can either be a data item with DB type= N2, or user variable, a dynamic, global variable, containing an integer.

Examples

RELPOS: Outputs the Position of a Record

Information

• This function returns the relative position of a record in the data table, in N4 format.

Note

• The function does not work in modules with structural sorting.

ROUND: Rounding Numbers

Information

• The ROUND function rounds a number to a definable number of places. The expression ROUND(12,3456;2), for example, provides the value 12.35.

Rule

• Syntax: ROUND(<value>;<number of decimal places>).

CTRL: Truncating a String

Objective

• To fetch the value an operand and to truncate a string at any required point.

Rule

• CTRL(<DIxxxxxx oder @xx>, start position, length)

  • Operands can be data items and variables.

  • Start position

    • Position within the operand string from which it is to be truncated.

    • From which the first operand position is counted incrementally.

  • Length

    • Number of characters cut off from the start position

    • Maximum length is the length of the operand

Example

SYSTEM: Opening the Program from the Software

For information

• This function may no longer be used.

• This function enables another program to be opened from within the program.

• The program is executed synchronously, i.e. the system waits until the program which was called terminates.

Rule

• SYSTEM(DI<DI number>)

  • DI<number>: the DI contains the command as in a shell input"cp file1 file2 1>&2 2&gt; cp.err$$".

  • To check only for errors, the comparison operand is appended after the parentheses e.g. SYSTEM( DI<DI number>) 0

Notes

• The SYSTEM function returns the value zero if successful; it should therefore be bound to a DI of the internal type integer. A text DI is required to specify the program to be executed, which includes the name and options of the program.

• If this value range is connected to a Y/N field named Error, the status is displayed as a text.

• The new process adopts the standard input and output of PPMS, and is therefore directly linked to the PPMS client. Output would therefore be sent to the client, so standard input, output, and errors must be redirected.

• The return value from SYSTEM corresponds with the return status (integer) of the program which is called-up.

• In order to distinguish the files produced in the rerouting of output, "$$" can be inserted into the file name. The two characters are replaced by the process number during execution.

TRIM: Removing Blanks

Objective

• To remove any blanks before and/or after a string.

Rule

• TRIM (Operand)

  • TRIM: Function

  • Operand: DI, variable, or constant ID

VH: Number of Records in a Child Data Table

Information

• This function displays the number of records for a superior (parent) record which exist in a child data table. This does not require that the module contains a data area for this data table.

Rule

• UF (DT, index, path, DI)

  • DT: Target data table the frequency of use of which is to be determined

  • Index: Data table specified in the ident of the target DT

  • Path: dependent on "variable name" from relation

    • 0: e.g. "ID425"

    • 1: e.g. "ID425A"

    • 2: e.g. "ID425B"

    • 3: e.g. "ID425C"

  • DI: Ident DI in the data table of the parent object which is to be used to search in child objects.

Note

• This function can be used with the following VR types: CS, CO, and CL.

Caution

• If the value range is constructed incorrectly, the Database error -52 error message is displayed in the module during the search, which must be confirmed for each data record found.

IKV: EXCEL Function for Financial Mathematical Calculations

Objective

• Using the EXCEL function IKV as value range function in PPMS to realize the financial mathematical calculations with PPMS-Customizing.

Rules

• IKV (value1, value2, value3,....,value12, estimated value)

• Algorithm: MS EXCEL, IKV function

• Input parameter

  • Value1 to value12

    • Values of payment series as costs (negative) and benefits (positive) in sequence of years.

    • There must be at least one negative and one positive value.

    • Permissible operators for values 1-12:

      • DIxxxxxx (numerical data items, DB type = N8)

      • @DIxxx (dyn. variables on numeric data items, DB type = N8

      • numeric constants, e.g. 0.0

    • If less than 12 values are required, the remaining values can be specified constantly with 0.0 in the value range.

  • Estimated value

    • See EXCEL help

    • 0, if not used. Then the system internally takes on "10%", see EXCEL function

  • Return value

    • Result of EXCEL-IKV

    • 999999.99, if calculation cannot be carried out (in Excel: #NUMBER!)

Example

• IKV(DI299101, DI299102, DI299103, DI299104, DI299105, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0)

Note

• The value range should not be used with value range type CO because then a recalculation of the IKV takes place after the entry of every field involved in the formula, which can take time because of the iteration process used.

Distance and Date Calculation

Objective

• To calculate the data or periods between two dates using the calendar, stored in PPMS

Details: Determination of the number of working days

• Calculate the number of working days between two dates

  • In order to calculate the number of the working days between two dates, use the following formula:

    • WD_BETWEEN([calendar],[date1],[date2])

Example 1:

• Number of working days between today and today + 100 days in the "BC" calendar

• Result:

  • WD_BETWEEN("BK",@15,@15+100)

Example 2:

• Number of working days between 01/01/1970 and today’s date in the "BC" calendar

• Result:

  • WD_BETWEEN("BK",0,@15)

Details: Date determination

• Determine the date using working days

  • For calculation using the number of the working days use the following formula:

    • WD_ TIMEINTERVAL ([calendar],[start date],[number])

Example 1:

• Today’s date + 5 working days stored in the "BC" calendar.

• Result:

  • WD_TIMEINTERVAL("BK",@15,5)

Example 2:

• Today’s date + number of the working days from DI000002+1 stored in the calendar from DI000001.

• Result:

  • WD_TIMEINTERVAL(DI000001,@15,(DI000002+1))