Complex data reduction (for me anyway)

[#1]

Hi guys,

I have 2 tables which have been concatenated as follows

Table1:

Month, Value, UNIT

1,100,A

2,200,B

3,300,C

Table2:

Month, ValueA, X_UNIT

1,100,A

2,200,B

3,300,C

The resulting concatenated table looks something like this

Month, Value, UNIT, ValueA, X_UNIT

1,100,A-,-

2,200,B,-

3,300,C,-

1,-,-,100,A

2,-,-,200,B

3,-,-,300,C

Now, I am trying to reduce data for each user by UNIT only, NOT by X_UNIT. The output that I am trying to get at is:-

If a user who belongs to UNIT A logs in, he/she should see:

Month, Value, UNIT, ValueA, X_UNIT

1,100,A-,-

1,-,-,100,A

2,-,-,200,B

3,-,-,300,C

Is this possible?

Right now, if I reduce by UNIT, all data under X_UNIT is not available to the user.

Any help would be appreciated

[pover · Accepted solution]

Following your example the Value 'A' needs to be explicitly defined for every row that you want the user to see so you need the rows to be:

1,100,A,-,-
1,-,A,100,A
2,-,A,200,B
3,-,A,300,C

You could do a join of the 2 tables so that A,B and C would be defined for every value in Table 2, but this might have the heavy cost or increasing drastically the size of the table.

I wonder if you could just link Table 1 and Table 2 by Month and as long as value A is related to all the months you are going to still see all the values of Table 2. How does that sound?

Regards.