topic Re: Loops in data structure in QlikView

Loops in data structure

Tue, 24 Jan 2012 19:46:52 GMT

Hi!

We are developping a tool to log and trace faults in machines, but there are some looping errors. We have searched the coomunity and the net, many people seem to have similar problems, but the proposed solutions have not worked so far for our case.

There are the following tables:

Table_Turbine_Data:

TTimestamp,

Turbine_ID,

Turbine_Parameter1,

Turbine_Parameter2,

Turbine_Parameter3

Table Turbine_Logbook:

Turbine_ID;

Alarm_TimeDetected,

Alarm_TimeReset

Table Calendar:

TTimestamp,

Year,

Month,

Week,

Day

This yields a circular reference / loop problem:

"One or more loops have been detected in your database structure. Loops may cause ambiguous results and should therefore be avoided. QlikView will cut the loop(s) by setting one or more tables as loosely coupled. Settings for loosely coupled tables can be modified after script execution in the tables page of the document properties dialog.",

since Alarm_TimeDetected and Alarm_TimeReset are linked to theTTimestamp through an additional table:

Interval:

IntervalMatch(Calendar)

load

Alarm_TimeDetected,

Alarm_TimeReset

Resident Turbine_Logbook;

Now, TTimestamp and Turbine_ID and the Interval stories are linked together and form this loop:

We need to be able to make selections by turbine and by date simultaneously. Hence copying to an other key would not make much sense.

Does anyone have an idea how to fix this problem? We woudl very much appreciate!

Best regards,

Jonas

Loops in data structure

Oleg_Troyansky — Tue, 24 Jan 2012 20:46:56 GMT

Jonas,

the problem is caused by linking all the timestamps to the same "Calendar" table. You have 3 timestams in your application. They represent different events and different times. The date (month, year) of "TimeDetected" is not necessarily the date of "TimeReset". Therefore, you can't associate (link) the same Calendar to all 3 timestamps.

You basically have 3 options:

1. Restructure your data in such a way that you could have a single Timestamp field, qualified by another field for different "Event Types", such as Detected, Reset, etc...

2. Load the Calendar data 3 times (with all different names) and associate a separate calendar with each timestamp.

3. Keep the Calendar disconnected ( as a "data island") and facilitate associations via Set Analysis and other expression techniques.

The first option might require too much data modelling. The third option is usually causing performance problems (for large datasets). Option 2, even though not too elegant, does the job just fine in most cases.

cheers,

Oleg

Re: Loops in data structure

Tue, 24 Jan 2012 21:44:50 GMT

Dear Oleg,

Thank you for your answer!

Actually, what we would like to have, is what results out of the IntervalMatch, that is, we would like to see, what errors where on at which instant of time, we tried to do so via the association of every error data set with all the Timestamps (a continuous data serie with one value per minute) of the Calendar, during which the error was on.

Like in SQL, this querry could look like this, if Turbine errors on turbine "01" were to be shown:

SELECT * FROM Turbine_Data //(or TTimestamp)

WHERE TTimestamp>=Alarm_TimeDetected and TTimestamp<Alarm_TimeReset and Turbine_ID=01

We tried to achieve this through the IntervalMatch functionality. Do you see better options?

Cheers,
Jonas

Re: Loops in data structure

Wed, 25 Jan 2012 17:04:27 GMT

We were thinking to use a structure like:

If we have the following table (The times hh:mm stand for full timestamps).

Alarm_ID Alarm_Detected Alarm_Reset

1 01:20 01:24

2 01:22 01:27

How could we possibly generate this following table?

TTimestamp Alarm_Detected Alarm_Reset Alarm_ID

01:20 01:20 01:24 1

01:21 01:20 01:24 1

01:22 01:20 01:24 1

01:22 01:22 01:27 2

01:23 01:20 01:24 1

01:23 01:22 01:27 2

01:24 01:22 01:27 2

01:25 01:22 01:27 2

01:26 01:22 01:27 2

Meaning, that we would repeat these data sets for every time they occur.

Every hint will be appreciated! 😃

Cheers,

Jonas

Loops in data structure

Oleg_Troyansky — Thu, 26 Jan 2012 03:23:18 GMT

Oh, then you really need the intervalmatch, only instead of "calendar" use the field Timestamp inside the parentheses. I believe it should work... Also, add INNER JOIN in from of the intervalmatch.