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

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

absekrafi — Wed, 25 Nov 2020 16:16:04 GMT

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

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

jerifortune — Mon, 30 Apr 2018 14:11:45 GMT

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.

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

absekrafi — Mon, 30 Apr 2018 15:18:06 GMT

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?

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

jerifortune — Mon, 30 Apr 2018 15:55:56 GMT

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);

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

absekrafi — Wed, 02 May 2018 08:07:23 GMT

Hi Jerry,

Do I use PROD_KEY on both loads?

Thank you,

Kind regards,

Abdallah

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

jerifortune — Wed, 02 May 2018 12:41:51 GMT

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

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

absekrafi — Wed, 02 May 2018 16:18:39 GMT

I meant PRODUCT and SUB_PRODUCT Tables

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

jerifortune — Wed, 02 May 2018 16:29:32 GMT

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

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

jerifortune — Wed, 09 May 2018 18:24:45 GMT

Hi Abdallah,

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

Thanks.

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

absekrafi — Thu, 10 May 2018 07:30:01 GMT

Hi Jerry,

How to close the thread?

Thanks