# Priceloop Function Library

## Numeric Functions​

### AVERAGE​

`average(value1, value2, ...)`: Returns the mean of a set of numbers.

Example:

``average(1, 3)``

### MAX​

`max(value1, value2, ...)`: Returns the maximum value of a set of numbers.

Example:

``max(1, 3)``

### MEDIAN​

`median(value1, value2, ...)`: Returns the median of a set of numbers.

Example:

``median(1, 3, 9)``

### MIN​

`min(value1, value2, ...)`: Returns the minimum value of a set of numbers.

Example:

``min(1, 3)``

### EXP​

`exp(number)`: Euler's number e (~2.718) raised to a power.

Example:

``\exp(2)``

### ROUND​

`round(column, decimal_digits)`: Rounds a number to the defined digits after the decimal point.

Example:

``round(column1, 2)``

## Numeric Operators​

`+`: Addition of numbers.

Example:

``Column1 + Column2``

### SUBSTRACTION​

`-`: Subtraction of numbers.

Example:

``Column1 - Column2``

### MULTIPLICATION​

`*`: Multiplication of numbers.

Example:

``Column1 * Column2``

### DIVISION​

`/`: IEEE floating point division of numbers.

Example:

``Column1 / Column2``

## String Functions​

### CONCAT​

`concat(column1, separator1, column2, ...)`: Concatenates two or more columns with a specified Separator.

Example:

``concat(column1, “-“, column2)``

## Comparison Operators​

### LESS THAN​

`<`: Less than.

Example:

``Column1 < Column2``

### GREATER THAN​

`>`: Greater than:

Example:

``Column1 > Column2``

### LESS THAN OR EQUAL TO​

`<=`: Less than or equal to.

Example:

``Column1 <= Column2``

### GREATER THAN OR EQUAL TO​

`>=`: Greater than or equal to.

Example:

``Column1 >= Column2``

### EQUAL TO​

`=`: Equal to.

Example:

``Column1 = Column2``

### NOT EQUAL TO​

`!=`: Not equal to.

Example:

``Column1 != Column2``

### ISNULL​

`isnull(expression)`: Checks if the expressions resolves to null. If it resolves to null, it returns true, otherwise false.

Example:

``isnull(column1)``

### ISNOTNULL​

`isnotnull(expression)`: Checks if the expressions resolves to something other than null. If it resolves to something other than null, it returns true, otherwise false.

Example:

``isnotnull(column1)``

## Control Flow and Join​

### IF​

`if(logical_expression, value_if_true, value_if_false)`: Returns one value if a logical expression is TRUE and another if it is FALSE.

Example:

``if(a > 10, 1, 0)``

### AND​

`and(logical_expression1, logical_expression2)`: Returns true, if all of the provided arguments are true, and false if any of the provided arguments are false.

Example:

``and(a = 1, b = 2)``

### OR​

`or(logical_expression1, logical_expression2)`: Returns true if any of the provided arguments are logically true, and false if all of the provided arguments are logically false.

Example:

``or(a = 1, b = 2)``

### JOIN​

`join(key_current_table, table_to_join, key_other_table, column_to_get, aggregation)`:

Looks up data of a column from another table into the current table. Based on a defined key (identifier) on which to merge the data on.

Available aggregations are:

• text [min, max, concat]
• number [sum, average, median, min, max]
• date [min, max, median, average]
• boolean [and, or]

Example:

``join(table1, id, table2, table1_id, column2, max)``

## Date Functions​

### YEAR​

`year(date)`: Extracts the year from a date.

Example:

``year(@2022-01-01)``

### MONTH​

`month(date)`: Extracts the month from a date.

Example:

``month(@2022-01-01)``

### DAY​

`day(date)`: Extracts the day from a date.

Example:

``day(@2022-01-01)``

### DATE​

`date(YYYY, M, D)`: Converts string input of year, month and day into a date.

Example:

``date(2022, 1, 1)``

## Competitor Tracking Functions​

### \GETPRICEAPI​

`\getpriceapi(gtin, country, source, api_key)`: Returns product and offers data from Price API in json

Country: DE Source: idealo, google_shopping API key: get your API key from Price API

``\getpriceapi("0190198457035", "DE", "google_shopping", "api_key")``

### \JSONQUERY_NUM​

`\jsonquery_num(json_document, search_path)`: Returns number value based on the search_path. We use JMESPath as a query language for JSON. More info here: https://jmespath.org/.

Example:

``\jsonquery_num("{"a": {"b": {"c": {"d": "10"}}}}"", "a.b.c.d")``

### \JSONQUERY_STRING​

`\jsonquery_string(json_document, search_path)`: Returns string value based on the search_path. We use JMESPath as a query language for JSON. More info here: https://jmespath.org/.

Example:

``\jsonquery_string("{"a": {"b": {"c": {"d": "Value"}}}}"", "a.b.c.d")``

## Amazon Functions​

`\amazonbuyboxlistprice(asin, marketplace)`: Returns the list price of the buybox winner of the specified amazon product.

Supported Marketplaces: AE, BE, DE, PL, EG, ES, FR, GB, IN, IT, NL, SA, SE, TR, UK, AU, JP, SG, US, BR, CA, MX

⚠️ You need to authorize us to connect to your Amazon data first

Example:

``\amazonbuyboxlistprice("B083FCVCMC", "DE")``

### \AMAZONFEEESTIMATE​

`\amazonfeeestimate(asin, marketplace, price, is_fba)`: Calls an external Amazon API and returns an estimated fees attached to selling the product on Amazon for the given price.

Supported Marketplaces: AE, BE, DE, PL, EG, ES, FR, GB, IN, IT, NL, SA, SE, TR, UK, AU, JP, SG, US, BR, CA, MX

⚠️ You need to authorize us to connect to your Amazon data first

Example:

``\amazonfeeestimate("B083FCVCMC", "DE", 200, true)``

## Mock Functions​

### \GENERATEMOCKDATA​

`\generatemockdata(string_value, faker_provider)`: Returns mock data based on the Faker library. As an input you need to generate a list of string values (e.g. “1”, “2, “3”) as this would trigger the computation.

Supported Faker values:

person = ["first_name", "first_name_female", "first_name_male", "last_name", "last_name_female", "last_name_male", "name"] address = ["address", "building_number" "city", "city_suffix", "country", "country_code", "postcode", "street_address", "street_name"] company = ["company"] job = ["job"] phone_number = ["phone_number"] internet = ["company_email", "email", "free_email"]

Example:

``\generatemockdata("1", "name")``