Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Use the concatenate prefix to create one big table of it all.
HIC
Thanks for the reply. Could you elaborate more on the use of concatenate in this situation? Could you provide a small example?
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