Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
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
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
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
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
Hi Sarathi,
Considering your Customer ID & CustomerID same, attached the data model.
Hope it will help you.
Thanks,
Neha
sorry typing mistake its CustomerID
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.
in your Test.qvw ContactID is missing in potential table