Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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;
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.
Here is a great link on this.
http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/16/synthetic-keys