Config file functions
- Michelle Cabaya (Unlicensed)
The config file supports several functions to allow data to be manipulated prior to being imported.
<field> represents the data item mapping syntax required for the configured Delimiter
Routed ASN and On Demand Configuration
Routed ASN configuration supports the following.
| Function | Details |
|---|---|
| NOW | The current timestamp at the time of processing, see Macro Data Items For Cloud customers, the current time will be in Arizona time (MST). |
| CXTNOW | The current timestamp in cxtAsp at the time of processing, see Macro Data Items |
Transformations for NOW and CXTNOW functions
When using the transformations you must use the format Item=Macro&OffsetHours:OffsetMinutes|Transformation Example Any import that comes in after 2:59 pm will be pushed to tomorrow. | |
| TODAY | Macro the current date at the time of processing with the time set to 12:00 AM, see Macro Data Items |
| GUID | Creates a new Guid and stores in field |
| 0, | Static text identifier, following text will be entered into the field |
| REPLACE | REPLACE(<field>;<text to look for>;<text to put in>), do not add spaces around the semicolons. Replace can also be nested.append( REPLACE(REPLACE(<FIELD>;<text to look for>;<text to put in>)<second text to look for>;<second text to put in>) IIF |
| NZ | Return a non null value if the field is null, NZ(<test field>;<field to use if test field is null>) |
| SUBSTR | SUBSTR(<field>;<0 based start index>;<length>), do not add spaces around the semicolons. It does not support text values in the first parameter. It has to be a field. Starting in 18.0.1, the SUBSTR function has been modified to work like this Assuming that field 1 has the following text: “ABCDEFGHIJ” SUBSTR(1;0;4) - Will return ABCD SUBSTR(1;0;65) - Will return ABCDEFGHIJ - this will return up to 65 characters SUBSTR(1;65;3) - Will return an empty string because the start index is past the end of text. SUBSTR will no longer report an error if called on a field that is empty. |
| IIF | Inline if compares first and second fields to check for equality, IIF(<field operand 1>;<field operand 2>;<field to return if true>;<field to return if false>) For X Dispatch 12.0.5 or older, this only handles one check at a time and cannot be nested within itself (without the use of the NESTED function). Example: IIF (X;Y;Z;IIF(...)) |
ARITHMETIC | (<operand1>;<operation>;<operand2>) Arithmetic performs a simple mathematical operation on two operand parameters. The operation in the 2nd parameter is applied to the two operand parameters. Each input parameter is evaluated as a field, just like with IIF. If you want to hardcode a value, wrap it in double quotes (Example: “*”) for delimited or XML and preface with 0, (EX: 0,*) for fixed-width (this mirrors the behavior of IIF). All operands are treated as integers (whole numbers). Available operations:
Examples Divide the value of field 15 by the integer value 16 in a delimited file. Add the values of fields 2 and 3 together in a delimited file. Take values 10-12 in the line and raise them to the power of values 20-22 in the l line of a fixed-width file. For fixed-width files, you must use the “0,” to denote the operation. Subtract the value of element “discount” from element “price” in an XML file. Multiple operands can be used in the ARITHMETIC function by nesting additional functions. |
| APPEND | Takes a string of functions or strings and builds one string from results. Using the APPEND function does not work when using the NESTED function. Use of the IIF function within the NESTED function instead. When using the APPEND function with a CSV file, do not reference column 0 (as column 0 does not exist in a CSV file format; the column count starts at 1). If column 0 is referenced, the import will work but an error is thrown. Correct syntax: APPEND(3,32,) Incorrect syntax: APPEND(4,32,0) |
| NESTED | Takes a string of functions or strings and processes values to generate one value Nested( Length of parameter 2, String to be processed ) |
| PARSEDATE | Take a field and parse the date with an output date format. PARSEDATE(<field>|NOW|TODAY[modifications];<input format>;<output format>) Do not add spaces around the semicolons. For X Dispatch 20 and older, the input format must match the string that is created. Function parameters:
DateTime Modifier (&)- Takes the number of days, hours, minutes, seconds, milliseconds to add to the datetime that is parsed, values can be positive or negative PARSEDATE(<field>|NOW|TODAY;<input format>;<output format>)&dd:hh:mm:ss:ffff The following example parses in a date, adds 8 hours, then formats the output in 24 hour time: (field 16 data is "20100110 05:47 PM") PARSEDATE(16;yyyyMMdd hh:mm tt;yyyy-MM-dd HH:mm)&00:08:00:00:00 output: "2010-01-11 00:00:00.000" Hardcoded Time PARSEDATE(16;yyyyMMdd hh:mm tt;yyyy-MM-dd 00:00:00.000) output: "2010-01-11 00:00:00.000" Literal T PARSEDATE(16;yyyyMMdd hh:mm tt;yyyy-MM-ddT00:00:00.000) output: "2010-01-11T00:00:00.000" After 4:00 pm the date will return the next day PARSEDATE(16;yyyyMMdd hh:mm tt;yyyy-MM-dd)&00:08:00:00:00 output: "2010-01-11" Add 30 minutes PARSEDATE(16;yyyyMMdd hh:mm tt;yyyy-MM-dd HH:mm:ss)&00:00:30:00:00 output: "2010-01-10 18:17:00" Subtract 30 minutes PARSEDATE(16;yyyyMMdd hh:mm tt;yyyy-MM-dd HH:mm:ss)&00:00:-30:00:00 output: "2010-01-10 17:17:00" NOW, "1/8/2010 3:49:45.0533 PM" PARSEDATE(NOW;;yyyy-MM-dd HH:mm:ss)&00:00:-30:00:00 output: "2010-01-08 15:19:45" NOW, "1/8/2010 3:49:45.0533 PM" with single quotes The characters inside single quotes are treated as literals PARSEDATE(NOW;;'yyyy-MM-dd HH:mm:ss' yyyy-MM-dd HH:mm:ss)&00:00:-30:00:00 output: "yyyy-MM-dd HH:mm:ss 2010-01-08 15:19:45" |
| XPATH | (XPath Expression) XPath allows for the standard XPath expressions to be utilized for normal XML based functions. Important note is that this function operates at the RELATIVE path to where the node is being processed. IF you have an OrderNode specifed, then this will be ORDER specific. Same for Parcel specification.. To get a list of possible function a search online will afford the needed functions Available starting in X Import 13.3.3 Example XPATH(count(Container/Container) ) Get the count of containers ( relative to node path order/Parcel ) XPATH(sum(Container/Pieces)) Add pieces together |
The different delimiters also support the following
Delimited
; - concat
0
; - concat
XML
APPENDALLCHILDREN - appends data from all child nodes ex: rsSecondary|ParcelReference2=APPENDALLCHILDREN(./MultiInvoice$InvoiceNumber) - appends the InvoiceNumber attribrute from each MultiInvoice child to a comma delimited string and stores in ParcelReference2
, - concat