Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How do I get rid of $Syn table..how to create a link table..Please help

Hi All,

I am getting  a Syn table while creating my dashboard. I am having  a two commonm fileds in two tables and I want to keep both the fields. I am not sure how to create  alink table...can anyone hlpe me creating  a link table.

Here my code: My 3 tables are USPTO,DMS and MHGroup, I am also attaching view of my table.

USPTO:
Load *;
SELECT APPNUM, replace(CODE,'.', '_') as CODE,dms_document_number as DOCNUM
FROM USPTO;

DMS:
Load *;
Select SUBCLASS_ALIAS as CODE,C4ALIAS,DOCNUM as DMSDOCNUM
FROM [PatentDMS_Prd].[MHGROUP].[DOCMASTER]
       WHERE
      Type = 'D' and C_ALIAS = 'PATENTOFFICE' and C6ALIAS like 'uspto_%';
   
     
      MHGROUP: inner Join(DMS)
Load *  WHERE EXISTS(APPNUM);

SELECT  [CUSTOM_ALIAS] as C4ALIAS
      ,[C_DESCRIPT] as APPNUM
        FROM [PatentDMS_Prd].[MHGROUP].[CUSTOM4];

Regards,

Vikas

5 Replies
sushil353
Master II
Master II

Hi Try the below code

USPTO:
Load

autonumberhash128(APPNUM,CODE) as KEY,

APPNUM as USPTO_APPNUM,

CODE as USPTO_CODE,

DOCNUM;
SELECT APPNUM, replace(CODE,'.', '_') as CODE,dms_document_number as DOCNUM
FROM USPTO;

DMS:
Load

autonumberhash128(APPNUM,CODE) as KEY,

CODE,

C4ALIAS,

DMSDOCNUM,

APPNUM;
Select SUBCLASS_ALIAS as CODE,C4ALIAS,DOCNUM as DMSDOCNUM
FROM [PatentDMS_Prd].[MHGROUP].[DOCMASTER]
       WHERE
      Type = 'D' and C_ALIAS = 'PATENTOFFICE' and C6ALIAS like 'uspto_%';
   
     
      MHGROUP: inner Join(DMS)
Load *  WHERE EXISTS(APPNUM);

SELECT  [CUSTOM_ALIAS] as C4ALIAS
      ,[C_DESCRIPT] as APPNUM
        FROM [PatentDMS_Prd].[MHGROUP].[CUSTOM4];

HTH

Sushil

Not applicable
Author

Hi Sushil,

No its not working..it threw an error

Field not found - <APPNUM>

Select SUBCLASS_ALIAS as CODE,C4ALIAS,DOCNUM as DMSDOCNUM

FROM [PatentDMS_Prd].[MHGROUP].[DOCMASTER]

       WHERE

      Type = 'D' and C_ALIAS = 'PATENTOFFICE' and C6ALIAS like 'uspto_%'

Regards,

Vikas

sushil353
Master II
Master II

Ok, Try the below one and if that wont work then attach a sample file .

USPTO:
Load

autonumberhash128(APPNUM,CODE) as KEY,

APPNUM as USPTO_APPNUM,

CODE as USPTO_CODE,

DOCNUM;
SELECT APPNUM, replace(CODE,'.', '_') as CODE,dms_document_number as DOCNUM
FROM USPTO;

Temp_DMS:
Load

CODE,

C4ALIAS,

DMSDOCNUM,

APPNUM;
Select SUBCLASS_ALIAS as CODE,C4ALIAS,DOCNUM as DMSDOCNUM
FROM [PatentDMS_Prd].[MHGROUP].[DOCMASTER]
       WHERE
      Type = 'D' and C_ALIAS = 'PATENTOFFICE' and C6ALIAS like 'uspto_%';
   
     
      MHGROUP: inner Join(Temp_DMS)
Load *  WHERE EXISTS(APPNUM);

SELECT  [CUSTOM_ALIAS] as C4ALIAS
      ,[C_DESCRIPT] as APPNUM
        FROM [PatentDMS_Prd].[MHGROUP].[CUSTOM4];

DMS:

Load

autonumberhash128(APPNUM,CODE) as KEY, *

resident Temp_DMS;


Drop table Temp_DMS;


Gysbert_Wassenaar

The synthetic key in you document is not a problem. I think you can leave it in place. But if you do want to get rid of it you can use the Qlikview Components library to quickly create link tables. See the LinkTable.qvw document in the Examples folder after you've extracted the files from the qvc-8.1.zip download.


talk is cheap, supply exceeds demand
Bill_Britt
Former Employee
Former Employee

Here is a great link on this.

http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/16/synthetic-keys

Bill - Principal Technical Support Engineer at Qlik
To help users find verified answers, please don't forget to use the "Accept as Solution" button on any posts that helped you resolve your problem or question.