My link table have $syn key

[#1]

Hi All

I have a data model , which i already created the link table key. but i don't know how to remove the syn key.

[#2]

Hi Jaswant

Many thank for yr code , i did try but fail half way. never mind i do keep yr script , as i hv found quite a few of user command.

[Anonymous]

See final app, Which is binary loaded your app and then added my code to create lin table

New code:

// New code from Jaswant

Drop Table LinkTable;

LinkTable:
LOAD
Date & '|' & [Ref. No_] & '|' & [Key_I] as Link_Key1,
Date as Date
Resident Inquiry1;

Concatenate [LinkTable]:
LOAD Distinct
Date as Date,
Date & '|' & [Ref. No] & '|' & [Key_Q] as Link_Key2
Resident Quotation;

Inquiry2:
NoConcatenate
LOAD
Date & '|' & [Ref. No_] & '|' & [Key_I] as Link_Key1,
*
Resident Inquiry1;



Quotation1:
NoConcatenate
LOAD
Date & '|' & [Ref. No] & '|' & [Key_Q] as Link_Key2,
*
Resident Quotation;



Drop Tables Quotation, Inquiry1;

Drop fields Date , [Ref. No] , [Key_Q] from Quotation1;

Drop fields Date , [Ref. No_] , [Key_I] from Inquiry2;

[#4]

Hi Jas

Many thank , i just want to give up and you give me the surprise.

Finally you have force all the way to make the data model as my wish. the issue now is you drop some of the field which is impt for me. for example Ref. No_I & Ref. No

it there any way you can put back above field ?

Paul

[Anonymous · Accepted solution]

Hi Paul,

Added fields bac to your model.

Here it is your final model: and new code:

// New code from Jaswant
Drop Table LinkTable;

LinkTable:
LOAD
Date & '|' & [Ref. No_] & '|' & [Key_I] as Link_Key1,
Date as Date
Resident Inquiry1;

Concatenate [LinkTable]:
LOAD Distinct
Date as Date,
Date & '|' & [Ref. No] & '|' & [Key_Q] as Link_Key2
Resident Quotation;

Inquiry2:
NoConcatenate
LOAD
Date & '|' & [Ref. No_] & '|' & [Key_I] as Link_Key1,
*
Resident Inquiry1;


//RENAME Table Inquiry2 to Inquiry1;

Quotation1:
NoConcatenate
LOAD
Date & '|' & [Ref. No] & '|' & [Key_Q] as Link_Key2,
*
Resident Quotation;


//RENAME Table Quotation1 to Quotation;


Drop Tables Quotation, Inquiry1;

Drop field Date from Quotation1, Inquiry2;

[#6]

Hi Jas

you are the few how are good in create link table. i do learn some trick here. thank you it work now.

Paul

[#7]

Hi Jas

i like to ask you .

1. NoConcatenate command is use , why ? as i remove it , it still working fine.

2. Date & '|' & [Ref. No] & '|' & [Key_Q] as Link_Key2, you using "|" , what i the purpose ?  i try below also work :-

Date & [Ref. No] & & [Key_Q] as Link_Key2,

3. What it the logic for link key must link between date and ref number ?

Hope you can share with me.

Paul

[Anonymous]

Hi Paul,

1. NoConcatenate command is use , why ? as i remove it , it still working fine.

Ans: I thought if columns names same then rather than creating new table it will append to previous table thats why i used, If it works for you then not sure, will check that later

2. Date & '|' & [Ref. No] & '|' & [Key_Q] as Link_Key2, you using "|" , what i the purpose ?  i try below also work :-

Date & [Ref. No] & & [Key_Q] as Link_Key2,

  Ans: That is your which how you create composite key, I used to follow standard of using "|" to identify what fields parts of composit key thats why i used "|". It will wor without this one too.

3. What it the logic for link key must link between date and ref number ?

Ans: Identify all common fields and move them onto link table and then create same link key in dimensions and then either drop or rename those fields.

Thanks,

Jaswant

[nizamsha]

want to reomove ur synthetic key mean there are many ways to remove the synthetic key ..

1)use alias

2)use qualify and unqualifty

3) concatenate two field and make it as one eg:firstname & '-' &   lastname as name

4)use autonumber before concatenate two fields eg: autonumber (firstname & '-' &   lastname ) as name

5:use hash128 eg: hash128(firstname & '-' &   lastname ) as name

each and every type has a disadvantage so choose according to ur requirement  surely it will work

[#10]

Hi Chowdary, I try to learn how to remove sync key by follow your approach , i fail again.  can you take a look at below link ?

http://community.qlik.com/message/411581#411581