Sythetic Keys Detailed Understanding Required

[#1]

My question roams around the synthetic keys.

In below scenario is there any way to remove synthetic keys or else what could be the best solution.

For example if we have two tables as shown below where I would prefer to make Roll No as Primary key, unqualify Name and now 

Questions arises for "Marks"  column. How it should be considered in this data modelling structure.

1. Can I remove this synthetic key if yes then how as I don't see opportunity to alias qualify or comment. If I do so then i would not get my complete data.

2. I think in this scenario synthetic key for "Marks" column cant be avoided. Y/N ?

Table 1
Name	Roll No.	Marks	Standard
A	1	54	X
B	2	65	X
C	3	85	X
D	4	95	X
E	5	-	X

Table 2
Name	Roll No.	Marks
A	1	54
B	2	65
C	3	-
D	4	95
E	5	20

  I hope its clear enough to get the satisfactory replies.

[pokassov]

Hello!

You can change the names of fields to avoid synthetic fields.

Table1:

load

     Name,

     [Roll No.],

     Marks as Marks1,

     Standard

from ....;

Table2:

load

//     Name,

     [Roll No.],

     Marks as Marks,

from ....;

[marcus_sommer]

Have a look here: Synthetic Keys

- Marcus

[#4]

Thank you for your prompt reply,

however,

this will not reflect all the results as I am looking for.

For instance; If I follow what you said would give me results as follows

Roll No.	Marks	Marks1
1	54	54
2	65	65
3	85	-
4	95	95
5	-	20

If i have huge data and I don't know where i have values in either Marks1 or in Marks. It will not be actually fruitful.

Regards

[pokassov]

If you have situation in which you have only 3 possible case:

1. marks are equal for both table

2. mark is null for table 1

3. mark is null for table 2

you can do something like this:

Table1:

load

     Name,

     [Roll No.],

     Marks as Marks1,

     Standard

from ....;

left join (Table1)

load

//     Name,

     [Roll No.],

     Marks as Marks,

from ....;

Table2:

load

     Name,

     [Roll No.],

     if(not isnull(Marks1), Marks1, Marks2)     as Marks

     Standard

resident Table1;

drop Table1;

[timanshu]

hi,

Use mapping load.

Find Script and look for attached app for more help.

tab:

Load * inline

[

Name,Roll No.,Marks,Standard

A,1,54,X

B,2,65,X

C,3,85,X

D,4,95,X

E,5,,X

];

table2:

mapping load * inline

[

Name,Marks

A,54

B,65

C,

D,95

E,20

];

tab2:

Load *, if(len(Marks)<1, ApplyMap('table2',Name),Marks) as mark2 resident tab;

drop table tab;

drop Field Marks;

RENAME Field mark2 to Marks;

[avinashelite]

Hi Nihil,

If you want to derive only a few set of columns, then better use the Applaymap function and load the the mapping tables first and use applymap function. This will avoid the un-necessary joins and data in the qlikview app

[ankitaag]

Hi Nikhil,

Reply given by Сергей Покасов is correct I think, because this way you will not miss any of your record.

This is the best solution to your question