topic Avoiding circular loops caused by synthetic keys in QlikView

Avoiding circular loops caused by synthetic keys

Wed, 23 Sep 2009 15:27:21 GMT

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

Avoiding circular loops caused by synthetic keys

Wed, 23 Sep 2009 15:55:27 GMT

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

Avoiding circular loops caused by synthetic keys

Wed, 23 Sep 2009 16:06:19 GMT

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".

Avoiding circular loops caused by synthetic keys

Wed, 23 Sep 2009 20:34:23 GMT

You guys are great.

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

Thanks a lot

Vincent