Pulling data from numbered list into a module

hinamahmood1994

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.

DavidSmith

@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

usman.zia

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

hinamahmood1994

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 

hinamahmood1994

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 

 

 

usman.zia

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