NOW

The current timestamp at the time of processing, see Macro Data Items 

CXTNOW

The current timestamp in cxtAsp at the time of processing, see Macro Data Items

Transformations for NOW and CXTNOW functions

• DAYFLOOR - Sets the resulting time to 00:00 for any calendar day.

• DAYUPPER - Sets the resulting time to 00:00 and adds one day.

• WEEKDAYFLOOR - Sets the resulting time set to 00:00 for Monday through Friday only and does not include Saturday and Sunday. If the after adding the OffsetHours and OffsetMinutes is on a Saturday or Sunday, the resulting time will be the following Monday at 00:00.

Example

Any import that comes in after 2:59 pm will be pushed to tomorrow. 

See File Format (General) - Macro Data Items.

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. 

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>) 

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:

• +: Addition (EX: 2 + 3 = 5)

• -: Subtraction (EX: 5 - 3 = 2)

• *: Multiplication (EX: 2 * 3 = 6)

• /: Division (EX: 6 / 3 = 2)

• ^: Exponentiation (EX: 2 ^ 3 = 8)

• >: Comparison (EX: 2 > 3 = 3) * if true it will return the first value, if false the second

• <: Comparison (EX: 2 < 3 = 2) * if true it will return the first value, if false the second 

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.

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.
Append( Length of parameter 2, String to be processed )

Using the APPEND function does not work when using the NESTED function. Use of the IIF function within the NESTED function instead. 

NESTED Example

Your data is "one","two","three" and want to return the value in the second column.

Recommended syntax: NESTED(12,IIF(2;2;2;2))

Wrong syntax: NESTED(13,APPEND(3,2,0))

XML Example

Example

Data:  COW,DOG,CAT,BIRD,MOOSE,FISH,FLOWER

Append Statement:  APPEND(6,Store-);APPEND(2,5,);APPEND(1,-);APPEND(2,6,)

Outputs:  Store-MOOSE-FISH

Example of Output

4/18/2012 12:00:00 AM | 23370 Road 22 | D1N

CSV Example

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>)

Function parameters:

• <field> - Field containing date data. Supports modifications on the NOW and TODAY values.

• <input format> - String representation of the format of the date in the <field> parameter.

  • This segment can be left blank, which will allow the function to process any valid inbound date/time string. For example: PARSEDATE(5;;yyyy-MM-dd)

• <output format> - Optional. String representation of the format of the output. 

  Date Format Strings

  Example date 2008-03-09 16:05:07.123

  • Year - y yy yyy yyyy  ex: 8 08 008 2008

  • Month - M MM MMM MMMM  ex: 3 03 Mar March

  • Day - d dd ddd dddd  ex: 9 09 Sun Sunday

  • Hour - h hh H HH  ex: 4 04 16 16

  • Minute - m mm  ex: 5 05

  • Second - s ss  ex: 7 07

  • Millisecond - f ff fff ffff  ex: 1 12 123 1230

  • Millisecond without padded zeroes - F FF FFF FFFF  ex: 1 12 123 123

  • AM/PM - t tt  ex: P PM

  • TimeZone - z zz zzz  ex: -6 -06 -06:00

  Most other characters are treated as literals, y M d h m s f F t z can be treated as literal characters if they are inside single quotes

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. An 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 functions a search online will afford the needed functions

Example

XPATH(count(Container/Container) )

Get the count of containers ( relative to node path order/Parcel )

XPATH(sum(Container/Pieces))

Add pieces together