question

scott-alvis avatar image
0 Votes"
scott-alvis asked scott-alvis commented

Complex Derived Column Pattern with Cache Sink Lookup

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!

azure-data-factory
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @scott-alvis
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet .In case if you have any resolution please do share that same with the community as it can be helpful to others . Otherwise, will respond back with the more details and we will try to help .

Thanks
Martin

0 Votes 0 ·

1 Answer

MartinJaffer-MSFT avatar image
0 Votes"
MartinJaffer-MSFT answered scott-alvis commented

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")
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@MartinJaffer-MSFT - Thank you for your response. Unfortunately, the example has been simplified greatly. There are potentially 20-30 of different substitutions that could occur and the source data determines which ones need to happen.

0 Votes 0 ·