Skip to main content
Submitted

Easily reference "current field" in SQL snippets and custom transformations

  • August 1, 2017
  • 7 replies
  • 1 view
S
Forum|alt.badge.img

In many transformations you need a reference to the "current field". Example a tranform in [Field A] could be:

coalesce(nullif( [Field A], N''), N'Unknown')

In this case, it would be a huge improvement of we could something like the following instead of having to assign a parameter. Especially when working with SQL snippets it would greatly improve usability as it allows drop-in snippets that don't need parameterization.

coalesce(nullif( [CURRENT_FIELD], N''), N'Unknown')

It should be noted that in the case of a self-reference, having an actual parameter has little benefit anyway as a self reference has no lineage impact

 

    7 replies

    S
    Forum|alt.badge.img
    • support
    • Known Participant
    • August 1, 2017

    Agree. If this could be generalized you could easily drag & drop field transforms with conditions that point to the field itself.

    That way the condition would be more like 'When I am Empty (or not Empty or Equal to) etc... and can be quickly transferred to other fields.

    S
    Forum|alt.badge.img
    • steven.koppenol
    • Author
    • Known Participant
    • August 1, 2017

    Indeed, in the conditions section this also makes a lot of sense.

    B

    Agree!

    A
    Forum|alt.badge.img
    • andri
    • Known Participant
    • April 26, 2018

    Self-referencing snippet or a parameter along with self-referencing condition would save some time when replacing empty fields with a default value. Then you would be able to add it to one field and then drag the transformation with the condition to all the other fields,

    D
    Forum|alt.badge.img
    • doug.wynkoop
    • Known Participant
    • April 30, 2018

    Hi everyone,

    We do have a kind of "Current Field" option, which is the non-custom transformations.  In Stuart's example, you could avoid custom transformations altogether by using a fixed transformation and a "not empty" condition, which is what we'd recommend folks use whenever possible. 

    Also, the parameterization doesn't do anything for lineage/impact analysis, but it does allow you to change the field name without causing any SQL errors.

    However, I agree that this would be incredibly useful for situations where a custom script action in required.  One use case I've seen for this is converting date strings from odd, non-SQL Server compatible data sources into a date format SQL Server can parse.  Since they had to implement this transformation for every single date field, it meant doing dozens of script action transformations.  Being able to just apply the transformation with [CURRENT_FIELD] instead of having to use the parameter function would have been a huge time saver.

    Up-voting this for sure!

    S
    Forum|alt.badge.img

    Hi Doug, being able to put the builtin conditions (for example) "not empty" on a CURRENT_FIELD-like placeholder would be fantastic indeed! Then when we copy the transform+condition to other fields the condition, being a self-reference, will automatically work.

    A
    • Alex Wijnen
    • Participating Frequently
    • February 12, 2021

    Thanks for your product feedback.

    We have passed your input to our R&D department for further evaluation.

    The roadmap of our product is focused on a split in ODX, MSW, Semantic Layer and Customer Portal.

    With that split we will see new features and functions appear that might exceed your expectations or make the request obsolete.

    Terms & ConditionsCookie settingsAccessibility statement