Duplicate data / use data from another ID

[butter]

Hi All,

I have searched and I don't think I am using the correct key words to get the solution I am looking for.

I have data in Table1 that looks like this:

Location	Line	Product	Value
A	1	X	1.23
A	1	X	1.24
A	1	X	1.22

I have data in Table2 that looks like this:

Location	Line	Product	Volume
A	1	X	100
A	2	Y	200
A	3	Z	300

When I run all of my loads, run calcs and put into a straight table , I am getting the following (correct) data:

Location	Line	Product	Volume	Avg Value
A	1	X	100	1.23
A	2	Y	200
A	3	Z	300

The summary is correct and the data is loading correctly.  The problem is that my data sources are not perfect due to the back end systems.  In this case, "Line" is the problem.  I have the "values" being generated on a common production line, which is then split into three finishing lines.  

How can I load or use the values from Line 1 on lines 2 &3?  I want the table to look like:

Location	Line	Product	Volume	Avg Value
A	1	X	100	1.23
A	2	Y	200	1.23
A	3	Z	300	1.23

Essentially I want to load extra data or load it twice/three times.  It is NOT always a case where Line 1 = Line 2 = Line 3, so I want to be able to select when I do this...

[Vegar]

How do you want to select this? Dynamicly in the app or when loading the the data?

You could do this in the script by configuring the relationship in a structured way and then adding that into the table2.

Like this:

Load

Location,

Line as ValueLine,

Product,

Value

From table1;

Load

Location,

Line,

Product,

Volume, 

<some logic based that defines which valueLine> as valueLine

From table2;

[butter]

@Vegar I am ok to do this at loading time as you show above. 

I would assume the logic would be something like:

If ( location = 'A' AND ValueLine = '1', true, false)

for conditions of true, how to do I duplicate the data?

[PrashantSangle]

where have you calculated avg() front end or Script?

[butter]

I am calculating the average in the script using a master item measure.  I am allowing the user to filter by a date range and then displaying the avg() in a straight table.