Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

which is the best approach to eliminate syntheic keys

Table1:

Customer:

Manager

Year

Month

Salary

Table2:

Product

Customer

Year

Month

Date

PaidAmount

These tables create syntheic keys, which is the best approach  to eliminate syntheic keys

Tags (2)
1 Solution

Accepted Solutions
Not applicable

Re: which is the best approach to eliminate syntheic keys

@Bill Markham thanks for your answer

Which one is the best way among below methods? why?


1: concatenate key

2: outer join

3: Rename the column name

10 Replies

Re: which is the best approach to eliminate syntheic keys

Sreeharsha

Either an outer join, or a couple of ApplyMap 's so you end up with a single table.

Best Regards,     Bill

israrkhan
Valued Contributor II

Re: which is the best approach to eliminate syntheic keys

I think Just Rename the columns and remove the synthetic keys, and link On Customer.

like below..

Table1:

Customer:

Manager

Year as C_Year

Month as C_Month

Salary

Table2:

Product

Customer

Year as P_Year

Month as  P_Month

Date

PaidAmount

Not applicable

Re: which is the best approach to eliminate syntheic keys

@Bill Markham thanks for your answer

Which one is the best way among below methods? why?


1: concatenate key

2: outer join

3: Rename the column name

Not applicable

Re: which is the best approach to eliminate syntheic keys

Table1:

Customer as [Customer_ID],

Manager

Year

Month

Salary

Table2:

Product

Customer as [Customer_ID],

Year

Month

Date

PaidAmount


Try this.

Re: which is the best approach to eliminate syntheic keys

Sreeharsha

There is invariably more than one way to achieve a required result, and the one to to use depends on the data involved , what you wish to achieve and your personal preferences.

If the there are not many fields and the lookup table is not too huge, then I always steer towards ApplyMap 's.

Best Regards,     Bill

salto
Valued Contributor II

Re: which is the best approach to eliminate syntheic keys

Hi,

the best way among methods depends on what you want to do with your data model, in each case.

In this case, if you rename Year and Month to C_Year,C_Month, P_Year, P_Month, instead of creating a composite key with the year and month in a single field, you will not be able to select a single date in the master calendar and obtain those dates.

I would load Year and Month in a single field, dropping the Year and Month fields, in both tables.

Hope this helps.

Not applicable

Re: which is the best approach to eliminate syntheic keys

Hi,

Actually I use the Qvc.Linktable from the Qlikview Components library.

Actuallly does create a linktable in the background with an autonumber, so you don't need to

worry too much about the performance.

Kind Regards,

Dion

israrkhan
Valued Contributor II

Re: which is the best approach to eliminate syntheic keys

Hi, SALTO


You Wrote : I would load Year and Month in a single field, dropping the Year and Month fields, in both tables.


in that case again you will have synthetic key in your tables, Year_Month to Year_Month and Customer To Customer.

................

and he have Date field in Table2, he can generate Master Calendar, Year, Month, etc By that Date Field...


and can you please explain me, from 3rd line of your post... why?

salto
Valued Contributor II

Re: which is the best approach to eliminate syntheic keys

Hello IsrarKhan,

ooops... you are right! My fault. My approach would leave a synthetic key.

Regarding my post (from the 3rd line), what I meant was to have a single Date Field in the data model. Maybe this is not a requirement - because you could have more than one master calendar and choose the appropriate date field.

Regards.

Community Browser