EXAMPLE - Filtering Strings into Objects
You can create nested objects by filtering strings. In this example, column headers and column values are nested into a single entity in a new column of Object data type.
Functions:
Item | Description |
|---|---|
FILTEROBJECT Function | Filters the keys and values from an Object data type column based on a specified key value. |
PARSEOBJECT Function | Evaluates a String input against the Object datatype. If the input matches, the function outputs an Object value. Input can be a literal, a column of values, or a function returning String values. |
Source:
The following table shows a series of requests for inventory on three separate products. These are rolling requests, so inventory levels in the subsequent request are decreased based on the previous request.
date | reqProdId | reqValue | prodA | prodB | prodC |
|---|---|---|---|---|---|
5/10/21 | prodA | 10 | 90 | 100 | 100 |
5/10/21 | prodC | 20 | 90 | 100 | 80 |
5/10/21 | prodA | 15 | 75 | 100 | 80 |
5/11/21 | prodB | 25 | 75 | 75 | 80 |
5/11/21 | prodA | 5 | 70 | 75 | 80 |
5/11/21 | prodC | 30 | 70 | 75 | 50 |
5/12/21 | prodB | 10 | 70 | 65 | 50 |
You must create a column containing the request information and the inventory level information for the requested product after the request has been fulfilled.
Transformation:
The five data columns must be nested into an Object. The generated column is called inventoryLevels.
Transformation Name |
|
|---|---|
Parameter: Columns | reqProdId,reqValue,prodA,prodB,prodC |
Parameter: Nest columns to | Object |
Parameter: New column name | inventoryLevels |
You can then build the inventory response column (inventoryResponse) using the FILTEROBJECT function:
Transformation Name |
|
|---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | filterobject(parseobject(inventoryRequest), ['reqProdId','reqValue',reqProdId]) |
Parameter: New column name | inventoryResponse |
Results:
The inventoryResponse column contains the request information and the response information after the request has been fulfilled.
date | reqProdId | reqValue | prodA | prodB | prodC | inventoryLevels | inventoryResponse |
|---|---|---|---|---|---|---|---|
5/10/21 | prodA | 10 | 90 | 100 | 100 | {"reqProdId":"prodA","reqValue":"10","prodA":"90","prodB":"100","prodC":"100"} | {"reqProdId":"prodA","reqValue":"10","prodA":"90"} |
5/10/21 | prodC | 20 | 90 | 100 | 80 | {"reqProdId":"prodC","reqValue":"20","prodA":"90","prodB":"100","prodC":"80"} | {"reqProdId":"prodC","reqValue":"20","prodC":"80"} |
5/10/21 | prodA | 15 | 75 | 100 | 80 | {"reqProdId":"prodA","reqValue":"15","prodA":"75","prodB":"100","prodC":"80"} | {"reqProdId":"prodA","reqValue":"15","prodA":"75"} |
5/11/21 | prodB | 25 | 75 | 75 | 80 | {"reqProdId":"prodB","reqValue":"25","prodA":"75","prodB":"75","prodC":"80"} | {"reqProdId":"prodB","reqValue":"25","prodB":"75"} |
5/11/21 | prodA | 5 | 70 | 75 | 80 | {"reqProdId":"prodA","reqValue":"5","prodA":"70","prodB":"75","prodC":"80"} | {"reqProdId":"prodA","reqValue":"5","prodA":"70"} |
5/11/21 | prodC | 30 | 70 | 75 | 50 | {"reqProdId":"prodC","reqValue":"30","prodA":"70","prodB":"75","prodC":"50"} | {"reqProdId":"prodC","reqValue":"30","prodC":"50"} |
5/12/21 | prodB | 10 | 70 | 65 | 50 | {"reqProdId":"prodB","reqValue":"10","prodA":"70","prodB":"65","prodC":"50"} | {"reqProdId":"prodB","reqValue":"10","prodB":"65"} |