Complex Derived Column Pattern with Cache Sink Lookup

Question

I'm not sure if this is a bug or a limitation but I have a some what complex Derived Column transform using a Column Pattern and Cache Sink to map/set the values of multiple columns.

Basically, for each column found using a cache sink lookup I need to match the current columns value to an array of complex types from the cached sink and return the code property.

Source

-------------------------------------------------
| id | title   | handle-time   | return-window  |
|----|---------|---------------|----------------|
| 1  | Shirt   | 1 to 2 days   | 7 days         |
| 2  | Pants   | 3 to 5 days   | 14 days        |
------------------------------------------------- 

Desired Output

-------------------------------------------------
| id | title   | handle-time   | return-window  |
|----|---------|---------------|----------------|
| 1  | Shirt   | 1-2d          | 7d             |
| 2  | Pants   | 3-5d          | 14d            |
-------------------------------------------------

Cached Sink

[
    {
        "code": "handling-time",
        "values": [
            {
                "code": "1-2d",
                "label": "1 to 2 days"
            },
            {
                "code": "2-3d",
                "label": "2 to 3 days"
            },
            {
                "code": "3-5d",
                "label": "3 to 5 days"
            },
            {
                "code": "5-7d",
                "label": "5 to 7 days"
            }
        ]
    },
        {
        "code": "return-window",
        "values": [
            {
                "code": "7d",
                "label": "7 Days"
            },
            {
                "code": "14d",
                "label": "14 Days"
            }
        ]
    }
]

Everything is working as expected until I try and use the following snippet in the "Value expression" of the Column Pattern.

find(ValueCodes#lookup($0).values, #item.label === toString($$)).code

When I replace $$ with a hard-coded value of '1 to 2 days' the correct code value '1-2d' is returned and the column value is updated as expected.

If simply set the "Value Expression" to $$ the value '1 to 2 days' is returned as expected.

It appears that within the find function $$ does not resolve.

Any thoughts or suggestion would be greatly appreciated.

Thank you!

Answer 1

Hello and welcome @Scott Alvis .

I'm not sure whether you simplified the examples or not, but from what I see, this substitution could be done by string replacement.

From your data I see _to_ is replaced by - , and days is replaced by d.
For this transformation, a simple string replace is much better than listing out all the possible ranges of days and keeping a mapping. Also much less maintenance.

Could you please let me know if this solution sounds appropriate, or if your actual mapping is much more complex.

replace(    replace([handle-time], " to ", "-"),    " days","d")  

replace([return-window], " days", "d")