Why this function is not working when using replace matches?

Marco_13112001
Marco_13112001 Member
edited December 2023 in General

Good afternoon,

Could you please help me to undertand why the following function isn't returning the desired outcome.

image.png

Here is the what it should be doing:

  • Looks if the combination of two columns have a match between the tables i.e. I’m using the match table 1 - Job title/table 2 - position name and table 1 - job reference/table 2 - BM reference.
  • If there is a match then the column called “Correct BM reference” receives BM Reference from table 2
  • If there isn’t a match than called “Correct BM reference” receives BM Reference from table 1

The problem is that it is retuning the positions/job titles and not the job references, as showed in the preview box.

Am I missing something?

Many thanks again for your help

Marco

Tagged:

Best Answer

  • Josh Boxer
    Josh Boxer Administrator
    Answer ✓

    So in your Result column there should be some IDs further down the list?

    Taking the first result you are sharing, the value 'Public Affairs Manager' does not exist in the table Job Profile Data.

    Hopefully this example highlights. Given a simple lookup table:

    image.png

    Then replacing the values bob and john:

    image.png

    Above i wrote: "select the And and then '(x) Make variable'." I should have said select Contains Match then Make Variable 'Job title exists' (could also do the same for the other Contains Match 'Ref exists').

Answers

  • Josh Boxer
    Josh Boxer Administrator

    Hi Marco

    I think the issue is that none of your jobs are matching, so the Result is coming from the else and returning the input value with nothing replaced. To check if this is the issue, select the And and then '(x) Make variable'. A column will be added to the Preview table, which will help you see the result mid-function and if that part of the Function is working as expected.

    The Function library has an example where Job titles will match more robustly:

    https://community.experianaperture.io/discussion/571/job-title-match-key-%E2%80%8D-%E2%80%8D

    ====

    Another thought, if a record has code 014 job title A and another record has code 014 but job title Z is that an issue? It could be an idea to Concatenate the two colums and look them up together against a reference set

  • Marco_13112001
    Marco_13112001 Member
    edited April 2023

    @Josh Boxer Thanks for your reply, I've just did a count on the matches by isolating the first part of the function (the AND containing the two blocks of Contain Matches) and I got a total of 698 of 1500 records with a true match.

    So, for this ones the idea is to have the BM reference coming from the table 1 and the "else" should get the result of the Replace Matches, where I'm matching the job titles/org names and if yes then replacing the null with the job reference from the lookup table. I believe this is similar scenario with the one you described in your question.

    Is the Replace Matches logic wrong?

Categories