how to use Lookup when source module has no dimensions.

My source module has no dimensions so How do I use lookup to transfer my data to result module.

My Source file

source.png

My Result module

result.png

 Data should onlu inserted in ABS not other two.

Tagged:

Answers

  • Hi @Nachiketa!

    You can use a direct LI link to LI. If you have other LI and dimensions in the module, you can read about Line Items Subset.

  • Direct LI ??

  • I'm sorry, I didn't understand your question right away. Am I right to understand that there is a separate module ABS and need to pull data from it into a separate module, where ABS - the value of the directory?

    And how should the data be formed on the other elements of the directory?

  • Yes there is a module where we have only abs values and one where abs is part of dimension so I want to insert values only in abs section.

  • @Nachiketa 

     

    You can write IF else statement on your ABS list.

     

    IF ITem(List) = List.ABS then Source module. line item else 0

     

    Best Practice suggests you to create  SYS module dimensioned by ABS list and create one boolean formatted line item. Re write the above formula as

    IF SYS ABS module. Boolean line item then Source module.line item else 0

     

    Hope that helps

     

    Misbah

    Miz Logix

  • This is a working approach, but if you have a lot of elements and different sources in the directory - the formula will be too long and opaque.

  • Yes you are right it won't be a good option if list is long is there no other way like vlookup in excel

  • If you have many sources, I suggest using LIS mappings:

    1. Create an LIS from source modules
    2. Create an LIS from the target module.
    3. Create a mapping where you associate the source LIS with the directory items and the target LIS.
    4. Create a technical module with COLLECT on the source LIS.
    5. In the target module, make a link to the module with COLLECT and two SUM on the mapping.

    The screenshot is a quick example.


    Снимок экрана 2021-12-17 в 08.52.25.png

  • @Nachiketa 

    Create a line item subset from the line items of the source module. 

    Add a new line item to the source module making sure it is not automatically added to the line item subset.

    Add the line item subset to this new line item via the Applies To column in the blue print. 

    Use the following formula; COLLECT()

     

    Create a second line item subset of the target module. 

    Create a mapping table using the line item subset of the target and any other relevant dimensions. Add a line item formatted as the line item subset of the source. 

    Populate the line item in the mapping to pull allocate the relevant source line item to the combinations of line items and dimensions in the target.

     

    Use LOOKUP to retrieve data from the correct line item in the source as per the mapping. Use the COLLECT() line item in the source as your data source in the LOOKUP.

     

  • Hey can you please explain how to map line item subset?

  • You can choose your LIS as LI Format. From there, it works like a normal list.

    Снимок экрана 2021-12-20 в 10.51.39.png