Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Sythetic Keys Detailed Understanding Required

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
NameRoll No.MarksStandard
A154X
B265X
C385X
D495X
E5-X

 

Table 2
NameRoll No.Marks
A154
B265
C3-
D495
E520

  I hope its clear enough to get the satisfactory replies.

7 Replies
pokassov
Specialist
Specialist

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

Not applicable
Author

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.MarksMarks1
15454
26565
385-
49595
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
Specialist
Specialist

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
Creator III
Creator III

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
Partner - Creator III
Partner - Creator III

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