topic how to Remove synthetic key in QlikView

how to Remove synthetic key

Anonymous — Tue, 21 Mar 2017 06:53:42 GMT

Table A

a	b	c

Table B

d	b	e

table C

f	b	h

main

b	h	g	h	i	j

Table A,b,c are connected with Main table and also with Table A

how to remove sythentic key without renaming and remove the field

Re: how to Remove synthetic key

Anonymous — Tue, 21 Mar 2017 07:03:43 GMT

Try this

TableA:

LOAD * Inline

[

A,B,C

];

TableB:

LOAD * Inline

[

D,B,E

];

TableC:

LOAD * Inline

[

F,B,H

];

QUALIFY H ;

Main:

LOAD * Inline

[

B,H,G,I,J

];

Or share expected output.

Re: how to Remove synthetic key

neha_shirsath — Tue, 21 Mar 2017 07:07:47 GMT

Hi Sarthi,

How field name will be same in main table? (In your given sample example "h" field is repeated).

Is it same in your real data also?

Thanks,

Neha

Re: how to Remove synthetic key

rahulpawarb — Tue, 21 Mar 2017 07:08:22 GMT

Hello Sarathi,

As per my understanding, Table A is linked with Table B, Table C & Table main on field b; BTW, Table C is linked to main Table on field b & h fields. Considering this please refer below sample script to remove synthetic keys from present data model:

- Used QUALIFY statement for field b on Table C

- Created complex key on field b & h in Table C and Table main

[Table A]:
LOAD * INLINE [
a, b, c
];


[Table B]:
LOAD * INLINE [
d, b, e
];


QUALIFY b;
[Table C]:
LOAD f, b, b &'|' & h AS Key;
LOAD * INLINE [
f, b, h
];
UNQUALIFY b;


main:
LOAD b, g, i, j, b &'|' & h AS Key;
LOAD * INLINE [
b, h, g, i, j
];

P.S.: Field names must be unique within table main (field h is appeared two times).

Hope this will be helpful.

Regards!

Rahul

Re: how to Remove synthetic key

Anonymous — Tue, 21 Mar 2017 07:34:11 GMT

sorry i am beginner this is real table

Customer Table

Customer ID,Customer name, Address

Contact Table

Customer ID ContactID,Contactname,Contact Address

Visit Table

CustomerID,ContactID,VisitID,Visit place,PotentialID

potential Table

PotentialID,CustomerID,ContactID,PotentialName

this is real table,how to remove synthetic key

Re: how to Remove synthetic key

rahulpawarb — Tue, 21 Mar 2017 07:45:51 GMT

Hello Sarathi,

Could you please share the relationship details between given tables (How one table linked with other table?) ?

Observation: Given table has two different naming style for field Customer ID & CustomerID. Please correct it if both field carries same value.

Regards!

Rahul

Re: how to Remove synthetic key

neha_shirsath — Tue, 21 Mar 2017 07:48:26 GMT

Hi Sarathi,

Considering your Customer ID & CustomerID same, attached the data model.

Hope it will help you.

Thanks,

Neha

Re: how to Remove synthetic key

Anonymous — Tue, 21 Mar 2017 08:04:29 GMT

sorry typing mistake its CustomerID

Re: how to Remove synthetic key

Anonymous — Tue, 21 Mar 2017 08:14:12 GMT

I think we can remove the sync keys by creating a combined key like showed in the attachment. But I seriously think you should change the schema of your tables as there is a high chance of sync keys and loops formations, Think you can denormalize your tables.

Re: how to Remove synthetic key

Anonymous — Tue, 21 Mar 2017 08:15:36 GMT

in your Test.qvw ContactID is missing in potential table

Re: how to Remove synthetic key

neha_shirsath — Tue, 21 Mar 2017 08:54:28 GMT

Hi,

I've removed contactID from potential table as the same field data we are getting from Contact table.

so no need of that field in Potential table.

If the fields data is different then we have to create Composite key for that.

Thanks,

Neha