Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

My link table have $syn key

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.

28 Replies
Not applicable
Author

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.

jaswantc
Creator II
Creator II

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;

Not applicable
Author

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

jaswantc
Creator II
Creator II


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;

Not applicable
Author

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

Not applicable
Author

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

jaswantc
Creator II
Creator II

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
Specialist II
Specialist II

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

Not applicable
Author

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