which is the best approach to eliminate syntheic keys

[#1]

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

[Anonymous]

Sreeharsha

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

Best Regards,     Bill

[israrkhan]

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

[#4 · Accepted solution]

@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

[#5]

Table1:

Customer as [Customer_ID],

Manager

Year

Month

Salary

Table2:

Product

Customer as [Customer_ID],

Year

Month

Date

PaidAmount

Try this.

[Anonymous]

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]

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.

[#8]

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]

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]

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.