Help Reducing Synthetic Relationships

Wed, 19 Dec 2012 17:25:04 GMT

Hello,

I have a system with three organizations and twelve tables per organization (36 tables total). Each table represents a form in a sequential life cycle process. Each table has a primary key common to all tables within an organization. Some tables have other common fields because data elements entered earlier may be updated at later stage in the life cycle. All tables need to share other common fields due to reporting requirements.

My issue is that Qlikview loads the data and crashes/times out due to the inordinate amount of synthetic relationships it is attempting to create. I would like some insight into more appropriate data organization approaches within Qlikview. I cannot modify the source data as it comes from Access tables linked to Sharepoint lists. I must use Qlikview to alter the organization.

Here is a example of my data relationship:

Organization 1

Table 1

Record ID	Record Name	Area 1	Area 2	Unique
1	Item One	Area A	Area AA	Value 1
2	Item Two	Area A	Area AB	Value 2
3	Item Three	Area B	Area BA	Value 3

Table 2

Record ID	Record Name	Unique 1	Unique 2
1	Item One	Value 1	Value 4
2	Item Two	Value 2	Value 5
3	Item Three	Value 3	Value 6

Table 3

Record ID	Record Name	Area 1	Area 2	Unique
1	Item One	Area A	Area AA	Value 1
2	Item Two	Area A	Area AC	Value 2
3	Item Three	Area C	Area CA	Value 3

Organization 2

Same tables as Organization 1

Organization 3

Same tables as Organization 1 and 2

In essence, the data structure is identical for all three organizations. The only difference is the user base providing data and some constant fields identifying the data as belonging to a specific organization.

Here is my psuedo Load Script:

ODBC CONNECT32 TO [MS Access Database;DBQ=Organization 1.accdb];

LOAD

'Organization 1' As [Life Cycle Org Name],

'ORG1' & [Record ID] As [Life Cycle ID],

Capitalize([Record Name]) As [Life Cycle Name],

'Form One' As [Life Cycle Form Name];

SQL SELECT

[Record ID],

[Record Name],

[Area 1],

[Area 2],

[Unique]

FROM [Table 1];

LOAD

'Organization 1' As [Life Cycle Org Name],

'ORG1' & [Record ID] As [Life Cycle ID],

Capitalize([Record Name]) As [Life Cycle Name],

'Form Two' As [Life Cycle Form Name];

SQL SELECT

[Record ID],

[Record Name],

[Unique 1],

[Unique 2]

FROM [Table 2];

LOAD

'Organization 1' As [Life Cycle Org Name],

'ORG1' & [Record ID] As [Life Cycle ID],

Capitalize([Record Name]) As [Life Cycle Name],

'Form Three' As [Life Cycle Form Name];

SQL SELECT

[Record ID],

[Record Name],

[Area 1],

[Area 2],

[Unique]

FROM [Table 3];

ODBC CONNECT32 TO [MS Access Database;DBQ=Organization 2.accdb];

LOAD

'Organization 2' As [Life Cycle Org Name],

'ORG2' & [Record ID] As [Life Cycle ID],

Capitalize([Record Name]) As [Life Cycle Name],

'Form One' As [Life Cycle Form Name];

SQL SELECT

[Record ID],

[Record Name],

[Area 1],

[Area 2],

[Unique]

FROM [Table 1];

LOAD

'Organization 2' As [Life Cycle Org Name],

'ORG2' & [Record ID] As [Life Cycle ID],

Capitalize([Record Name]) As [Life Cycle Name],

'Form Two' As [Life Cycle Form Name];

SQL SELECT

[Record ID],

[Record Name],

[Unique 1],

[Unique 2]

FROM [Table 2];

LOAD

'Organization 2' As [Life Cycle Org Name],

'ORG2' & [Record ID] As [Life Cycle ID],

Capitalize([Record Name]) As [Life Cycle Name],

'Form Three' As [Life Cycle Form Name];

SQL SELECT

[Record ID],

[Record Name],

[Area 1],

[Area 2],

[Unique]

FROM [Table 3];

ODBC CONNECT32 TO [MS Access Database;DBQ=Organization 3.accdb];

LOAD

'Organization 3' As [Life Cycle Org Name],

'ORG3' & [Record ID] As [Life Cycle ID],

Capitalize([Record Name]) As [Life Cycle Name],

'Form One' As [Life Cycle Form Name];

SQL SELECT

[Record ID],

[Record Name],

[Area 1],

[Area 2],

[Unique]

FROM [Table 1];

LOAD

'Organization 3' As [Life Cycle Org Name],

'ORG3' & [Record ID] As [Life Cycle ID],

Capitalize([Record Name]) As [Life Cycle Name],

'Form Two' As [Life Cycle Form Name];

SQL SELECT

[Record ID],

[Record Name],

[Unique 1],

[Unique 2]

FROM [Table 2];

LOAD

'Organization 3' As [Life Cycle Org Name],

'ORG3' & [Record ID] As [Life Cycle ID],

Capitalize([Record Name]) As [Life Cycle Name],

'Form Three' As [Life Cycle Form Name];

SQL SELECT

[Record ID],

[Record Name],

[Area 1],

[Area 2],

[Unique]

FROM [Table 3];

As you can see, this structure causes Qlikview to establish multiple synthetic relationships:

[Life Cycle Org Name]

[Life Cycle ID]

[Life Cycle Name]

[Life Cycle Form Name]

[Area 1]

[Area 2]

I need these fields so I can create List Boxes in Qlikview to allow users to select those elements to see different organizations, areas, and forms.

How can I reorganize the load from a Qlikview perspective to reduce/eliminate synthetic relationships? I've read about Link Tables, but I am unsure how to dynamically create one here.

Thanks!

Re: Help Reducing Synthetic Relationships

hic — Wed, 19 Dec 2012 17:31:40 GMT

Use the concatenate prefix to create one big table of it all.

HIC

Re: Help Reducing Synthetic Relationships

Wed, 19 Dec 2012 18:59:10 GMT

Thanks for the reply. Could you elaborate more on the use of concatenate in this situation? Could you provide a small example?

Re: Help Reducing Synthetic Relationships

hic — Wed, 19 Dec 2012 19:31:03 GMT

The first "Load ... ; SQL SELECT ... FROM" should be kept as it is. All the following ones should have the word Concatenate in front of them: "Concatenate Load ... ; SQL SELECT ... FROM".

Then QlikView will concatenate the 2nd and all following tables onto the first one, so that you get all data in one big table. Compare with a SELECT UNION SELECT. Fields that exist in several input tables will be in the same output table.

HIC

topic Re: Help Reducing Synthetic Relationships in QlikView

Help Reducing Synthetic Relationships

Re: Help Reducing Synthetic Relationships

Re: Help Reducing Synthetic Relationships

Re: Help Reducing Synthetic Relationships