need to do a look up formula

hinamahmood1994
hinamahmood1994
in Integrations and extensibility

Hi, 

 

I need to do a lookup in my line items so that I can import the data into a module. 

 

I have a flat hierarchy factory list. I have 2 line items Inbound Qty and Lot Numbers. 

 

I've split them so that I can see: 

Factory 1 inbound

Factory 1 Lot Number

Factory 2 Inbound 

Factory 2 Lot Number 

 

I'm not sure what formula to use in order to split the data now the way I need it to be done. Also, my Lot Numbers are text formatted and Inbound are Number formatted. 

 

Thanks, 

 

Hina 

 

Tagged:

Best Answer

Answers

  • nathan_rudman
    nathan_rudman

    Hi,

     

    A LOOKUP is used to make a link between two modules that have different lists. In your case it seems you have only one list, Factory, so I'm not sure I understand what is your problem.

     

    Do you have a flat list of inbound transactions and you are trying to convert that into a module that has Factory as a list so you can see the inbound by factory ?

    Are you working in a module or in the list itself ? Could you show use a screenshot of your source and of your target ?

  • hinamahmood1994
    hinamahmood1994

    Hi, 

     

    So the lot data test attachment is where my data is currently pulling in from a list upload. This module needs to be imported into my second attachment.

     

    The reason I'm looking to split the data in the first attachment is so that line items can match. Alternatively, I guess I could do 4 different saved views by factory, I don't want to do that. I think a formula can the issue. Not sure if it's a sum? 

     

    Hina 

  • nathan_rudman
    nathan_rudman
    I dont see any attachment
  • hinamahmood1994
    hinamahmood1994

    really ?? It's attached... doing it here again. 

  • nathan_rudman
    nathan_rudman

    You want to bring the total values of inbound for each factory in your "mapping" module, is that correct ?

     

    In your source, you need to have a line item called "Factory" formatted as your factory list. I think that's you module 2.

    Then in your target module, where you want to bring the information by factory, you can do Source.Inbound[SUM:Factory] (in your mapping module ?)

    This is equivalent to a SUMIF, bringing the sum of the values by factory.

     

    That only works for number, as Anaplan cannot sum text. It's possible to concatenate the text if that's what you want.

  • hinamahmood1994
    hinamahmood1994

    Hi, 

     

    How would I concatenate the text? My workaround of saved views does the trick. I'd like to try out the formula as well. 

     

    Thank you!

     

    Hina 🙂 

  • nathan_rudman
    nathan_rudman

    https://help.anaplan.com/anapedia/Content/Calculation_Functions/All/TEXTLIST.html

     

    I don't think you should have a workaround here, creating many saved views isn't a viable option on the long term.

  • ArunManickam
    ArunManickam

    Hello @hinamahmood1994 

    As I see your requirement, I suggest you to create "Lot" as a dimension first. 

    You can create your destination module Factory x Lot x Line items. In the line items you can bring the inventory by Factory, by Lot, using the SUM formula @nathan_rudman  explained.

     

    Thanks

    Arun

Categories