need to do a look up formula

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 

 

Best Answer

Answers

  • 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 ?

  • 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 

  • I dont see any attachment
  • really ?? It's attached... doing it here again. 

  • 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.

  • 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 🙂 

  • 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.

  • 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