topic Re: synthetic key in QlikView

synthetic key

ssriramin19 — Tue, 09 Apr 2013 12:12:28 GMT

Hi,

I have two tables A and B.Table A has 10 columns and table B has 10 columns .The columns names are same in both the tables. Because of the same column name synthetic key is getting formed.

Can anyone tell me how to resolve the synthetic key that is being formed

Thanks

Sriram

Re: synthetic key

Tue, 09 Apr 2013 12:17:48 GMT

Hi!

Rename the fields! As shown below:

Table_Test:

Load Campo1 as Campo_Novo

By Rebeca

Re: synthetic key

swuehl — Tue, 09 Apr 2013 12:22:53 GMT

Either concatenate your tables (I would have assumed auto-concatenation with two tables with same number of fields and field names anyway) --> CONCATENATE LOAD prefix

Or rename the fields in one of the tables using AS. Only keep the field named the same that serve as key between the two. If you need more than 1 field as key, use a combined key.

Re: synthetic key

ssriramin19 — Tue, 09 Apr 2013 12:39:58 GMT

If it 10 columns ,I will be able to rename the fields. What if there are 100 fields in table A and B. Each field will be joined with other table and renaming 100 fields is very difficult .This is just scenario , was asked in one interview question .

Can anyone comment on this

Re: synthetic key

advait_thakur — Tue, 09 Apr 2013 12:43:03 GMT

Hello Sri,

You may try with an approach which swuehl has suggested.

Make the aliases of fields or use Qualify/Unqualify statement. And if all fields are common then Qlikview will Auto-Concatenate.

This will resolve your Synthetic Keys.

Thanks

Advait

Re: synthetic key

narender123 — Tue, 09 Apr 2013 13:01:01 GMT

In script you can concatenate your both table as both table have same column name.

eg.

TableA:

---------(your data);

concatenate(TableB) OR concatenate

TableB:

---------(your data);

(alternate)

you can also use left join between table

eg.

TableA:

---------(your data);

left join(TableB)

TableB:

---------(your data);

You can also rename your field but changing name for more column is not a standard way.So use concatenation .

Try it.

Re: synthetic key

Tue, 09 Apr 2013 13:15:24 GMT

Hi ,

You can use QUALIFY statement to remove Synthetic key

Eg:

Load * Inline [A,B,C

1,0,5

2,0,6

3,0,7];

QUALIFY A,B;

Load * Inline [A,B,C,D

4,0,1,9

5,0,2,10

6,0,3,11];

UNQUALIFY A,B;

Let me know if this help ..............

Re: synthetic key

Anonymous — Tue, 09 Apr 2013 14:01:03 GMT

HI,

What is your aim? Do you want to have one table with the datas of the table A AND the table B or do you want to keep 2 different tables?

Re: synthetic key

Tue, 09 Apr 2013 14:15:11 GMT

Hi , you can put also "QUALIFY * ;" before LOAD table name and it will rename all the fields like

' Table . Field '

and you will have

A.COL1

A.COL2

A.COL10

and

B.COL1

B.COL2

B.COL10

Re: synthetic key

Tue, 09 Apr 2013 16:18:04 GMT

HI Sriram ,

The Best way is to Concatenate them . But if even though you want to have 2 Separate Tables then

You can use "Qualify" keyword .

Qualify Field1 , Field2 ,.....

before the Load statement .

Please let me know if there is anything .

Re: synthetic key

Thu, 26 Jun 2014 19:41:55 GMT

Hi Sriram,

TABLE A TABLEB

COL1 COL1

COL2 COL2

COL3 COL3

..... ....

COL10 COL10

Before Load, give Qualify and Unqualify keywords

QUALIFY * ;

UNQUALIFY COL1 ;

Re: synthetic key

Thu, 26 Jun 2014 20:40:31 GMT

"Combined key" >< syn. key ???