Get data into List Property from another List

Hi,

 

I have 2 LIsts, Numbered List and Flat List. 

I need to Pull Data from Flat list to Numbered List using Formula.

 

For Ex: I have Emp ID Data in Flat List. I need to pull that data to Numbered List Emp ID property by using Reference and Formula.

 

I tried but Not able to get the output.

 

Can anyoune help here to get the Output

Tagged:

Best Answer

  • Hi Shruthi,

     

    Ideally I will keep the code of both the list same and then in the system module have a property against Employee of the employee Flat List. If code is same the formula you can use here is FINDITEM(Empl Flat, CODE(ITEM(EMpl))). If the codes are not same, you will need a mapping for the employee and employee flat list.

    Below formula will help you get the data from the module dimensioned by Employee Flat List

     

    Emp Flat Mod.Salary[LOOKUP: Emp Flat]

     

    where Emp Flat refers to the mapping 

     

    Please take care of doing all this in respective modules and not in the list , which goes with the DISCO and ways of modelling.

     

    Let us know if this resolves

     

     
     

     

Answers

  • Hi,

     

    Linking two list is usually done with the LOOKUP formula. Flat list.value[LOOKUP:reference line item]

    So you need your reference line item:

    - if the two list have a common property like the employee ID, you can use the FINDITEM() formula. This formula, given a list and a code, will result in a list item of that code. You can then use that list item as a key for LOOKUP.

    - if the numbered list is created from the flat list, make sure your numbered list has a property / line item formated as the flat list that you fill in during the import creation. You can then also use LOOKUP 

     

    You can find these formulas here:

    https://help.anaplan.com/anapedia/default.htm#cshid=1550

     

  • Thanks for the solution, I tried with this Formula but no output.

     

    Formula: Employee.Emp ID[LOOKUP: Emp Name]

     

    Thanks,

     

  • @Shruthi 

    Just to clarify, the name property of the numbered list is not applicable to use as the lookup because this is the system generated id (#23, #45 etc.).  As @akhil.kohli says, the only way to effectively match to this is to use a code.

    It is best practice to use codes for all lists but especially for numbered lists.

    I would also add it is best practice not to use List Properties, but to use modules to hold the attributes of the lists.

    David

  • what's the format of employee name ? and is it filled with a value ?

  • Formate of employee name in Text, and i am importing data from CSV file. Yes Data is filled in that column.

  • text format will not work for a LOOKUP, it needs to be list formated, the list that you are trying to link (the list where you type the formula). And to get that list formatted element, you can do as I describe in my first reply
  • Hi 

     

    Thanks for suggestion. Lookup is working fro list to module but falt list to numbered list  is not working. I tried with module and got the out put. Thanks for the solution given

     

    Regards,

    Shruthi

  • @Shruthi 

    I would also split out the CODE(ITEM(EMpl)) element of the formula and hold that in a model dimensioned by Empl.  It is likely that you will need it again, so it is more efficient to hold it in one place and reference it many times.

    David