removing synthetic keys

[srujanaponnuru]

hi,

i ahve sources coming from 5 excel files and i have extracted all of them but i am getting lot of synthetic keys.

attached is the notepad that has the code. please help on removing keys and creating a data model.

[tresesco]

Instead of quickly jumping into the specific solution, I would suggest that you learn about it and the resolve yourself. Please check the below links:

synthetic-keys

https://community.qlik.com/blogs/qlikviewdesignblog/2013/04/16/synthetic-keys

Re: Syntetic key

[ramasaisaksoft]

Hi Srujan,

As per my analysis i got your data model like below

Here Active flags are different for each and every table(i mean Flag creation is depending on condition applied in that table)  so we can't  use that field as a Key for 3 tables(Organisation,Report Detail,Report Master).

-->in general Load & Modified time stamps also different for each table so we can't create Key on these  fields.

As per my knowledge Report detail and Report Master having the same number of records for Report Identifier field(If it is a Primary key field).so

create a Association between Report detail and Report Master tables on Report Identifier field and  do alias name for Report Name field.

depends on situation some times you can remove the field Report Name in anyone table.

after that alias names need to give for both time stamp fields  Load & Modified time stamps in both tables

Now you got association between Report detail and Report Master tables with  Report Identifier field.so no synthetic key will created between these 2 tables.

Depends on the data you can check the Key field between 3 tables(Organisation,Report Detail,Report Master).

but i am sure we can't create any key on these 3 fields (Active Flag,Load & Modified time stamps) so think other field depend on data.

still you have any doubt feel free to reach me on ramasaiqvw@gmail.com / 8376964364

[Colin-Albert]

Hi Srujan,

Rather than adding all 5 tables into your model at once, it is easier to add the tables one-at-a time, and resolve any data issues due to Syn-keys or loops as you go.

The easiest way to change your script to add the tables one at a time is to insert an "exit script"  line before you load the Reportmaster table. The "exit script" will stop the script cleanly at that point.

Then you can review the data loaded - you only need to use debug and a limited load of 10 rows is sufficient for this,

fix any errors, then move the exit script line down the script after the next table.

Just remember to remove the "exit script" line when your load is fixed.

ReportDetail:

LOAD

Report Identifier],

[Report Name],

[Report Complexity],

[Report Source],

[Processing Type],

[Processing Stability],

[Validation method],

[Change requests],

      [Automation Complexity],

     [Opportunities for automation],

     [Limitations for automation],

     [Effort required for report],

     [Active Flag], [Load Timestamp],

      [Modified Timestamp]

FROM XXXX

(ooxml, embedded labels, table is Sheet1);

exit script ;  //  #### Script execution will stop here ###########################################

Reportmaster:

LOAD

[Report Identifier],

[Report Name],