Syncthetic Key

[#1]

Hello,

With ref to the following table, i'm creating 2 charts, by using table on right and left. month as X-axis and Y would be the count of a key from both tables.

i can't merge the 2 charts because of month and year. but i had to merge them so that i can do calculation. and show the result in single chart.

now i had to create 4 selector, for both the month n year...

does anyone has a solution for this? if i change the of CCR_MONTH > CreaMonth, CCR_YEAR> CreaYear.
i'd have a synthetic table, n both chart will not work, it's not reporting me the correct figure.

[#2]

ok, may be tat's too tough.. let's simplify it...

says i have 2 table. and they are having the same number of column, both

Month | Count

for some reason, i can't link the month table together, otherwise it'd create loops. in this case, i'm looking at expression level, is there anything that i can "blend" the data from 2 table, sort them by month? in this case, it's easier for me to construct single chart. rather than 2 separate chart. but of course the month will still be control by 2 different selector or LIST.

[Anonymous]

Just a question, why do you need to have month in two separate tables? If the tables are already connected properly on another key then it shouldn't matter and you could just remove month from one of the tables. Selecting a month in the other table should still reduce the selection in the first table.

Then you can use the only month field in the charts and still have a proper connection to the related data for the expressions.

[#4]

There are several reason.

1. my brain still pretty much in relational db. so when QV is able to do that, i don't understand how.
2. because the data from left table is externally fed.

what i'm trying to merge is data collected manually, which is in left table, the KEY is supposed to be month, year, zone, plant. and then mapp this to the right table. but whern i do that with the 4 columns, i create sync$ table. so i decided to link by just zone, n it work fine, BUT i'll have duplicate month n year for user to select.

perhaps you might have a better idea on how to do that? i've been stuck for weeks on this...

[#5]

here is my data strucutre.

the BRIDGE & ACTUAL tables are working fine, i can create charts on those 2 table.

BRIDGE table is like a ref table, it store a list of plant that we keep track, whereas ACTUAL table has everything, unclean data.

so when i build a selector on BRIDGE.PLANTCODE, user is able to see the correct plant figure.

now, i need to "plug" an external data to this strucutre. EXTERNAL.

and in the end, i'd need to create these charts,
1. by month, i'm trying to merge month, but it's not reporting the correct figure. it actually doubles the value for me when i see it in list. eg, 2 jan, 2 feb, 2 mar,.... etc..

2. by ACTUAL.zone by month (actual.zone = client zone, bridge.zone = plant zone, they are different)

3. by Bridge.zone by month

picture this> a bar chart, y=count(id) & sum(total), x=month,

now, i can create them in 2 separate chart, but i need them to be in single chart.

any suggestion?

[Anonymous · Accepted solution]

Ok, the question then is, is Zone a unique field to create the connection you're looking for between the tables or does month, year, zone and plant together build up the key?

In that case you would have to create a composite key by concatenating the values of the fields to build a distinct linking value.

Imagine having the following two tables:

OrderID, Month, Customer
1 Jan A
2 Feb B
3 Mar C

OrderID, Month, Shipper
1 Jan X
2 Feb Y
3 Mar Z

Now, the OrderID is enough to link these two tables together. OrderID = 1 is associated with the Month=Jan even if you remove the field Month from one of the tables. If you don't, you end up with a synthetic key between OrderID and Month.

If the situation is different however, say that we start the OrderID count from 1 each new month:

OrderID, Month, Customer
1 Jan A
2 Feb B
1 Mar C
2 Mar D
3 Mar E

OrderID, Month, Shipper
1 Jan X
2 Feb Y
1 Mar Z
2 Mar X
3 Mar Z

Now we can't just link on OrderID since if we were to remove the Month from the second table we have no way of knowing which Supplier that particular OrderID (1) is attached to. This is of course a silly example but illustrates the functionality. What we can do here is create a composite key of OrderID & Month.

Load
OrderID & '-' & Month as Key,
OrderID,
Month,
Customer
from XXX;

Load
OrderID & '-' & Month as Key,
Shipper
from yyy;

This creates a field called Key in both tables with the following type of structure (1-Jan, 1-Mar, 2-Feb etc.). Now we can use this combined key value to link the tables and since we get a unique link we don't need month in the second table, or OrderID, as that field was only used for linking the tables and the OrderID's are all available in the first table anyway. I hope this makes sense.