Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rheacock
Contributor II
Contributor II

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.

1 Solution

Accepted Solutions
Not applicable

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 *;

View solution in original post

6 Replies
jagan
Luminary Alumni
Luminary Alumni

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

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

Anonymous
Not applicable

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

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 *;

reddy-s
Master II
Master II

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
Contributor II
Contributor II
Author

This worked beautifully!

Thanks!