Xactly Connect 2017-02 New Features

This article describes the new feature available in Xactly Connect 2017-02, which include:

  • SFDC SOQL Function Enhancements
  • Person and Title Upload Enhancements
  • New String, Math, Date and Time Functions
  • New ODBC Driver 1.1.7

SFDC SOQL Function Enhancements

Xactly Connect 2017-02 includes the following SFDC SOQL function enhancements:

Support for case insensitive column names

SFDC column names in the SOQL function are now case insensitive. For example:

select Name, Id from SFDC(SOQL='SELECT nAmE,iD FROM Account',UserName='foo',Password='bar',Environment='Production', RowLimit=5)

Support for parent child relationships

Xactly Connect now supports the ability to reference child objects using sub-queries in SOQL select statements. For example:

select ListPrice from SFDC(SOQL='SELECT Amount, (SELECT ListPrice FROM OpportunityLineItems) FROM Opportunity WHERE Name = 'Opp_1',UserName='foo',Password='bar',Environment='Production', RowLimit=5)

Support for SOQL functions

Xactly Connect now supports references to SOQL functions in SOQL statements. For example:

select expr0 as average_amount from SFDC(SOQL='select AVG(Amount) from Opportunity',UserName='foo',Password='bar',Environment='Production', RowLimit=5)

Person and Title Upload Enhancements

Xactly Connect adds the following new columns to the Person and Title objects (these changes require a reprovision):

  • Person: prorated_salary and prorated_personal_target
  • Title: category_other and function_other

Note that these updates were introduced in the Incent 2016-02 release and are now available for Xactly Connect Person and Title uploads with the current release.

New String, Math, Date and Time Functions

Xactly Connect adds the following string and math functions:

CapFirstLetters

Capitalizes the first character of the input value, splitting words by the supplied delimiter. The default delimiter is a comma.

CapFirstLetters(<value>[, <delimiter string>])

For example:

select CapFirstLetters('abc') from Empty(); //Returns 'ABC'
Compare

Compares two values using XSQL comparators (the values can be different data types). The function returns:

  • -1 if value1 < value2
  • 0 if value1 == value2
  • 1 if value1 > value2

Note that two null values are considered equal.

Compare(value1, value2)

For example:

select Compare(1,2) from Empty();    //Returns -1
DiffTime

Returns a long value representing the difference between the supplied time values, in the specified time-units. The default time-unit is SECONDS.

DiffTime(<input1>, <input2> [, time_unit string])

Valid time_unit values include the following:

  • MILLIS
  • SECONDS
  • MINUTES
  • HOURS
  • DAYS
  • WEEKS
  • MONTHS
  • YEARS

For example:

select DiffTime(ToDateTime('2016-01-01 10:10:10'), ToDateTime('2016-01-01 10:10:11')) from Empty();    //Returns 1
GatherString

An aggregate function that groups all the values into a single string of values separated by the supplied separator. The default separator is ‘,’ .

The function essentially transposes a row to a column. Note that the function limits number of characters to 65536.

GatherString(value [,separator])

For example:

select GatherString(select name || '_' || employee_id from xactly.xc_participant where name like '%deltanu%', ' | ') from Empty()
MaxValue

Returns the maximum value from the supplied list. This is a scalar function that returns one value per row. Null is not compared but can be returned if all values are null.

The return value can change depending on the input values, so if you want a particular
type returned, convert the return value to the appropriate type. MaxValue(int,double) can return an integer or a double for each row.

MaxValue(value1, ...)

For example:

select MaxValue(1,2,3,4,5) from Empty();    // Returns 5

MinValue

Returns the minimum value from the supplied list. This is a scalar function that returns one value per row. Null is not compared but can be returned if all values are null.

The return value can change depending on the input values, so if you want a particular
type returned, convert the return value to the appropriate type. MinValue(int,double) can return an integer or a double for each row.

MinValue(value1, ...)

For example:

select MinValue(1,2,3,4,5) from Empty();    // Returns 1
CountMatches

Returns the number of occurrences of the search pattern.

CountMatches(<string>, <search_pattern>)

For example:

select CountMatches('abacad', 'a') from Empty();    //Returns 3
ToQwerty

Removes all non QWERTY characters from the input string. If additional characters are present in a string form, the returned string will contain any QWERTY character plus any character in the additional string.

The output string will contain only QWERTY characters and characters supplied in the optional string.

ToQwerty(<string> [,additional])

For example:

select ToQwerty('ÉABC') from Empty();    //Returns ABC
ToName

Removes all non alphabetic characters, digits, and underscores, and includes all additional characters in the optional string.

ToName(<string> [,additional])

For example:

select ToName('A_$B') from Empty(); //Returns A_B
CountWeekdays

Counts the number of weekdays between the two input dates. The count includes both the start and end dates if the dates fall on weekdays.

CountWeekdays(date1, date2)

For example

select CountWeekdays(ToDate('2017-01-01'), ToDate('2017-01-02')) from Empty();    //Returns 1

New ODBC Driver 1.1.7

Xactly Connect features a new version of the ODBC driver that includes performance enhancements and supports our new centralized API management service. The previous version 1.0.5 will continue to be supported until the April 21st, 2017 release.

Contact Xactly Support following the February release to download the new version of the driver.