topic Re: removing synthetic keys in QlikView

removing synthetic keys

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

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.

Re: removing synthetic keys

tresB — Wed, 13 Sep 2017 06:56:22 GMT

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

Re: removing synthetic keys

ramasaisaksoft — Wed, 13 Sep 2017 08:01:57 GMT

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

Re: removing synthetic keys

Colin-Albert — Wed, 13 Sep 2017 09:55:11 GMT

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],