topic Re: My link table have $syn key in QlikView

My link table have $syn key

Wed, 16 Oct 2013 07:57:56 GMT

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.

Re: My link table have $syn key

Wed, 16 Oct 2013 08:02:14 GMT

Enclosed my QV doc

Re: My link table have $syn key

bbi_mba_76 — Wed, 16 Oct 2013 08:02:51 GMT

Hi,

you could concatenate the fields to create the key you want

Re: My link table have $syn key

jagan — Wed, 16 Oct 2013 08:10:53 GMT

Hi Paul,

Concatenate the Synthetic key fields in Link table and form a key and do the same in other tables. Now rename the synthetic key field names in link table, so that it avoids Synthetic keys.

Regards,

Jagan.

Re: Re: My link table have $syn key

Wed, 16 Oct 2013 08:47:16 GMT

Hi Sir

I am unable to understand what you mean. can you pls help me show me how to remove them from my QV doc.

Remark :-

For testing , just create folder on C:\TEMP

and copy the unzip into above folder.

Paul

Re: My link table have $syn key

bbi_mba_76 — Wed, 16 Oct 2013 09:36:42 GMT

Hi,

try to remove the following fields:

Date and Ref.No_ from the inquiry1 table

Date and Ref.No from the quotation table

Date, Ref.No and Ref.No_ from the linktable

Re: My link table have $syn key

Wed, 16 Oct 2013 10:10:30 GMT

Hi Sir

You ask me to remove the date and Ref no field from Inquiry Table.

My question is if i remove the Date field , may i know am i still able link the date to master calendar , since it deleted?

Are you refer to delete field name at my inquiry script. Since i don't know how to delete field , i try to rename it to another name. will it do the job ?

Re: My link table have $syn key

bbi_mba_76 — Wed, 16 Oct 2013 10:26:22 GMT

Yes, you are right, the date field must be in the linktable to join the master calendar.

You could rename the fields or drop them, it is quite the same: if you rename maybe it is easier for debug.

Drop Field

One or several QlikView fields can be dropped from the data model and thus from memory at any time during script execution, by means of a drop field statement.
The syntax is:

drop field[ s ] fieldname [ , fieldname2 ...] [from tablename1 [ , tablename2 ...]]

Both drop field and drop fields are allowed forms with no difference in effect.
If no table is specified, the field will be dropped from all tables where it occurs.

Examples:

drop field A;

drop fields A,B;

drop field A from X;

drop fields A,B from X,Y;

Re: My link table have $syn key

Wed, 16 Oct 2013 11:04:45 GMT

Hi MBA

I have try to rename both Quotation and Inquiry table ref no and date field to diff name.

Now my issue is link table , what should i do ? which i believe i need to do some thing on the link table , in order to drop the syn key.

Re: My link table have $syn key

bbi_mba_76 — Wed, 16 Oct 2013 11:10:40 GMT

Sorry, I wasn't so clear...

In the linktable you should have only Date, Key_I and Key_Q

In this way with the Date field you join the MasterCalendar, with Key_I the inquiry table and Key_Q the Quotation table.

Re: Re: My link table have $syn key

Wed, 16 Oct 2013 13:41:42 GMT

Hi MBA

I have follow your instruction.

Now may i ask you how to remove the $sync 3 table from my data structure ?

Re: Re: My link table have $syn key

bbi_mba_76 — Wed, 16 Oct 2013 14:21:58 GMT

In the Inquiry and quotation tables there is again the Date field, this is generating the sync table, try to rename them in Q.Date and I.Date as last action in your script.

Re: Re: Re: My link table have $syn key

Thu, 17 Oct 2013 01:24:21 GMT

Hi Sir

since rename the date field , Now only issue is how to make the calendar island link to quotation and inquiry. any idea ?

Re: My link table have $syn key

SunilChauhan — Thu, 17 Oct 2013 03:13:28 GMT

Hi paul,

why not you make link table where you have Date ,Key_I,Key_Q

and Enquiry has Key_I and rename date and rename field which create syn key

and question table has Key_Q and rename date field and rename fields which create syn key

and on this date link master calender to Linktable.

see the attached image

hope this helps

SUNIL

Re: Re: My link table have $syn key

Thu, 17 Oct 2013 03:52:47 GMT

Hi Sir

After i rename the date field , now my issue is i get loop , this is because in my link table i am not able to add Date filed,

LinkTable:

load Date&[Ref. No_I] as Key_I from $(vQVDPath1)Inquiry.qvd (qvd);

concatenate (LinkTable)

load Date&[Ref. No] as Key_I, Key_Q from $(vQVDPath1)Quotation.qvd (qvd);

Re: Re: My link table have $syn key

Thu, 17 Oct 2013 03:54:17 GMT

Hi sir

I for get to mentioned that , Once i add Date field at link table , i will get syn key table.

Re: My link table have $syn key

SunilChauhan — Thu, 17 Oct 2013 04:08:03 GMT

Hi paul

you are doing correct .only thing need to do is add date field to Linked table and rename date field in both question and inquiry.

i mean in question

date as Q.Date and

in inquiry

Date as I.Date

modify linked table as

Linked:

Load

Key_I

Date

from Inquiry;

concatenate(Linked)

Load

Key_Q

Date

from question

hope this will help

Sunil

Re: My link table have $syn key

Anonymous — Thu, 17 Oct 2013 04:37:32 GMT

Paul,

Can you add below code to your appa and see hope it will work.

Drop

Table

LinkTable;

LinkTable:
LOAD

Date

& '|' & [Ref. No_]

& '|' & [Key_I]

Link_Key1

,
Date

Date

Resident

Inquiry1;

Concatenate

[LinkTable]:
LOAD

Distinct

Date

,
Date

& '|' & [Ref. No]

& '|' & [Key_Q]

Link_Key2

Resident

Quotation;

Inquiry2:
LOAD

*,
Date

& '|' & [Ref. No]

& '|' & [Key_Q]

Link_Key2

Resident

Inquiry1;

Drop

Fields

Date

, [Ref. No]

, [Key_Q]

from

Inquiry2;

RENAME

Table

Inquiry2 to

Inquiry1;

Quotation1:
LOAD

*,
Date

& '|' & [Ref. No]

& '|' & [Key_Q]

Link_Key2

Resident

Quotation;

Drop

Fields

Date

, [Ref. No_]

, [Key_I]

from

Quotation1;

RENAME

Table

Quotation1 to

Quotation;

Drop

Tables

Quotation1, Inquiry2;

Re: My link table have $syn key

Thu, 17 Oct 2013 04:39:16 GMT

Hi Sunil

I think i have to live with the $syn key table , most impt the table is still working fine. just that it look not so nice. Thank you very much.

Paul

Re: Re: My link table have $syn key

Anonymous — Thu, 17 Oct 2013 04:40:50 GMT

Hi Paul,

Just try with below app.