Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Help Reducing Synthetic Relationships

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
1Item OneArea AArea AAValue 1
2Item TwoArea AArea ABValue 2
3Item ThreeArea BArea BAValue 3

Table 2

Record ID
Record Name
Unique 1
Unique 2
1Item OneValue 1Value 4
2Item TwoValue 2Value 5
3Item ThreeValue 3Value 6

Table 3

Record ID
Record Name
Area 1
Area 2
Unique
1Item OneArea AArea AAValue 1
2Item TwoArea AArea ACValue 2
3Item ThreeArea CArea CAValue 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!

3 Replies
Employee
Employee

Re: Help Reducing Synthetic Relationships

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

HIC

Not applicable

Re: Help Reducing Synthetic Relationships

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

Employee
Employee

Re: Help Reducing Synthetic Relationships

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

Community Browser