Available Operations for Import Specifications
The following table contains a list of available
operations when defining import specifications. The operations are
in upper case letters and the arguments in lower case letters:
| Operation name | The operation |
|---|---|
| ADD nval | Adds nval (a value) to the current input. |
| ALPHA | Converts a numerical value into an alphanumeric value; IBM® Cognos Controller converts automatically when necessary; if the value is not valid, it will be printed in the error log. |
| DIVIDE BY nval | Divides the current input by nval (a value). |
| DROP ch | Skips all characters up to the character ch EXAMPLE: operation: DROP ; input: XXX;YYY output: YYY |
| GETITEM n sep | Gets item number n from the current input with the separation character sep; can be a Trigraph symbol. |
| GETITEMEXPR | Loads the expression in a field. |
| GETITEMLOOP | Loads values for each loop. The operation is displayed but can only be created using the Import Specification Wizard. |
| GETLINE n | Loads the contents of the n row from the import file. |
| IIF expr val1 val2 | If the expression expr is true, return val1, otherwise return val2 |
| INSBYMASK chs mask | inserts the characters in chs based on mask in current input EXAMPLE: operation: INSBYMASK - XX_XX_XX input: 990902 output: 99-09-02 |
| LEFTALIGN | Left-aligns current input. |
| LEFT n | returns the first n characters from current input |
| LOCALCURRENCY | Loads the local currency for the company code in the current input according to the company database from the company table |
| LOOKUP lookup table on no match exclude/default | Loads values from a lookup table, where input is used as a search key. The search is carried out in the "From" column in the lookup table. If the input is found in the From column, the value in the To column will be returned. If the input is not found in the From column, the predefined value, if defined, will be returned. Otherwise the row will be excluded when import is performed. |
| LOOKUPFACTOR lookup table on no match exclude/default | Loads the value from the lookup table's "Factor" column. The search is carried out in the "From" column in the lookup table. If the input is found in the From column, the value in the Factor column will be returned. If input is not found in the From column, the predefined value, if defined, will be returned. |
| LOOKUPSPLIT lookup table breakpoint separator | Determines the breakpoint for when a field on the Fields (2) tab should be converted to different codes, depending on the total value. Example: The account code A should remain as A if the aggregated value for account A is positive, but if the aggregated value is negative, the account code should be converted to B. Operation: LOOKUPSPLIT Argument 1: X Argument 2: 0 Argument 3: ; Lookup table X is defined like this: From To A B; A |
| LOWERCASE | Converts input from uppercase to lowercase. |
| MASK mask | Carries out the opposite operation to INSBYMASK. EXAMPLE: operation: MASK XX_XX_XX input: 99-09-02 output: 990902 |
| MULTIPLY BY nval | Multiplies current input by nval (a value). |
| NDROPLEFT n | Numeric, deletes n characters from the beginning of current input. |
| NDROPTRAILIN n | Numeric, deletes n characters from the end of current input. |
| NUM | Carries out the opposite operation to ALPHA. |
| PACK ch | deletes all repeating characters ch from the current input string EXAMPLE: operation: PACK C input: ACCDCECC output: ACDCEC |
| PADDLEFT ch n | Indicates how many characters the whole string will contain and what character will be used to fill up to the left to reach this number. |
| PADDRIGHT ch n | Indicates how many characters the whole string will contain and what character will be used to fill up to the right to reach this number. |
| PREFIX str | Adds str in front of the input. |
| RELPERIOD arg1 arg2 arg3 | Operation defines the relative period for non-cumulative data. Determines the relative period based on the period in the current input, a positive integer for the offset of the period and the offset actuality. EXAMPLE: The operation uses #INPUT as argument1 which is the origin used for the field REL_PERIOD on the Fields (1) tab. Argument2 is the offset from the period in #INPUT. For instance, an argument2 set to 1 denotes monthly data whilst an argument2 set to 3 would denote quarterly data. Argument3 is the offset actuality. The offset actuality is particularly important when dealing with incremental weekly data. Operation: RelPeriod Argument: #INPUT Argument: 1 Argument: AC Example: Operation IIF Argument 1: #INPUT = 1 Argument 2: AC Argument 3: BU |
| REPLACE str1 str2 | Replaces all occurrences of str1 with str2 in the input. |
| REVERSE | Converts the string to the opposite order. |
| RIGHT n | Returns the last n characters from the current input |
| RIGHTALIGN | Right-aligns input. |
| ROUND n | Rounds the current input to n decimal places. |
| SELECTIF criteria | Includes the current input if criteria is true. If the input is not included it is shown on an error report. |
| SKIPIF criteria | Carries out the opposite function to SELECTIF. |
| SQUEEZE ch | Deletes all occurrences of the character(s) ch in current input EXAMPLE: operation: SQUEEZE " input: "a" "text" "where" "everything" output: a text where everything |
| STRIP ch | Deletes all characters ch at the beginning and end of the current input. |
| STRIPLEADING ch | Deletes all characters ch at the beginning of the current input. |
| STRIPTRAILING ch | Deletes all characters ch at the end of the current input. |
| SUBSTRING pos n | Loads n characters from the pos position |
| SUBSTRINGEXPR | Loads values in which parameters can be expressions. |
| SUBSTRINGLOOP | Loads values for each loop. The process is displayed but can only be created using the Import Specification Wizard. |
| SUBTRACT | Subtracts sequential numbers or parameters from input. |
| SUFFIX str | Adds str after the input. |
| TAKE ch | Carries out the opposite operation to DROP. |
| TRANSLATECHARACTERS chr1 chr2 | Translates all characters chr1 into corresponding characters in chr2 in current input. EXAMPLE: operation: TRANCHARS ¤#" odn input: L¤n#o" output: London |
| UPPERCASE | Changes input to upper case. |