To use Lookup in a Flatlist

aishabhy05
aishabhy05
in Modeling

Hi All,

 

I have a scenario, as follows:

 

Module 1 : 

To get Headcount in this module

 

Dimensions: L6 hierarchy, L6 Flat List(same hierarchy as a flat list), Cost Centre

Line Item: HeadCount

Time by month

 

Module 2:

Dimensions: L6 Hierarchy, Grade, Contract Type

Line ITem: HeadCount

Time by month 

 

I wanted to get the headcount in module 1 by referring to module 2.the headcount in module 1 should be by the L6 flat list. ( Not by L6 hierarchy). Any inputs, please?

 

Thanks 

 

Tagged:

Answers

  • obriegr
    obriegr
    Use Find Item so that you have the appropriate flat list member.

    Then you can use Lookup to find the headcount associated with it

    Line Items:
    L6: ITEM(L6 Hierarchy)
    L6 Name: NAME('L6')
    Matching FL L6 List Member: FINDITEM(L6 Flat List,'L6 Name')
    Flat List HC: Flat List Module.Headcount[LOOKUP: 'Matching FL L6 List Member']
  • MicheleVendemini
    MicheleVendemini

    Hi all,

    doing as obriegr has suggested, the lookup function doesn't work for the parent list members of the hierarchy list.

    In other words, when the FINDITEM formula retrieves a list member which is a parent in the hierarchy list, the lookup function can't retrieve any value

    Do you have any additional suggestions?

    Thanks a lot

    Michele

Categories