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

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

1 Solution

Accepted Solutions
Not applicable
Author

@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

View solution in original post

10 Replies
Anonymous
Not applicable
Author

Sreeharsha

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

Best Regards,     Bill

israrkhan
Specialist II
Specialist II

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
Author

@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
Author

Table1:

Customer as [Customer_ID],

Manager

Year

Month

Salary

Table2:

Product

Customer as [Customer_ID],

Year

Month

Date

PaidAmount


Try this.

Anonymous
Not applicable
Author

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
Specialist II
Specialist II

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
Author

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
Specialist II
Specialist II

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
Specialist II
Specialist II

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.