Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

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
Contributor III

Re: Resolve Synthetic Keys

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;

5 Replies
carlosamonroy
Contributor III

Re: Resolve Synthetic Keys

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

Re: Resolve Synthetic Keys

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
Contributor III

Re: Resolve Synthetic Keys

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

Re: Resolve Synthetic Keys

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
Contributor III

Re: Resolve Synthetic Keys

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

Community Browser