FINDITEM of first 3 character in List

CHSJ
CHSJ
in Modeling

Hi,

I have a module with say a Product dimension consisting of 3 Letter product codes, e.g. APP, ORG, PEA. I would like to find these items in a list where the Names in the List are 5 Letters e.g. APPUS, ORGUS, PEAUS. How am I able to lookup APP against the list of APPUS (first 3 letter) and return ÀPPUS?

Tagged:

Answers

  • TonyViGrand
    TonyViGrand

    Hi @CHSJ 

     

    Please refer the below screenshots, the formulas are available in screenshots.

     

    Note: The list used in target module is "List 1" and the list used in Mapping module is "List 2".

     

    TonyViGrand_0-1644666473432.png

    TonyViGrand_1-1644666514809.png

     

    Hope this helps.

     

    Regards,

    Tony

  • CHSJ
    CHSJ

    Hi Tony,

     

    Thanks for the quick response. How about if it is the other way around? I.e. List 1 having 5 characters?

     

    Thanks much!

  • TonyViGrand
    TonyViGrand
    If that is the case, then its easy. You can put the formula as
    finditem('List 2', left(name(item('List 1')),3))

    Hope this helps.
  • TonyViGrand
    TonyViGrand
    You can put the above formula in the target module line item itself. You don't need the mapping module I created.
  • CHSJ
    CHSJ

    Hi Tony,

     

    I am probably doing it wrong as I got an error. Below is a mock up of List 1, List 2 and the Module.

    What is the formula to get the desired result below? Thank you.

     

    CHSJ_0-1644691740622.png

     

  • TonyViGrand
    TonyViGrand

    Hi @CHSJ 

     

    I will give below my solution once again in steps based on the mock up you have shared.

     

    1) First create another module and lets name it as "StagingModule" dimensioned by List 1.

    2) Create two line items and lets name it as "LineItem1 & "LineItem2".

    3) The format of LineItem1 will be List 1 formatted line item.

    4) The format of lineItem2 will be List 2 formatted line item.

    5) In LineItem1, the formula should be ITEM('List 1')

    6) In LineItem2, the formula should be FINDITEM('List 2',left(name(item('List 1')),3)).

    7) Now back to your original module showed in the mock where you want the result. You can put the formula in the product line item as 'StagingModule'.'LineItem1'[FIRSTNONBLANK:'StagingModule'.'LineItem2'].

     

    See to that when you input the formula you put the brackets correctly otherwise you might get the formula invalid error. If any error, please do share the error screenshots and blueprint view of the module and formula you have put.

     

    Hope this helps.

     

    Regards,

    Tony

  • CHSJ
    CHSJ
    Hi Tony, will try this out. Thank you.
  • MarkWarren
    MarkWarren

    If you can, put the 3 letter part as the code of the list, that way the FINDITEM would work with either the 5 or 3 letter variants.

Categories