/
Config file functions

Config file functions

Related Articles:

Search related import documentation

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

Better Code Block

Routed ASN and On Demand Configuration

Routed ASN configuration supports the following.

Function

Details

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

  • 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.

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. 

Better Code Block

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. 

It does not support text values in the first parameter. It has to be a field.

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

Better Code Block

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

Better Code Block

Divide the value of field 15 by the integer value 16 in a delimited file.

Better Code Block

Add the values of fields 2 and 3 together in a delimited file.

Better Code Block

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.

Better Code Block

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

When using APPEND with CSV columns, you need to add a comma at the end of the column specifier.

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

This will not place a space in between. The trailing comma is necessary.



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.

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