topic Re: Sythetic Keys Detailed Understanding Required in QlikView

Sythetic Keys Detailed Understanding Required

Mon, 25 May 2015 06:30:20 GMT

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.

Re: Sythetic Keys Detailed Understanding Required

pokassov — Mon, 25 May 2015 06:37:08 GMT

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 ....;

Re: Sythetic Keys Detailed Understanding Required

marcus_sommer — Mon, 25 May 2015 06:39:44 GMT

Have a look here: Synthetic Keys

- Marcus

Re: Sythetic Keys Detailed Understanding Required

Mon, 25 May 2015 06:44:24 GMT

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

Re: Sythetic Keys Detailed Understanding Required

pokassov — Mon, 25 May 2015 06:49:28 GMT

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;

Re: Sythetic Keys Detailed Understanding Required

timanshu — Mon, 25 May 2015 07:26:34 GMT

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

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;

Re: Sythetic Keys Detailed Understanding Required

avinashelite — Mon, 25 May 2015 07:33:45 GMT

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

Re: Sythetic Keys Detailed Understanding Required

ankitaag — Mon, 25 May 2015 09:07:27 GMT

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