Pulling data from numbered list into a module

Hi All, 

 

I need some advice/guidance. I'm building a tiny model that looks at the number of components used in products. 

 

We have one component that can be used in 5/6 products. The end result that I'm trying to achieve is a module that shows me data by components. 

 

What I've done is created a numbered list called "infeed data". Within this list, I have my components and products. I've created a mapping to make sure that products are unique. Manged to feed the data in and it's working correctly. 

 

The second part of this is feeding some of this data into a module. I'm a little stuck on how to go about collating all the data for date fields. 

 

I've attached the properties for my list and the module I want to feed data into. Lookup/SUM isn't working. 

 

Essentially want to pull in RDD, ETD, ETA against "mapping to model" in my module.

Best Answer

  • @hinamahmood1994 

    Given it's a small model, why don't you create a module dimensioned by both Components and Products as a matrix.

     

    And by the way, it is best practice to use a code for the numbered list and avoid using list properties, use a System module instead.

     

    David

Answers

  • Hi @hinamahmood1994 

     

    The SUM function won't work on a date only on a number.

     

    One method could be to convert the date into a number and then use SUM and then convert back into a date.

     

    Or if you create a saved view you could try an import process. 

     

    I hope this helps! 

     

    Thanks,

     

    Usman

  • One method could be to convert the date into a number and then use SUM and then convert back into a date. 

     

    How would I go about doing that? 

     

    Or if you create a saved view you could try an import process. The saved view might work.  Would I need to create an intermediate module where my list data would be fed into. Then create a saved view import into my final module?

     

    Thanks

     

    Hina 

  • Hi, 

     

    I did the mapping in a numbered list - where I used a code. That list is the hierarchy against which my data will be fed into a module. 

     

    The infeed data is feeding into a different list, trying to use formulas to extract the data from my infeed list into my module, which has my numbered list mapping. 

     

    How would I set the matrix up in a module? I set it up in a numbered list as I have one component that feeds into many products. Those products aren't unique. They can be used in 3/4 other components. 

     

    Thanks, 

     

    Hina 

     

     

  • Hi @hinamahmood1994 

     

    You would use some formulas to split the date out into numbers such as:

    Day Number

    Use LEFT(Date Line Item, 2) takes the day characters as text

    Then VALUE(above line item) converts the text into number 

    ***This could all be done in one line item, so in this format for the purpose of explaining

    Month Number 

    Use MID(Date line item, 2) takes the month characters as text

    Then VALUE(above line item) converts the text into number 

    Year Number

    Use RIGHT(Date line item, 4) takes the year characters as text

    Then VALUE(above line item) converts the text into number

     

    Then you would sum each of these across as explained earlier.

     

    Then to combine them back into date you would use:

    DATE(Year, Month, Day)

    This would then give you the date. 

     

    I hope this helps!

     

    Thanks,

     

    Usman