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

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?

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

Best Answers

  • 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.

  • 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

  • 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

  • 8R Dimensions

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

    From this MATR_Status column in 13DL:

  • 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.

  • 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)

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

  • 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.

  • 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..