@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
value a key a
value b key b
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.
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:
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.
Daniel Allen,Daniel Allen
Dan Allen,Daniel Allen
D Allen,Daniel Allen
these are my lookup values
DA,Daniel Allen,Daniel-Allen,Dan Allen,D Allen,Daniel
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
Daniel Allen Allen, Daniel Allen <====== ! why has it return "Daniel Allen Allen" ? I even tried encapsulating my lookup val as "Daniel Allen"
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
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 :)
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):