Register

Using LASTNONBLANK on a numbered list

brookec
Contributor

Using LASTNONBLANK on a numbered list

I took over a Headcount model that was built by a consultant and it uses LASTNONBLANK on a numbered list. I'd like to confirm my understanding of the function when used on a numbered list. 

 

Here's some background on my particular model

  1. Workday sends Anaplan an Employee file each night
    1. This nightly feed from Workday updates a numbered list called Integration: Employee
    2. The code for the Integration: Employee is just the Employee ID. Therefore, if Workday hasn't ever sent this Employee ID before it is appended to the list, otherwise the corresponding employee record is updated in a module dimensionalized by the Integration: Employee list
  2. Fields in the module dimensionalized by the Integration: Employee list are concatenated to create a unique identifier that is Employee ID|Cost Center|Position ID
    1. The concatenated fields are used as the code for a numbered list called Employee. This list is only updated if that combination of the fields above does not already exist

Let's use a cost center transfer as an example:

Example

EID12345|CC9999|P54321 (First Instance loaded to Employee list)

EID12345|CC2222|P54321 (Second Instance loaded to Employee list)

 

Let's call the module dimensionalized by the Integration Employee list the Integration Employee Data module and the module dimensionalized by the Employee list the Employee Data module.

 

The Integration Employee Data module has a line item that is pulling the cost center from the Employee Data module. The formula for that line item is Employee Data.Cost Center[LASTNONBLANK: List: Integration Employee]. List: Integration Employee in the formula above is a list formatted line item in the Employee Data module, which is formatted by the Integration Employee list. 

 

I'd expect this formula to pick up the last instance of the given Employee that was loaded to the Employee list. For the cost center transfer example above, I would expect the formula to give us CC2222 and not CC9999. It appears to be functioning this way, but I recently found a couple out of sync records and want to confirm my understanding. I believe I should be able to expose the underlying number of the numbered list and expect the cost center to be that of the instance of the Employee with the higher number. 

 

I probably could have provided a simpler example, but I wanted to make sure I described exactly how everything works. Thanks in advance for any help!

1 REPLY 1
Misbah
Moderator

Re: Using LASTNONBLANK on a numbered list

@brookec 

 

See if this helps

 

I am checking Last Occurred Value based on the Cost Centers. Here I am using Anaplan system generated ID's to get to the highest underlying number

 

Misbah_0-1620031440125.png

Misbah_1-1620031488669.png