
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Composite key
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.
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Happy we are heading to the solution.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you Jerry.
The issue is fixed. I got correct values!
Many thanks again
Abdallah


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Good. Thanks for letting me know. Please, could you close the thread by marking the correct answer.
