Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
temiekaclay
Contributor III
Contributor III

Getting rid of synthetic keys

CASES:

LOAD
CASEID,
    CATEGORYID;
SQL SELECT
CASEID,
    CATEGORYID
FROM ICMSCITRIX.dbo.TBLCASE;



CHARGEOFFS:

LOAD
CASEID,
// AutoNumber (CASEID & '-' & CATEGORYID) As CaseCateKey,
   
CHARGEOFFAMOUNT,
    CHARGEOFFDATE,
    CHARGEOFFGL,
    CHARGEOFFSETRU,
    INCIDENTCOUNTERID,
    CATEGORYID,
    Date (monthstart (CHARGEOFFDATE) , 'MMM_YYYY') as ReportMonthYear,
    'ICMS' AS SYSTEM;
SQL SELECT
CASEID,

CHARGEOFFAMOUNT,
    CHARGEOFFDATE,
    CHARGEOFFGL,
    CHARGEOFFSETRU,
    INCIDENTCOUNTERID,
    CATEGORYID
FROM ICMSCITRIX.dbo.TBLINCIDENT
WHERE CHARGEOFFDATE >= '1/1/2012'
AND CHARGEOFFDATE <= '12/31/2012'
AND CHARGEOFFGL IN (860360, 980360, 990360, 970360, 860500);

RECOVERIES:

LOAD
CASEID,
DATEOFRECOVERY,
    ITEMIZEDRECOVERYGL,
    ITEMIZEDRECOVERYLOSSLOCATION AS LOCATIONID,
    TOTALRECOVERY,
    Date (monthstart (DATEOFRECOVERY) , 'MMM_YYYY') as ReportMonthYear;
SQL SELECT CASEID,
DATEOFRECOVERY,
    ITEMIZEDRECOVERYGL,
    ITEMIZEDRECOVERYLOSSLOCATION,
    TOTALRECOVERY
FROM ICMSCITRIX.dbo.TBLITEMIZEDRECOVERY
WHERE DATEOFRECOVERY >= '1/1/2012'
AND  DATEOFRECOVERY <= '12/31/2012'
AND  ITEMIZEDRECOVERYGL IN (860360, 980360, 990360, 970360, 860500);


Directory;

LOAD REPORTGROUPING,

     CATEGORYID,

     Type

FROM

[..\ICMS_Categories.xlsx]

(ooxml, embedded labels, table is ReportGroupings);

Directory;

LOAD L6,

     L5,

     LOCATIONID

FROM

[..\ICMS_Location.xlsx]

(ooxml, embedded labels, table is LOCATION);

PAYMENTS:

LOAD

CASEID,

PAYMENTAMOUNT,

    PAYMENTDATE,

    Date (monthstart (PAYMENTDATE) , 'MMM_YYYY') as ReportMonthYear;

SQL SELECT

CASEID,

PAYMENTAMOUNT,

    PAYMENTDATE

FROM ICMSCITRIX.dbo.TBLRECOVERYPAYMENT

WHERE PAYMENTDATE >= '1/1/2012'

AND PAYMENTDATE <= '12/31/2012';

RECOVERYCASE:

LOAD

CASEID,

COSTCENTER,

LOSSLOCATIONID AS LOCATIONID;

SQL SELECT

CASEID,

COSTCENTER,

    LOSSLOCATIONID

FROM ICMSCITRIX.dbo.TBLRECOVERYCASE

WHERE COSTCENTER IN ('860360', '980360', '990360', '970360', '860500');

My script is shown above.  I need to get rid of the synthetic keys shown in the image below.  Any suggestion will be appeciated.  Thanks!SyntheticKeys.png

1 Solution

Accepted Solutions
swuehl
MVP
MVP

I would first try to check which fields are real key fields and which not. Try to remove data redundancy.

For example, you have several tables with CaseID and CategoryID (same for CaseID and LocationID).

If the CategoryID is the same per CaseID, it would be sufficient to store the information once, not in several tables (I guess I would keep the CategoryID in Cases table and remove the field from ChargeOffs table. In general, store the CategoryID in the table that keeps all distinct values for CasesID)

Similar for LocationID.

If you need a combined key, create a combined one (using e.g. autonumber() ).

Think about joining / concatenating tables where appropriate.

Hope this helps,

Stefan

View solution in original post

3 Replies
edg_baltazar
Creator
Creator

You have to create a Link_Table, regards!

Not applicable

This is Link_table create from qlik. In a project qlik you don't have a synthetic keys, beacuse the synthetic keys consume a lot of memory and the performance of file  is very very worse when you have a big data.

swuehl
MVP
MVP

I would first try to check which fields are real key fields and which not. Try to remove data redundancy.

For example, you have several tables with CaseID and CategoryID (same for CaseID and LocationID).

If the CategoryID is the same per CaseID, it would be sufficient to store the information once, not in several tables (I guess I would keep the CategoryID in Cases table and remove the field from ChargeOffs table. In general, store the CategoryID in the table that keeps all distinct values for CasesID)

Similar for LocationID.

If you need a combined key, create a combined one (using e.g. autonumber() ).

Think about joining / concatenating tables where appropriate.

Hope this helps,

Stefan

View solution in original post