topic Re: syn keys in QlikView

syn keys

Sat, 12 Sep 2015 16:19:39 GMT

Do I need to remove the syn.key from these tables?
If so where do I start?

Re: syn keys

Sat, 12 Sep 2015 16:22:38 GMT

Re: syn keys

ecolomer — Sat, 12 Sep 2015 17:19:18 GMT

Hi,

for remove the Syn Key is necesary each field are named diferent for each table

for example:

In Customer:

COMPANY_ID as COMPANY_ID_C

In Branch:

COMPANY_ID as COMPANY_ID_B

Re: syn keys

Sat, 12 Sep 2015 17:43:07 GMT

Hi Chris,

Yes, you need to remove Sync key from your data model.

There are different ways to remove it.

1. Remove columns that are not required and creating sync key.

2. Rename Columns that are not directly related.

3. Concatenate two tables if they have multiple columns in common

4. Create a key column either by using concatenate or autonumber function

5. Creating link table.

Its depend on your data and requirement which one is suitable for you.

Hope this cleared your doubt

Regards

Priyanka

Re: syn keys

evan_kurowski — Sat, 12 Sep 2015 17:46:17 GMT

Hello Chris,

Synthetic keys in QlikView represent in other SQL or DDL languages a compound join.

In QlikView when we associate on a single field the SQL might be like

WHERE A.Key = B.Key

A synthetic key arises in QlikView when the SQL involves multiple joins between the same two tables

WHERE A.COMPANY = B.COMPANY AND
A.WAREHOUSE = B.WAREHOUSE

Often a synthetic key doesn't mean the data relationships in the data model have broken down, but it does create complexity in terms of QlikView design and also is very often a solid indicator of inefficiency in data-model design.

Some drawbacks of synthetic keys:

Data model preview gets more complicated to discern
Data model removes preview of field data from their native tables and preview only shows up in syn key table
The synthetic tables aren't referenceable in terms of functions, so you can't "use" the synthetic tables in the same way you would explicity defined table

In your case, your data model need 3 adjustments:

Several of the dimension tables are reusing the field [COMPANY_ID]. When you remove this from everything except the [Company] table, it allows your [Transaction] to [Company] associations to determine the full amount of [Company] information necessary for reporting. The COMPANY_ID sotred in all the other dimension tables is likely redundant.
The VOLUME field isn't an atribute of an ITEM, it is an attribute of a [Transaction]. (if the VOLUME field reports the amount of item moved per transaction). Therefore it doesn't belong in the dimension table.
The Calendar should connect directly to the [Transaction] table based on a date. Don't involve dimension tables when connecting a calendar table to a transaction tables.

If you remodel your association structure like the attached, you should be good to go and the synthetic key cleared up.

Re: syn keys

Sat, 12 Sep 2015 20:02:52 GMT

Awesome! Thanks Evan

I came right with solution point 1-2 before I got your reply.

But now I see I'm batting to connect the transaction date as you mentioned in your point 3.

How do I do this?

I only have a personal edition of QlikView so can't open your .qvw file at this stage.

Re: syn keys

Sat, 12 Sep 2015 20:15:00 GMT

my load script for transactions and date are attached.

I need to start the Financial Year in March (i.e. Fin Month 1 = March and Fin Month 12= Feb)

Could you help with this?

// **********Transactions*******

Transactions:

LOAD COMPANY_ID,

CUSTOMER_ID,

TRANSACTION_DATE,

LOAD_DATE,

SALES_VALUE,

DOCUMENT_NUMBER,

ITEM_ID,

DISCOUNT_VALUE,

OTHER_DISCOUNT,

ORDER_NO,

PRICE,

QTY_INVOICED,

TRANSACTION_TYPE,

WAREHOUSE_ID,

BRANCH_ID,

COST_VALUE,

VOLUME AS TransVolume,

TAX_AMOUNT,

DOCUMENT_TYPE

FROM

$(vPathName)Transactions.xls

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

// **********Date*******

Dates:

LOAD CAL_DATE,

COMPANY_ID,

CAL_DATE_DESC,

CAL_DAY_OF_WEEK,

CAL_END_OF_MONTH_FLAG,

CAL_END_OF_WEEK_FLAG,

CAL_END_OF_YEAR_FLAG,

CAL_MONTH_DESC,

CAL_MONTH_ID,

CAL_MONTH_NUMBER,

CAL_MONTH_NUMBER_DESC,

CAL_QUARTER_DESC,

CAL_YEAR_QUARTER_DESC,

CAL_QUARTER_ID,

CAL_YEAR_QUARTER_ID,

CAL_WEEK_DESC,

CAL_WEEK_END_DATE,

CAL_WEEK_ID,

CAL_WEEK_IN_MONTH,

CAL_WEEK_IN_MONTH_DESC,

CAL_WEEK_NUMBER,

CAL_WEEK_START_DATE,

CAL_WEEKS_IN_MONTH,

CAL_YEAR_DESC,

CAL_YEAR_ID,

CAL_YEARMONTH,

DATE_KEY,

FIN_YEAR_ID,

FIN_YEAR_MONTH,

FIN_MONTH_NUMBER,

FIN_YEAR_DESC,

FIN_QUARTER_DESC

FROM

$(vPAthName)Dates.xls

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

Re: syn keys

evan_kurowski — Sat, 12 Sep 2015 20:53:52 GMT

Sure Chris, connect tables [Dates] & [Transactions] by dropping [COMPANY_ID] from [Dates] and then aliasing one of the fields from [Dates] to [TRANSACTION_DATE].

Looking at the list I guess [CAL_DATE] and [TRANSACTION_DATE] would be equivalent, but only you can verify for sure. For the association to work, the format between the two date fields must be the same.

I prefer a pure integer for calendar associations, but a formatted date field works too.

p.s. Be careful with the case-sensitivity of your variables. I see you have $(vPathName) and $(vPAthName) in your script, these will be treated as two distinct variables.

Re: syn keys

Sun, 13 Sep 2015 07:59:30 GMT

Thanks Evan, will try that and let you know.

Appreciate the feedback.