How can I lookup a line item in a source module ?

LimPhingHua
LimPhingHua
in Modeling

Hi, I am trying to pull in a column data (Status) from a module 13DL into the target module 8R.

LOOKUP didnt work for me. Help. What did I do wrong?

image.png
image.png

Target module :8R is dimensioned by P Project Data and line items

Source module: 13DL has a line item which has a list format of P Project Data ( derived from using Finditem formula). 13DL is dimensioned by DPMS and line items.

I am trying to pull text data from the Status column from 13DL to 8R. But get dimension error

image.png

Best Answers

  • pyrypeura
    pyrypeura
    Answer ✓

    It seems that you are trying to do LOOKUP wrong way round. You could do lookup from 8R to 13DL but not otherway round. Instead of LOOKUP you could use FIRSTNONBLANK or LASTNONBLANK summary functions if those suit your need.

  • HimanshuRaj
    HimanshuRaj
    Answer ✓

    Hi @LimPhingHua

    You use Lookup when you want to pick a particular data from a cell from source module, so lookup is generally applied for dimensions.

    In your case, the field you are using lookup against is not a dimension, it's a line item.

    So, instead of lookup, As Pyry mentioned you need to use Firstnonblank to pull the data in to avoid creating staging module for lookup

    Cheers!!

Answers

  • asarna
    asarna

    Hi @LimPhingHua ,

    Can you show me the Data view for the Finditem you've done? Also, the code you've mentioned doesn't seem to be correct.

    Thanks,

    Anmol Sarna

  • LimPhingHua
    LimPhingHua

    13DL

    image.png

  • LimPhingHua
    LimPhingHua

    8R Dimensions

    image.png

    I am trying to fill in the MATR status (highlighted column in 8R :

    image.png

    From this MATR_Status column in 13DL:

    image.png

  • asarna
    asarna

    If there is a one-to-one mapping between 13DL and P Project Code, there should be no errors. However, if there are multiple lines for the same MATR_Status, an error will occur.

  • LimPhingHua
    LimPhingHua

    Actually its one to one mapping for 8R and 13DL. The display of 8R is showing like its has a many relationship…but actually the hidden code behind the display is unique …( the chosen display name isnt exactly ideal…..i inherited this existing list from someone else)

  • LimPhingHua
    LimPhingHua

    P Project data list with the hidden unique Code (although display name is may have repeats):

    image.png

  • LimPhingHua
    LimPhingHua

    Both tables have another unique line item 'Reservation' as well.

    P Project data list is just from a combination concatenation of Project elements-Reservation.

    Do I have to resort to creating a staging module if this LOOKUP isnt working.

  • LimPhingHua
    LimPhingHua

    Hahah FIRSTNONBLANK solved it! I get it now, FIRSTNONBLANK act like a reverse LOOKUP if the source module has the dimension of the target module as a line item.

    I have used FIRSTNONBLANK before as summary method on a line item within the same module with a hierarchy list dimension. But not like this.

    Thanks..

    image.png

Categories