Avoiding circular loops caused by synthetic keys

[#1]

Hello all.

This is my first post on this forum.

In order to set up a dashboard, I am loading data from various files such as invoices, offers, and so on.

Each sheet of the dashboard focuses on one type of data (again, invoices, offers and so on) and I have three selection criterias : Year, Month and Office. The idea is that on the first sheet of the dashboard, I select Year, Month and office and the table are refreshed accordingly on all sheets.

In the script, I add flelds Year, Month and Office with AS statements based on invoices dates, offers dates, ... as follows:

LOAD
InvoiceNumber,
Office,
...,
Left(InvoiceDate,4) as Year,
Mid(InvoiceDate,5,2) as Month
FROM C:\Invoices.TXT ....

So I end up with all tables linked by a synthetic key containing Office, Year, Month.

As some tables are linked by other fields (such as OfferNumber which is part of Invoices and Offers), I end up with a circular loop problem and the script will not load (by the way, when executing the script on Qv 8.5, I get the circular reference error meesage, while with Qv 9 i only get a script error with no further details).

To avoid this, I can :

1°) Qualify the tables or rename the Year, Month and office field for each loaded file: the problem is that I will have to use a Year, Month and Office selector on each sheet (Invoices.Year, Offers.Year,...) and then select manually the Year, Month and Office on each sheet.

2°) Loosen the tables : no synthetic key is created, but this could lead to collateral damages.

I think the 1°) would be the best way to do it, but how can I automatically set all the sheets selectors for Month, Year and Office at the same time ?

Thanks beforehand.

Vincent

[#2 · Accepted solution]

Vincent,

In my experience, never loosen the tables.

One way to reduce the synthetic keys with dates is to only hold the date itself in the tables and create a calendar table that breaks the date down into years/months/quarter:

tab1:

load date,

...

from source1;

tab2:

load date,

....

from source2;

tCalendar:

load distinct date

resident tab1;

load distinct date

resident tab2;

calendar:

load date,

month(date) as month,

....

resident tCalendar;

drop table tCalendar;

The looping is another problem that you can only solve by looking at the data structure. For example, would it make sense to create a link between the invoices and offers tables on just the offer number so that when invoices are selected by date, only the corresponding offers are selected?

Regards,

Gordon

[#3]

In general, to remove synthetic keys, replace all the fields in that synthetic key with a concatenated field, i.e. for your Year, Month and Office fields add a new field (Year & '|' & Month & '|' & Office as KeyField). You can even make this an Autonumber value. If you want the individual fields, create a new table containing the individual fields and the concatenated key field, but do not hold the individual fields in the other tables (or make them unique).

Note the use of '|' as a separating character - it does not matter what you use as long as it does not occur in your data. Without the separator, you may not have unique values; i.e. joining "01" to "001" without a separator gives "01001", as does joining "010" and "01".

[#4]

You guys are great.

I used a mix of both answers to solve my problem.

Thanks a lot

Vincent