topic Re: How do I get rid of $Syn table..how to create a link table..Please help in QlikView

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

Wed, 23 Oct 2013 05:02:06 GMT

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

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

sushil353 — Wed, 23 Oct 2013 05:11:05 GMT

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

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

Wed, 23 Oct 2013 05:22:26 GMT

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

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

sushil353 — Wed, 23 Oct 2013 05:57:38 GMT

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,

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;

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

Gysbert_Wassenaar — Wed, 23 Oct 2013 06:08:33 GMT

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.

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

Bill_Britt — Wed, 23 Oct 2013 10:10:52 GMT

Here is a great link on this.

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