Composite key

[absekrafi]

Dear Qlikview community,

I had to add a composite key to remove a synthetic table and to add 2 new fields to an existing table (originally having 2 fields) in order it maches with an other table.

The output is correct except when I have a composite code of 4 fields all fields different from null.

The CodesKey  when the result is wrong (not existing) are : (3-1-9-1) and (4-1-6-1) and (7-1-13-1) and (8-1-14-1).

The results are correct when CodesKey is equal to :  (1-1--) or (1-2--) or (7-2--) or (--6-2)

Could you please point me to what I did wrong in my code?

Please find enclosed my code.

Thank you.

[jerifortune]

It is not compulsory to use composite key to remove synthetic Keys. You can rename the fields when you need not to use those fields to form an association. Using Null values will not yield to uniqueness that would produce a solid and correct join.

One need to  understand the reason while you choose to use null as the best approach.

[absekrafi]

I used null() in INOUT table to create 2 new fields. This table contain originally only 2 fields codeart and codesart, but I need to rename (associate) these 2 fields to (codemp, codesmp) or (codprod, codsprod) depending on typeart field.

if typeart = 'M'

- codeart = codemp

- codesart = codesmp

if typeart = 'P'

- codeart = codprod

- codesart = codsprod.

I need also to keep INOUT table as one table and not 2 seperate tables.

Is there an other way to do this?

Thank you

[jerifortune]

You have two main tables SOURS_PRODUIT and INOUT. What is the relationship between the two tables? Please, take away synthetic keys in your mind at this time. We need to properly identify the associative keys before handling the synthetic keys.   Sorry, passing you through this approach.

[absekrafi]

The INOUT Table is the fact Table.

SOUS_PRODUIT is a dimension Table: It contains the codes and the labels of products. each entry of this Table contains: 4 codes of the product and its label.

The INOUT table has only 2 fields codes for each product but there's an other field named TYPEART and depending on the value of this field we associate a couple of product codes in the SOUS_PRODUIT Table.

[jerifortune · Accepted solution]

It is a little bit tricky.  Please, correct me if I am wrong with the following assumptions

1. In your fact table, transaction where TYPEART = 'P'  has a product label WHEN CODEART &'-'&CODESART = CODEMP &'_'&CODESMP

2. In your fact table, transaction where TYPEART = 'M' has a product label corresponding to the association CODEART &'-'&CODESART  = CODPROD &'_'&CODSPROD

If the above assumptions are correct, I will perform the following test.
1. Split the Fact table into two just as you did, but join each table with the product dimension. Return the fields needed eg Product label based on the joining conditions

2. Concatenate the two tables

3. Use Product label as a degenerated dimension instead of product table having its own dimension.

[absekrafi]

the first assumption is correct just reverse TYPEART = 'P' is for the second assumption and TYPEART = 'M' is for the first assumption.

I will check that tomorrow and I will let you know.

Just to let you know I generated a composite keys not containg all 4 fields different from 0 I got some results but I didn't finish to test them (because any way I will use TYPEART value, so when all codes in product table are different than zero, I will use either (CODEART , CODESART) as (CODEMP, CODESMP) or (CODEART , CODESART) as (CODPROD, CODSPROD). When only 2 codes in the composite key are present I got values I am looking for, just I didn't finish my tests.

Many thanks for your time.

Best regards,

Abdallah

[jerifortune]

Happy we are heading to the solution.

[absekrafi]

Thank you Jerry.

The issue is fixed. I got correct values!

Many thanks again

Abdallah

[jerifortune]

Good. Thanks for letting me know. Please, could you close the thread by marking the correct answer.