Sorry but I will ask the same question again: Circular loops, synthetic keys

[absekrafi]

Dear Qlikview community,

I am new to Qlikview and I need your help to explain to me what I should change in the figure enclosed to this question.

My dimentions are CODUSN, TRANSACTION_DATE, (CODPROD, CODSPROD or CODEMP AND CODESMP) AS axis and Quantity produced or transferred as second dimentions.

1. The issue I am facing is that there are 2 circular references.

2. When I added an other table using CODEMP AND CODESMP Qlikview crashed.

3. I want a star fact table (the figure I attached looks ok except there too many synthetic keys).

4. How to build a fact table.

I you want te code please send me your email address and I will send the code to you.

Thank you very much in advance

[jerifortune · Accepted solution]

You have many Synthetic keys. You would start by removing the synthetic keys. The best approach is to rename fields that are not your joining keys. You can us qualify to rename the entire table and Unqualify to exempt the associative keys.

[absekrafi]

Hi Jerry,

In my thinking and in the way the PL SQL Tables are defined all these Sync fields are joining keys. For example:

The Fields I can not remove are as follow because the PL SQL Table PRODUCT is defined as bellow:

PRODUCT:

LOAD CODE_PROD, // code in the matching table

     PRODUCT_NAME,

     CP_ACHMP_GCT, // Code of the product from Raw material  Table

     CP_PROD_GCT, // Code of the product from the production  Table

FROM

[..\QVD\PRODUCT.qvd]

(qvd);

The Table SUB_PRODUCT is defined (In PL SQL) as:

SUB_PRODUCT:

LOAD CODE_PROD,

     CODE_SPROD, // sub-code in the matching table

     SUB_PRODUCT_NAME,

     CP_ACHMP_GCT,

     CSP_ACHMP_GCT, // sub_code of the sub-raw material

     CP_PROD_GCT,

     CSP_PROD_GCT //sub_code of the sub_product

FROM

[..\QVD\SUB_PRODUCT.qvd]

(qvd);

So the products are described by 3 fields (CODE_PROD, CP_ACHMP_GCT, CP_PROD_GCT) and their sub-products (3 other fields CODE_SPROD,   CSP_ACHMP_GCT  and CSP_PROD_GCT).

Later on these codes:

     CP_ACHMP_GCT, // code of the raw material

     CSP_ACHMP_GCT, // sub_code of the sub-raw material

     CP_PROD_GCT, // code of the product

     CSP_PROD_GCT //sub_code of the sub_product

are used in other tables to determine the transferred and the produced quantities of each product and sub product depending of it's nature: a raw material or a product as defined in the maching table.

I don't understand why Qlikview didn't add CODESMP to the ($Sync  15 ) in the table TRASNFERT_AVEC_REDRESS_M and to ($Sync 13) in the table SOUS_PRODUIT?

[jerifortune]

You can still remove the Sync Key by forming a composite key,

For example If I need a Sync Key that is made up of CODE_PROD, CP_ACHMP_GCT and CSP_PROD_GCT, my load script should look like this:

PRODUCT:

LOAD *, Autonumber(PROD_CODE_PROD&'|'&PROD_CP_ACHMP_GCT&'|'&PROD_CSP_PROD_GCT) AS PROD_KEY;

LOAD CODE_PROD  AS PROD_CODE_PROD,

     PRODUCT_NAME,

     CP_ACHMP_GCT  AS PROD_CP_ACHMP_GCT,

     CP_PROD_GCT   AS PROD_CP_PROD_GCT,

FROM

[..\QVD\PRODUCT.qvd]

(qvd);

SUB_PRODUCT:

LOAD *, Autonumber(SUBP_CODE_PROD&'|'&SUBP_CP_ACHMP_GCT&'|'&SUBP_CSP_PROD_GCT) AS PROD_KEY;

LOAD CODE_PROD  AS SUBP_CODE_PROD ,

     CODE_SPROD AS SUBP_CODE_SPROD,

     SUB_PRODUCT_NAME,

     CP_ACHMP_GCT    AS SUBP_CP_ACHMP_GCT,

     CSP_ACHMP_GCT   AS SUBP_CSP_ACHMP_GCT,

     CP_PROD_GCT     AS SUBP_CP_PROD_GCT,

     CSP_PROD_GCT    AS SUBP_CSP_PROD_GCT 

FROM

[..\QVD\SUB_PRODUCT.qvd]

(qvd);

[absekrafi]

Hi Jerry,

Do I use PROD_KEY on both loads?

Thank you,

Kind regards,

Abdallah

[jerifortune]

Hi Abdallah,

You can use PRO_KEY in any table that you want to join based on same composite keys. In the example above, I used PROD_KEY on both tables - PRODUCT and SUB_PRODUCT to form an association.

I assume you mean both tables, otherwise, be specific with what you mean by "both loads".

Thanks

[absekrafi]

I meant PRODUCT and SUB_PRODUCT Tables

[jerifortune]

Okay... You will need to use PROD_KEY in both tables for them to associate.

[jerifortune]

Hi Abdallah,

Please, close this thread by marking the appropriate answer  to assist the community,

Thanks.

[absekrafi]

Hi Jerry,

How to close the thread?

Thanks