need to do a look up formula

Highlighted
Contributor

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 

 

9 REPLIES 9
Highlighted
Community Boss

Re: need to do a look up formula

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 ?


Nathan Rudman, Anaplan Model Builder
Highlighted
Contributor

Re: need to do a look up formula

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 

Highlighted
Community Boss

Re: need to do a look up formula

I dont see any attachment

Nathan Rudman, Anaplan Model Builder
Highlighted
Contributor

Re: need to do a look up formula

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

Highlighted
Community Boss

Re: need to do a look up formula

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.


Nathan Rudman, Anaplan Model Builder
Highlighted
Contributor

Re: need to do a look up formula

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 🙂 

Highlighted
Community Boss

Re: need to do a look up formula

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.


Nathan Rudman, Anaplan Model Builder
Highlighted
Community Boss

Re: need to do a look up formula

Hello @hina1994 

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

Highlighted
Master Anaplanner/Community Boss

Re: need to do a look up formula

@hina1994 

I would also suggest you have a look on the CPX highlights.  We ran a sessions specifically on SUM and LOOKUP

https://www.anaplan.com/connected-planning-xperience/highlights/

 

You will need to register to access this and many other valuable sessions

The specific one you want is 

Tips and Tricks: Formulas and Functions, Using SUM and LOOKUP

David