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: 
Not applicable

Dimension in multiple tables - avoiding synthetic key

Hey There,

Looking for some advice on a load script.  The tables look something like I've outlined below.  If I were to just load each table as per below, then obviously there'd be a synthetic table created based on the BUSINESS_ID.  What's the best way to avoid this?  I initially did a join (so joining manufacturer to business, for example), but this doesn't seem to be very efficient.  What are my other options for avoiding the synthetic key?  Any help would be greatly appreciated.

BUSINESS:

LOAD BUSINESS_ID,

     BUSINESS_DESCRIPTION,

     DIVISION

FROM

[BUSINESS.xls]

(biff, embedded labels, table is Sheet1$);

MANUFACTURER:

LOAD MANUFACTURER_NAME,

     BUSINESS_ID

FROM

[MANUFACTURER.xls]

(biff, embedded labels, table is Sheet1$);

AREA:

LOAD BUSINESS_ID,

     ADDRESS,

     SUBURB,

FROM

[AREA.xls]

(biff, embedded labels, table is Sheet1$);

6 Replies
ali_hijazi
Partner - Master II
Partner - Master II

this won't create a synthetic key

all tables will be associated by the business_id field

I can walk on water when it freezes
jpenuliar
Partner - Specialist III
Partner - Specialist III

Looking at your listed load script,

the only common field i can see is Business ID, so theoretically, you should not have synth keys.

But if there are more fields you havent listed, it is possible to have synth keys.

One way to avoid synth key is to QUALIFY the loaded tables.

Not applicable
Author

Hey There,

Thanks for the response.  I dunno why I thought that would create synthetic keys.  What's happening in my load script is that associations are being created when those three are loaded (as you mentioned, no synth keys).  Following that, I have another table that contains items from all three tables:

TRANSACTION_TABLE:

LOAD BUSINESS_DESCRIPTION,

          MANUFACTURER_NAME,

          ADDRESS,

          SALES_AMOUNT,

          SALES_DATE

FROM

[TRANSACTION_TABLE.xls]

(biff, embedded labels, table is Sheet1$);

This then creates the synthetic keys.  So I'm wondering if there is way to avoid the synth key in this case without joining the first three tables first and creating a mapping key (sorry for explaining so poorly initially)

jpenuliar
Partner - Specialist III
Partner - Specialist III

can you attach a screenshot of your Data Model (ctrl+T).

for Synth keys which you think are not really Important(key fields) you can just rename them,

another way is to Qualify all fields (*) or qualify only the fields you want, in this case, synth field.

There are plenty of ways to avoid Synth keys if you dig a bit more i the forums, might be useful to your current requirement.

JP

jonathandienst
Partner - Champion III
Partner - Champion III

Hi

You need to choose by which key field (of BUSINESS_DESCRIPTION, MANUFACTURER_NAME, ADDRESS) you want to link the transactions to the main table.


Are the other possible key fields in this spreadsheet redundant (ie the same as what has already been loaded in the main table), or do they differ (ie BUSINESS_DESCRIPTION is associated with a different MANUFACTURER_NAME and/or ADDRESS than in the main table)?

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
thomaswrieck
Partner - Creator
Partner - Creator

Hi Nathan,

based on the info you provided …

my guess is it makes sense to join the first two tables and build a combined key in the new table from BUSINESS_DESCRIPTION and MANUFACTURER_NAME (BUSINESS_DESCRIPTION &’_’ & MANUFACTURER_NAME as %BDMAN) and rename the fields to something else in both table. Then load the Transaction Table also with the new Key like shown above and rename the two fields here as well.

Then load the Area table like you do right now ….

If you could send me a sample qvw with some fake data I could show you better what I mean …