Replace Matches Function error - Function must return true/false?

DTAconsultingDTAconsulting Learner
edited March 16 in General discussion



  • Clinton JonesClinton Jones Experian Elite

    @DTAconsulting it looks like your message was truncated - was there a question here?

  • Thanks for letting me know Clinton.

    I'm trying to perform a basic lookup using a lookup table to match the input and return a value from lookup table.

    STEPS to reproduce

    1) Create a CSV of "key" / "value" pairs, e.g.

    2) Import as a Dataset

    3) Within a Transformation step use the Replace Matches function (eventually I want to create a custom function using a series of built in functions)

    4) Create a Parameter called "input" and provide comma delimited test values

    5) Lookup table - data from 2)

    6) Return column - "value"

    7) Match Type - Case-sensitive

    8) Input Type - Space-Separated

    9) Lookup column - "key"

    10) Input value - "input" (i.e. the Parameter) - eventually I want to be able to pass any value

    Expected result:

    Result input

    value a key a

    value b key b

    Actual result

    Error : Function must return true or false

    Next question is that I'd like to extend the function and make it re-usable. I'd like to feed the input value with a column from a table I supply at run time. e.g. the Function is used in a Transform or as part of Validate step where I supply my source data at run time, the column used as input to my function will be constant.

  • Josh BoxerJosh Boxer Administrator

    Validate step does require function to return a true or false value, but it sounds like you are on the right track for a Transform step.

    (realized this is back to front from your description, looking up values to return keys, but should provide an illustration!)

    (#10) In the Transform step when you select the function you built, you will also select the Input column, which will replace the parameter you created when building/testing the function

    Note that you could instead use your lookup CSV with a Lookup step in your Workflow:

    1. add a Source step with any dataset containing the "values" 
    2. add a second Source step with the csv containing your lookup table
    3. add a Lookup step and select the definition, such as find first matching 'value' and return 'Key'
    4. (if required) add a transform step to remove any columns no longer required
    5. (optional make this a re-usable workflow so that this can be added to other workflows in future)

    Finally to note that if the number of lookup values is small and constant then you could remove the need for a CSV lookup file and use the If then else function instead

  • Many thanks Josh,

    I feel a little foolish because the Lookup step was in fact staring me in the face. As I'd come from V1 last time I used a lookup (and pandora previously) I was looking for the Lookup on the Transformation step itself 😊

    I probably can now use the look up step once I'd done some manipulate of my input value to lookup, hence why I was looking at the function route.

    For my use case the input value needs to be extracted from a much bigger description. I am looking to create a list of extracted values and then see if any exist in my lookup table.

    I think I have made the error go away by creating the function from scratch.

    ** The other issue I'm now faced with is this:

    I want to return "Daniel Allen" as the value from this Lookup table.

    Lookup, Value

    DA,Daniel Allen

    Daniel Allen,Daniel Allen

    Daniel-Allen,Daniel Allen

    Dan Allen,Daniel Allen

    D Allen,Daniel Allen

    Daniel,Daniel Allen

    these are my lookup values

    DA,Daniel Allen,Daniel-Allen,Dan Allen,D Allen,Daniel

    **Expected result**

    Result, lookup val

    Daniel Allen, DA

    Daniel Allen, Daniel Allen

    Daniel Allen, Daniel-Allen

    Daniel Allen, Dan Allen

    Daniel Allen, D Allen

    Daniel Allen, Daniel

    **Actual result**

    Result, lookup val

    Daniel Allen, DA

    Daniel Allen Allen, Daniel Allen <====== ! why has it return "Daniel Allen Allen" ? I even tried encapsulating my lookup val as "Daniel Allen"

    Daniel Allen, Daniel-Allen

    Daniel Allen, Dan Allen

    Daniel Allen, D Allen

    Daniel Allen, Daniel

  • Josh BoxerJosh Boxer Administrator

    The 4th setting 'Input value' on the Replace Matches function, if you update from 'Space-separated' to 'Whole value' it should resolve this issue

  • 😊 Many thanks again Josh

  • With your help I have now managed to create a complex function and made re-usable.

    2 ways I solved this.

    Split (space separator/delimiter)--> Extract Matches as List --> Replace Matches

    Regular expression replace (search spaces, replace with comma --> Extract Matches as List --> Replace Matches

  • DTAconsultingDTAconsulting Learner
    edited March 16

    The 1st one actually appears to be more robust if input contains some noise (But possibly not with remove noise).

    Also I could just now feed the result of the  "Extract Matches as List" into a Lookup Step :)

  • Josh BoxerJosh Boxer Administrator
    edited March 17

    Great that you got this working. There is a library of pre-built functions that you can view and also download, which might be a good starting point when you build your next Function (there are a couple of examples that use a lookup):

Sign In or Register to comment.