Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

rheacock
New Contributor

How to Remove Synthetic Key

I have two DW tables, both very similar as far as the data goes, one is for Shipped orders, one is Backlog.  They do not link together in any way, but have 30 or 40 columns that are the same name and I would like to keep it that way to make it easier for the users building visualizations.  Is there a command I can use to force Sense to NOT create a synthetic key between these two tables with a massive synthetic key?  Everything I've read leads me to believe I need to rename nearly every column in one of these tables, just so Qlik won't automatically create the key.

Also, I'm currently running Qlik Sense Desktop, waiting to get Enterprise installed.  Maybe this isn't an issue with Enterprise?

Thanks for any help.

Tags (1)
1 Solution

Accepted Solutions
Not applicable

Re: How to Remove Synthetic Key

My idea would be to use the Qualify statement.

Then all fields get the name of the table added which makes them unique

before the upload Qualify *;

Example

Qualify *;

Table1

Sales         (becames Table1.Sales)

Location    (becames Table1.Location)

Table2

Sales       (becames Table2.Sales)

Location      (becames Table2.Location)

Unqualify *;

6 Replies
MVP
MVP

Re: How to Remove Synthetic Key

Hi,

If most of the columns are same then you can concatenate both the tables this is best approach for this scenario.

Data:

LOAD

*,

'Shipping Order' AS DataType

FROM ShippingOrders;

Concatenate(Data)

LOAD

*,

'Backlog' AS DataType

FROM Backlog;

Hope this helps you.

Regards,

Jagan.

igdrazil
Contributor III

Re: How to Remove Synthetic Key

If you're going with Jagan's approach make sure your data sets are identifiable.

What i do to ease up my filter work, i add another column to each table with this formula:

SUBFIELD(ROWNO() & '|YourValueName', '|', 2) AS YourColumnName


Regards

Chris

lakwinder
Contributor III

Re: How to Remove Synthetic Key

Thinking out the box, why don't you keep the names the same of both fields on both table, but make a slight change on one of them.so they wouldn't concatenate.

Example

Table1

Sales

Location

Table2

Sales_

location

Not applicable

Re: How to Remove Synthetic Key

My idea would be to use the Qualify statement.

Then all fields get the name of the table added which makes them unique

before the upload Qualify *;

Example

Qualify *;

Table1

Sales         (becames Table1.Sales)

Location    (becames Table1.Location)

Table2

Sales       (becames Table2.Sales)

Location      (becames Table2.Location)

Unqualify *;

reddys310
Honored Contributor II

Re: How to Remove Synthetic Key

Hi Ryan,

All you have to concentrate on is to make sure no two field in a table are linked to a same dimension table.There should only be one field association between two tables to avoid synthatic keys. To achieve this make sure all the fields which need not be linked , are named uniquely. I would suggest you to go through this article for more information:

Synthetic Keys

Thanks,

Sangram

rheacock
New Contributor

Re: How to Remove Synthetic Key

This worked beautifully!

Thanks!