topic Re: Resolve Synthetic Keys in QlikView

Resolve Synthetic Keys

Mon, 26 Jun 2017 18:12:06 GMT

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.

Dimension(s):
- FTE
- GSO-ON
- GSO-OFF
- Budget
- Objectives
- Deliverables
Metric(s):
- Year
- Maturity State
- Maturity Index
- Budget Type
- Budget Nature
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,

Re: Resolve Synthetic Keys

CarlosAMonroy — Mon, 26 Jun 2017 18:49:09 GMT

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

Re: Resolve Synthetic Keys

Mon, 26 Jun 2017 19:08:49 GMT

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,

Re: Resolve Synthetic Keys

CarlosAMonroy — Mon, 26 Jun 2017 19:13:44 GMT

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;

Re: Resolve Synthetic Keys

Mon, 26 Jun 2017 19:41:36 GMT

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?

Re: Resolve Synthetic Keys

CarlosAMonroy — Mon, 26 Jun 2017 19:46:46 GMT

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