KV
The key/value object (KV
) is a way to pass parameters to multiple functions. It's often used in conjunction with lookup functions, where you need to specify multiple conditions to retrieve data from a data source.
The key/value object consists of pairs of keys and values, where the keys are strings and the values can be of any data type. The keys are used to identify the corresponding values, and are often used in lookup operations to specify the conditions for retrieving data.
The FROM function can be used to override data source properties by passing in a key/value object with a Command key, which is set to the SQL query that should be executed. This allows you to customize the SQL query used to retrieve data from a data source.
Syntax
KVAL KV(object key, object value)
KVAL KV(string key, string value)
KVAL KV(int key, int value)
KVAL KV(int key, string value)
KVAL KV(string key, int value)
Examples
Custom SQL Query
Suppose you want to retrieve data from a database using a custom SQL query. You can use the FROM function with a key/value object to accomplish this as shown below:
LOOKUPA("CompanyName",FROM(KV("Command","SELECT SupplierID, CompanyName FROM Suppliers")), KV("SupplierID",SupplierID))
In this example, the FROM function is used with a key/value object that specifies the SQL query to execute (SELECT SupplierID, CompanyName FROM Suppliers). The LOOKUPA function is then used to retrieve the CompanyName from the resulting data set based on the SupplierID value.
Chaining Conditions
Suppose you want to retrieve data from a data source based on multiple conditions. You can use the key/value object to chain conditions together as shown below:
LOOKUPA("CompanyName","Suppliers",KV("SupplierID",SupplierID),KV("Country","UK"))
In this example, the LOOKUPA function is used to retrieve the CompanyName from the Suppliers data source based on two conditions: SupplierID and Country. The key/value object is used to specify the values for each condition, with the SupplierID set to a variable and the Country set to a string value.