Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Resolve Synthetic Keys

Hello,

I'm struggling to figure out how to resolve a synthetic key issue. The context of this issue is that I'm attempting to create a dynamic pivot table with the following selection options as list boxes. I figured out how to achieve the list boxes, but I can't figure out how to populate the dynamic pivot.

  1. Dimension(s):
    • FTE
    • GSO-ON
    • GSO-OFF
    • Budget
    • Objectives
    • Deliverables
  2. Metric(s):
    • Year
    • Maturity State
    • Maturity Index
    • Budget Type
    • Budget Nature
  3. Business Area(s):
    • Dev Ops
    • Prod Ops
    • 3PD
    • TDM

My main issue is that all of the business areas share the same metrics & dimensions. Thus, my entire table is connected by a number of synthetic keys. Here is the key view after I tried to resolve the issue by qualifying each table. However, that won't resolve my problem because I need to associate each dimension & metric between the 10 business areas.

Is anyone able to point me in the right direction? Do I need to create a link table, or concatenate the common dimensions & metrics (even though they are shared by all of the business areas)?

Best,

AR

1 Solution

Accepted Solutions
CarlosAMonroy
Creator III
Creator III

If all the tables have the same structure, just remove the Qualify and the tables will concatenate automatically.

or you can do it by using Concatenate (TableName)

Fact:

//DevOps

Load Field1,

Field2,

'DevOps' as _sourceflag

From Table;

concatenate (Fact)

//ProdOps

Load Field1,

Field2,

'ProdOps' as _sourceflag

From Table;

View solution in original post

5 Replies
CarlosAMonroy
Creator III
Creator III

Hi Alexander,

You have to concatenate each of the tables that have the same structure and create for each a flag that distinguished the source.

DevOps:

Load Field1,

Field2,

'DevOps' as _sourceflag

From Table;

ProdOps:

Load Field1,

Field2,

'ProdOps' as _sourceflag

From Table;

and so on.

Make sense?

Carlos M

Not applicable
Author

Hey Carlos,

Appreciate the quick response. I followed along with your advice and added the business area titles as source flags. What is the next step with respect to concatentation?

Best,

AR

CarlosAMonroy
Creator III
Creator III

If all the tables have the same structure, just remove the Qualify and the tables will concatenate automatically.

or you can do it by using Concatenate (TableName)

Fact:

//DevOps

Load Field1,

Field2,

'DevOps' as _sourceflag

From Table;

concatenate (Fact)

//ProdOps

Load Field1,

Field2,

'ProdOps' as _sourceflag

From Table;

Not applicable
Author

Thanks Carlos,

Now that I have consolidated all of my tables into a single fact table, how do I access/sort the information by the sourceflags I have created?

CarlosAMonroy
Creator III
Creator III

Now you can add the flag as a list box or use set analysis in order to get the desired results.