Vlookup formula in List of Functions | Community
Skip to main content
J
jael91407431
Level 3
January 18, 2018

Vlookup formula in List of Functions

  • January 18, 2018
  • 4 replies
  • 15809 views

Hi,

Can I create vlookup or hlookup formula inside our workflow?

The List of Functions under Advanced Parameters doesn't contain either vlookup or hlookup functions:

As you know vlookup  (or Vertical lookup) searches down the first column of a range for a key and returns the value of a specified cell in the row found.

Syntax: VLOOKUP(lookup_value, Table_array, Col_index_num, Range_lookup)

So how can we create ad-hoc functions if they are not listed under List of Functions?

    This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

    4 replies

    V
    vendimb15716755
    Level 4
    January 18, 2018

    hi

    you can create your own function following the guidelines here:  https://docs.campaign.adobe.com/doc/AC/en/CFG_API_Adding_additional_SQL_functions.html#General_structure_of_package_to_import

      J
      jael91407431Author
      Level 3
      January 18, 2018

      HI,

      The link is broken. Can you provide the correct link?

      J
      jael91407431Author
      Level 3
      January 18, 2018

      I found it, the correct link:

      Adding additional SQL functions

        J
        jael91407431Author
        Level 3
        January 18, 2018

        Hi,

        In the documentation, it points to a repository of functions inside Campaign:

        "Further function examples can be found in the predefined package "xtk\datakit\funcList.xml":

        Where in Campaign can I find the folder, "xtk\datakit\funcList.xml"; as I can't find "datakit" schema inside the "Data Schemas" folder:

        Adhiyan
        Adobe Employee
        AdhiyanAdobe Employee
        Adobe Employee
        January 18, 2018

        Hello Jae,

        It actually refers to the location in the server where this funclist.xml is present. It's under datakit/xtk/eng folder on the server.

        from the console you can simply search for funclist under schemas and it should be there.

        Regards.

        Adhiyan

        Adhiyan
        Adobe Employee
        AdhiyanAdobe Employee
        Adobe Employee
        January 18, 2018

        The directory path is referred to the server itself not in the Campaing

        the func list is on the data schema but not sure what are you looking for there. if you are trying to write the functions please follow the document


        Hi Jae,

        Try searching with funcList instead of funclist as it's written in camel casing

        Regards,

        Adhiyan

        J
        jael91407431Author
        Level 3
        January 19, 2018

        Hi,

        So I followed the documentation and formulated the following XML to be imported as a package into Campaign:

        Here is the raw code:

        <?xml version="1.0" encoding='ISO-8859-1' ?>

        <package

          namespace  = "nms"

          name        = "package-additional-funclist"

          label      = "Additional functions"

          buildVersion= "7.0"

          buildNumber = "8857">

          <entities schema="xtk:funcList">

            <funcList name="myList" namespace="jhl">

              <group name="ehl_functions" label="EHL Functions">

                <function name="vlookup" type="long" args="(&lt;vlookup&gt;)" help="Vertical (v)lookup searches down the first column of a range for a key and returns the value of a specified cell in the row found" minArgs="4" maxArgs="4" display="Vlookup will define based on parameters and arguments">

                  <providerPart provider="SQL, MSSQL,Sybase,PostgreSQL" body="vlookup(lookup_value, Table_array, Col_index_num, Range_lookup)"/>

                </function>

              </group>

            </funcList>

          </entities>

        </package>

        ...which follows, I think, the documentation:

        Here is the template code Adobe provides in the documentation, those blocks of code to be replaced, I've highlighted in red:

        <?xml version="1.0" encoding='ISO-8859-1' ?>

        <package

          namespace = "nms"

          name = "package-additional-funclist"

          label = "Additional functions"

          buildVersion= "6.1"

          buildNumber = "10000">

          <entities schema="xtk:funcList">

          <funcList name="myList" namespace="cus">

          <group name="date" label="Personalized date">

          <function name="relativeMaturity" type="long" args="(&lt;Âge&gt;)" help="Returns the difference between a date and 18 years"

          minArgs="1" maxArgs="1" display="Relative maturity of the person born on the date $1">

          <providerPart provider="MSSQL,Sybase,PostgreSQL" body="extract(year from age($1))-18"/>

          </function>

          </group>

          </funcList>

          </entities>

        </package>

        So my raw code replaces replaces the Adobe's template sample with my own code (in red):

        <package

          namespace  = "nms"

          name        = "package-additional-funclist"

          label      = "Additional functions"

          buildVersion= "7.0"

          buildNumber = "8857">

          <entities schema="xtk:funcList">

            <funcList name="myList" namespace="jhl">

              <group name="ehl_functions" label="EHL Functions">

                <function name="vlookup" type="long" args="(&lt;vlookup&gt;)" help="Vertical (v)lookup searches down the first column of a range for a key and returns the value of a specified cell in the row found" minArgs="4" maxArgs="4" display="Vlookup will define based on parameters and arguments">

                  <providerPart provider="SQL, MSSQL,Sybase,PostgreSQL" body="vlookup(lookup_value, Table_array, Col_index_num, Range_lookup)"/>

                </function>

              </group>

            </funcList>

          </entities>

        </package>

        Yet, when I try to load, (import as a package)  I am getting this error:

        Can anyone define what's wrong with either the code (presuming someone went there before--loading customized functions into Campaign) or anything else? Did I formulate the <body> tag correctly with the right syntax?

        Vlookup syntax is the following: vlookup(lookup_value, Table_array, Col_index_num, Range_lookup)

          J
          jael91407431Author
          Level 3
          February 1, 2018

          So the syntax is relatively simple:

          <package>
          <entities schema="xtk:funcList">
          <funcList name="myList" namespace="jhl">
          <group name="ehl_functions" label="EHL Functions">
          <function name="vlookup" type="long"
          args="(&lt;vlookup&gt;)" help="Vertical (v)lookup
          searches down the first column of a range for a key and returns the value of a
          specified cell in the row found" minArgs="4"
          maxArgs="4" display="Vlookup will define based on four (4)
          arguments">
          <providerPart provider="SQL, MSSQL,Sybase,PostgreSQL" body="vlookup($lookup_value,
          $Table_array, $Col_index_num, $Range_lookup)"/>

          Where $lookup_value, $Table_arry, $Col_index_num, $Range_lookup would be the actual xpath fields (i.e., columns or fields) that represents the values ($xpath field (attribute or column) for lookup_value, $xpath field (attribute or column) for Table_array, $xpath field (attribute or column) for Col_index_num, $xpath field (attribute or column) for Range_lookup).

          Can someone validate this based on their own experience?

          florentlb
          florentlb
          Level 10
          February 20, 2018

          Any update on this?

          Florent

          Terms & ConditionsAccessibility statement

          Loading footer...