Returning one line items value using other line items

ryan.trottier

Hi all, 

 

I'm trying to find a way to return the value of one line item in a list, using two other line items.

Background:
I have one large module built off sequence numbers as the rows, and line items as the columns (no page dimensions). Each row is a seperate transaction (either a product being added or a product being removed).

 

I have one line item that will tell me the date that a recently removed product was originally added (this shows up on the row with the product being removed), and another line item that has the account number.

 

What I am trying to do:

I need a way to find out what the employee ID was on the row (transaction) where my product was added. This transaction would have the same account number, and the transaction date would match the removal date on the row where the product has been removed.

 

Long story short - I am trying to have the employee number of the person who originally added the product, show up on the row (transaction) where the product has been removed, using only the date and the account number.

 

I have created an example below that is overly simplified as I am dealing with ~100k transactions each day for all different products and thousands of employees, but hopefully it demonstrates what I am trying to do.

 

Example:

Thanks in advance for any assistance!

zafkamar

Hi Ryan,

 

An intriguing problem - this could be a job for the ISFIRSTOCCURRENCE() command.  What I would do to solve this one is the following:

1. Create another boolean-formatted line item in the list called 'FO Product?' (our Lionpoint model builders like to use FO as an abbreviation for IsFirstOccurrence) using the below syntax:
   ISFIRSTOCCURRENCE('Product Code', Products)
2. Create a filter on the 'FO Product?' line item -> this should result in a filtered list of transactions that only show the first instance of that product showing up... which should hopefully be when it was added.
3. Save this filter as a Saved View, I'll call it "FO Product Adds > Transactions"
4. Create another module called something like "FO Product Adds > Products" dimensionalized by your Products list and the same line items as your transactions module, especially including the Employee Number
5. On the "FO Product Adds > Transactions" module, Import... Connect to Anaplan Model... Saved View ... "FO Product Adds > Transactions", and configure that appropriately.  Run the import.
6. Once that executes, you should now have what you need to get the original employee who added the product.  On the original transactions module in the 'Employee ID who originally added' line item, you should be able to execute the following formula:
   'FO Product Adds > Products'.'Employee Number' [LOOKUP: Product Code]

 Hope that helps!

-Zaf

rob_marshall

Ryan,

 

Can an Account have more than one Product assigned to it?  If it can, I would create a hierarchy with Products (numbered list) rolling up to the Account using a similar method that Zaf mentioned above (isfirstoccurrence()).

 

Thanks,

 

Rob