3 Replies Latest reply: Dec 19, 2012 2:31 PM by Henric Cronström RSS

    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!