topic Link Table? in QlikView

Link Table?

Nagaraju_KCS — Sat, 23 Nov 2013 19:12:08 GMT

Hi Community,

How to create linktable,

LinkTableTemp:

LOAD C_SERVICE_CENTRE,

C_SAMPLE_MONTH,

C_BRANCH,

FILE_NO,

C_LOAN_PRODUCT

Resident Compliance_Report;

Concatenate

Load

FILE_NO,

BRANCH As LinkBranch,

SERVICE_CENTRE As Link_ServiceCentre,

LOAN_PRODUCT As Link_Product,

SAMPLE_MONTH As Linkdate

Resident DatabaseData;

Concatenate

Load

FILE_NO,

F_BRANCH As LinkBranch,

F_SERVICE_CENTRE As Link_ServiceCentre,

F_SAMPLE_MONTH As Linkdate

Resident File_Details;

Concatenate

Load

FILE_NO,

QR_BRANCH As LinkBranch,

QR_SERVICE_CENTRE As Link_ServiceCentre,

QR_SAMPLE_MONTH As Linkdate

Resident Question_Review_Status;

Concatenate

Load

FILE_NO,

QS_BRANCH As LinkBranch,

QS_SERVICE_CENTRE As Link_ServiceCentre,

QS_SAMPLE_MONTH As Linkdate

Resident Question_Status_Report;

Concatenate

Load

FILE_NO,

CO_BRANCH As LinkBranch,

CO_SERVICE_CENTRE As Link_ServiceCentre,

CO_SAMPLE_MONTH As Linkdate

Resident Compliance_Overdue_Report;

LinkTable:

Load Distinct

FILE_NO,

Linkdate,

LinkBranch,

Link_Product,

Link_Product As LOAN_PROD_CODE,

Link_ServiceCentre,

Link_ServiceCentre As SERVICE_CENTRE_CODE,

year(Linkdate) As Year,

Month(Linkdate) As Month,

AutoNumberHash128(FILE_NO,Linkdate) As Link_Field

Resident LinkTableTemp;

DROP Table LinkTableTemp;

Thanks in advance.....

Re: Link Table?

Anonymous — Sat, 23 Nov 2013 20:15:54 GMT

Can you send your sample file, Just wanted to take a look .

Re: Link Table?

Anonymous — Sat, 23 Nov 2013 20:19:46 GMT

- Identify common fields between common tables

- Create concatenated Key

- remove common fields from source tables and move them onto link table which we have taken in link table

- load common table with key

Re: Link Table?

Nagaraju_KCS — Mon, 25 Nov 2013 04:33:27 GMT

Hi jaswant,

sorry for the delay..

Above fields are common in my tables.

Re: Link Table?

tresB — Mon, 25 Nov 2013 04:40:58 GMT

Please refer this nice attached doc

Re: Link Table?

agni_gold — Mon, 25 Nov 2013 05:23:00 GMT

If you want to create a main fact table then you have to concatinate in this way :

LinkTableTemp:

LOAD C_SERVICE_CENTRE,

C_SAMPLE_MONTH,

C_BRANCH,

FILE_NO,

C_LOAN_PRODUCT

Resident Compliance_Report;

Concatenate LinkTableTemp

Load

FILE_NO,

BRANCH As LinkBranch,

SERVICE_CENTRE As Link_ServiceCentre,

LOAN_PRODUCT As Link_Product,

SAMPLE_MONTH As Linkdate

Resident DatabaseData;

Concatenate LinkTableTemp

Load

FILE_NO,

F_BRANCH As LinkBranch,

F_SERVICE_CENTRE As Link_ServiceCentre,

F_SAMPLE_MONTH As Linkdate

Resident File_Details;

Concatenate LinkTableTemp

Load

FILE_NO,

QR_BRANCH As LinkBranch,

QR_SERVICE_CENTRE As Link_ServiceCentre,

QR_SAMPLE_MONTH As Linkdate

Resident Question_Review_Status;

Concatenate LinkTableTemp

Load

FILE_NO,

QS_BRANCH As LinkBranch,

QS_SERVICE_CENTRE As Link_ServiceCentre,

QS_SAMPLE_MONTH As Linkdate

Resident Question_Status_Report;

Concatenate LinkTableTemp

Load

FILE_NO,

CO_BRANCH As LinkBranch,

CO_SERVICE_CENTRE As Link_ServiceCentre,

CO_SAMPLE_MONTH As Linkdate

Resident Compliance_Overdue_Report;

/* Now You can make final fact table */

LinkTable:

Load Distinct

FILE_NO,

Linkdate,

LinkBranch,

Link_Product,

Link_Product As LOAN_PROD_CODE,

Link_ServiceCentre,

Link_ServiceCentre As SERVICE_CENTRE_CODE,

year(Linkdate) As Year,

Month(Linkdate) As Month,

AutoNumberHash128(FILE_NO,Linkdate) As Link_Field

Resident LinkTableTemp;

DROP Table LinkTableTemp;

Or if your question is to link all the tables with each other then you can use lookup funtion and make some composite keys to link each other .

Hope this may help you.