Syntetic keys created problem

[a2ztests]

Hi,

I want to add 3 facts to my model.

Some of the fields in all 3 tables are the same name - for example a, b,c

In 2 of the tables I have extra fields with the same name - for example d, e.

I have tried to use the linked table approach like mentioned in the script below.

The problem: I still get 3 synth keys - see the output: 

$Syn 1 = %LinkKey_2+fld_a + fld_b + fld_c

$Syn 2 = %LinkKey+%LinkKey_2

$Syn 3 = $Syn 1+$Syn 2

//-----------------------------------------------------------------------------------------

Here is my load script:

LIB CONNECT TO 'my conn';

//-----------------------------------------------------------------------------------------

tbl_1:

LOAD 

  fld_d & fld_e as %LinkKey,

  fld_a & fld_b & fld_c as %LinkKey_2,

  fld_a,

  fld_b,

  fld_c,

  fld_d,

  fld_e,

  fld_f,

  fld_g,

  fld_h

FROM [lib://my_file_1.qvd]

(qvd);

//-----------------------------------------------------------------------------------------

tbl_2:

LOAD 

  fld_d & fld_e as %LinkKey,

  fld_a & fld_b & fld_c as %LinkKey_2,

  fld_a,

  fld_b,

  fld_c,

  fld_d,

  fld_i,

  fld_j,

  fld_k,

  fld_l

FROM [lib://my_file_2.qvd]

(qvd);

//-----------------------------------------------------------------------------------------

tbl_3:

LOAD

  fld_a & fld_b & fld_c as %LinkKey_2,

  fld_a,

  fld_b,

  fld_m,

  fld_n,

  fld_o

FROM [lib://my_file_3.qvd]

(qvd);

//-----------------------------------------------------------------------------------------

LinkKey:

    LOAD

distinct (%LinkKey), fld_d, fld_e

resident tbl_1;

    LOAD

    distinct (%LinkKey), fld_d & fld_e

    residen tbl_1;

Concatenate

// LinkKey_2:

    LOAD

distinct (%LinkKey_2), fld_d & fld_e

resident tbl_2;

    LOAD

    distinct (%LinkKey_2), fld_a , fld_b , fld_c 

    resident tbl_2;

    LOAD

    distinct (%LinkKey_2), fld_a , fld_b , fld_c  

    resident tbl_3;

drop fields  fld_d, fld_e  from tbl_1, tbl_2;

drop fields  fld_a , fld_b , fld_c from tbl_3;

Exit Script;

[henrikalmen]

When using the approach of creating composite keys (eg. %LinkKey in your example) you need to remove the original fields from your data model.

For example:

tbl_1:

LOAD 

  fld_d & fld_e as %LinkKey,

  fld_a & fld_b & fld_c as %LinkKey_2,

  //fld_a,

  fld_b as b,

 ...

[a2ztests]

Thanks for your answer,

I think I have a mistake in the last part  of the script i have provided.

I actually already have in my script:

drop fields fld_a , fld_b , fld_c, fld_d, fld_e  from tbl_1, tbl_2;

drop fields  fld_a , fld_b , fld_c from tbl_3;

What else am I missing?

[henrikalmen]

Ah, sorry - I missed that.

Then I'd say your problem is this: drop fields  fld_a , fld_b , fld_c from tbl_3;

You still have those fields in tbl_1 AND tbl_2.

Start with fixing that.

[a2ztests]

Thanka again for your reply.

I think i took care of that as well.

Please see in the script i last provided:

drop fields fld_a , fld_b , fld_c, fld_d, fld_e  from tbl_1, tbl_2;

[henrikalmen]

What probably remains now is that you have %LinkKey and %LinkKey_2 in both tbl_1 AND in tbl_2. That gives you a synthetic key.

(Another thing: perhaps you already know but you don't need to specify "from" in the drop fields statement. If you don't specify tables, the fields will be dropped from all tables.)

[a2ztests]

Right. How can i solve this?

[henrikalmen]

It's difficult to give a general answer to that. Just like you eliminated synthetic keys between fld_a, fld_b etc, you need to make sure that your new compound keys don't result in synthetic keys. Two tables may only be linked by one field - if two or more fields with the same name exists in multiple tables, a synthetic key is the result.

You need to alter your data model so that it is avoided, and how you should do that very much depends on the data you have.