need to do a look up formula

hinamahmood1994

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

Accepted answers

DavidSmith

@hinamahmood1994

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

All comments

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

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

mapping_module_52199.JPG

module_2_52199.JPG

nathan_rudman

I dont see any attachment

hinamahmood1994

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

mapping_module_52201.JPG

module_2_52201.JPG

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

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

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

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

DavidSmith

@hinamahmood1994

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

Quick Links

Can you help?

Unanswered questions

How to create Anaplan formula MTD with Input Date
Hi, I'm trying to create a month to date line that is based on a specific input date, not the actual date. Is this possible?
How to create identical child items for multiple parents
The data I'm trying to import is as follows Parent-Code A B A C B D C D D E I want to generate the following list from this data: L1 L2 L3 L4 A A-B A-B-D A-B-D-E A A-C A-C-D A-C-D-E You can create up to L3.However, L4 has multiple parents for one row of data When I try to create L4 from D-E, only one list row is generated…
Predefaulting A Feild in ANAPLAN
Hello Anaplan Community, I have a special requirement from a customer and would appreciate your input. Let me explain the situation: We have a hierarchy with three levels: Level 1, Level 2, and Level 3. Screen 1: Creation of Level 1 Items On this screen, the user selects a team. Based on their selection, an action runs to…