Table of Contents
GET_KVSTORE_VALUE
Compatible with:
- Spreadsheet Worksheets
- Dynamic Worksheets
- GET_KVSTORE_VALUE(KVSTORE_ID, KEY1, KEY2, ...): string/int
Gets a value from a Key/Value Store in QBench by the Key/Value Store ID and one or more keys inside the Key/Value Store.
Args
- KVSTORE_ID: This is the ID of the Key/Value Store inside QBench. This can be found on the Key/Value Store detail page or on the "Key/Value Store" tab on the Spreadsheet Worksheet detail page. NOTE: It is important to wrap the ID in double quotes (ex: "66fc4cd0-6e85-45ca-9a03-5ae1e0e712d2" NOT '66fc4cd0-6e85-45ca-9a03-5ae1e0e712d2')
- KEY, ...: Any number of "KEY" arguments can be passed to retrieve any value at any layer inside your Key/Value Store.
Example
Suppose we have a Key/Value Store in QBench has the following values (see the two images below)
We can access these values inside a Spreadsheet Worksheet using GET_KVSTORE_VALUE.
1. Add the Key/Value Store to the Spreadsheet worksheet from the Spreadsheet Worksheet Config page.
2. Next, we will need to copy the Key/Value Store ID from the table where we added the Key/Value Store to the Spreadsheet Worksheet
3. Now, we can go to the Spreadsheet Worksheet "Configuration" tab and retrieve data from our Key/Value Store using our formula. If I wanted to retrieve what the color of a "banana" is from our Key/Value Store, our formula would look like this:
=GET_KVSTORE_VALUE("66fc4cd0-6e85-45ca-9a03-5ae1e0e712d2", "banana")
This will return "yellow", which is stored in the key "banana"
Example:
NOTE:
- You can also use other cells inside this function, for example:
- You can also retrieve values inside of other objects in your Key/Value Store. For example, given my Key/Value Store:
If I wanted to retrieve the value "green" inside the "apple" key, I would use the Formula like so
=GET_KVSTORE_VALUE("66fc4cd0-6e85-45ca-9a03-5ae1e0e712d2", "apple", "grannysmith")
This would return the value "green".
QB_TEXTJOIN
Compatible with:
- Spreadsheet Worksheets
(Note: For dynamic worksheets, use TEXTJOIN)
- QB_TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...): string
Combines the text from multiple strings and/or arrays, with a specifiable delimiter separating the different texts.
Args
- delimiter: A string, possibly empty, or a reference to a valid string. If empty, text will be simply concatenated.
- ignore_empty: A boolean; if TRUE
, empty cells selected in the text arguments won't be included in the result.
- text1 - Any text item. This could be a string, or an array of strings in a range.
- text2, ... [optional]: Additional text item(s).
Example
=QB_TEXTJOIN(",", TRUE(), A1:E1)
=QB_TEXTJOIN(",", FALSE(), A1:E1)
=QB_TEXTJOIN(",", TRUE(), A1:E1, "Another", "Value")
Observe the previous examples in a worksheet
QB_XLOOKUP
Compatible with:
- Spreadsheet Worksheets
(Note: For dynamic worksheets, use XLOOKUP)
- QB_XLOOKUP(search_key, lookup_range, result_range, missing_value, match_mode, search_mode): string
The QB_XLOOKUP
function returns the values in the result range based on the position where a match was found in the lookup range. If no match is found, it returns the closest match.
Args
- search_key: The value to search for. For example, 42
, "Cats"
, or B24
.
- lookup_range: The range to search in. This range must be a singular row or column.
- result_range: The range to consider for the result. This range's row or column size should be the same as the lookup_range, depending on how the lookup is done.
- missing_value [optional #N/A
by default ]: The value to return if no value in the lookup_range matches the search_key.
- match_mode [optional - 0
by default]: The manner with which to find a match for the search_key
- 0
is for an exact match.
- 1
is for an exact match or the next value that is greater than the search_key.
- -1
is for an exact match or the next value that is lesser than the search_key.
- 2
is for a wildcard match.
- 3
is for a regex search.
- search_mode: [optional - 1
by default] The manner with which to search through the lookup_range.
- 1
is to search from the first entry to the last.
- -1
is to search from the last entry to the first.
- 2
is to search through the range with binary search. The range needs to be sorted in ascending order first.
- -2
is to search through the range with binary search. The range needs to be sorted in descending order first.
Example
Given we have the following table
The following are examples and their outputs
Input: =QB_XLOOKUP("banana", A1:A4, B1:B4)
Output: 1
Input: =QB_XLOOKUP("orange", A1:A4, B1:B4)
Output: 3
Input: =QB_XLOOKUP("peach", A1:A4, B1:B4)
Output: #N/A
Input: =QB_XLOOKUP("peach", A1:A4, B1:B4, "No Value Found!")
Output: No Value Found!
Given we have the following table
The following are examples and their outputs
Input: =QB_XLOOKUP("banana", A1:D1, A2:D2)
Output: 1
Input: =QB_XLOOKUP("orange", A1:D1, A2:D2)
Output: 3
Input: =QB_XLOOKUP("peach", A1:D1, A2:D2)
Output: #N/A
Input: =QB_XLOOKUP("peach", A1:D1, A2:D2, "No Value Found!")
Output: No Value Found!
QB_SIGFIG
Compatible with:
- Spreadsheet Worksheets
- Dynamic Worksheets
- QB_SIGFIG(value, sig_fig_count): string
The QB_SIGFIG
function returns the value in the first argument with significant figures equal to the value in the second argument.
Args
- value: The value to with which to add significant figures to.
- sig_fig_count: The number of significant figures to add to the value.
Examples:
INPUT: =QB_SIGFIG(1539, 1)
OUTPUT: 2000
INPUT: =QB_SIGFIG(0.345006, 4)
OUTPUT: 0.3450
INPUT: =QB_SIGFIG(-15.75, 3)
OUTPUT: -15.8
INPUT: =QB_SIGFIG(1539, 6)
OUTPUT: 1539.00
Comments
0 comments
Please sign in to leave a comment.