How do I get rid of $Syn table

[#1]

Hello,

I am a newbie to Qlikview and am trying to create my own report for the first time. Unfortunately I am running into some issues with a $Syn table.

My scenario: I have a table with "processed hour" records. Such a record contains an hourcode and a ProdHeaderDossierCode. I also have a HourBudgetTranslate table which translates the hourcode to an hourbudgetcode based on the HourCode, unless a ProdHeaderDossierCode or DossierCode exists in the HourBudgetTranslate table, then it should translate to the related hourbudgetcode. Since the DossierCode is not part of the ProcessedHour table, I also have to link to another table to retrieve the dossiercode based on the ProdHeaderDossierCode. So now I have 3 fields that end up in the $Syn table (DossierCode, HourCode and ProdHeaderDossierCode).

I don't think renaming works since the HourBudgetTranslate table translates based on the actual codes.

Can anyone help me out and point me in the right direction?

Kind regards,

Diego

[IAMDV]

Synthetic keys are generally resource heavy and may slow down calculations and, in extreme cases, overload the application. They also make a document harder to understand and maintain.

In your case, can you create a composite key on the columns?

Concatenate both the colums and this removes synthetic key.

Hope this helps!

Cheers - DV

[#3]

Hi DV,

Not quite sure which columns I should concatenate. I have added a picture below for clarification. If I concatenate the columns I'm creating a unique column right? But I need the original columns to check against the HourBudgetTranslate table.

In the HourBudgetTranslate table all HourCodes translate to an HourBudgetCode but when either the DossierCode or ProdHeaderDossierCode is filled in the HourBudgetTranslate table it should translate against that specific HourBudgetCode which will be different from the HourCode one.

In my report I would like to be able to select the HourBudgetCode and the report should show me all ProcessedHour records with that specific HourBudgetCode, based on either the HourCode, DossierCode or ProdHeaderDossierCode. By the way, the DossierMain table is only there because I need the OrdNr as well.

Do you still think concatenate will work? If so, which columns should I use?

Kind regards,

Diego

[#4]

Hi Diego,

I think this is LinkTable territory, but am not 100% sure as don't know what are the underlying values in each table.

It looks like you need a massive link table which will contain all the individual values from the 4 source tables in your image for the fields DossierCode, HourCode, ProductHeaderDossierCode. This link table will then need a concated field with HourCode & ProdHeaderDossierCode to link to the ProcessedHour table, a concat of DossierCode & ProdHeaderDossierCode to link to the ProductionHeader and a concat of the DossierCode&ProdHeaderDossierCode&HourCode to link to the HourBudgetTranslate.

None of the source tables should be loading DossierCode, HourCode or ProductHeaderDossierCode as a singular field only the concatenated fields needed to join to the link table.

Hope this makes sense. It's not complicated, but not easy to explain in a few phrases. What you are doing with the above technique, is taking posession of the ownership of created the synthetic keys, rather than relying on QlikView to figure out the structure of your resulting data model.

[#5]

Btw, depending on the size of your datamodel and tables, an optimal way of creating link fields is to use the autonumber function. Apparently, QlikView resolves the fastest, queries over tables which use numbers as link field, as opposed to varchars.

Hence, for e.g. you should have:

Load

autonumber(HourCode & ProdHeaderDossierCode, 1) as Link_ProcHr,

autonumber(DossierCode & ProdHeaderDossierCode, 2) as Link_ProdHead,

autonumber(DossierCode&ProdHeaderDossierCode&HourCode, 3) as Link_HrBudTran

etc...

These linking fields should be copied and pasted to the associated source tables their are linked to.

Hope it helps.

[#6]

Hi, It's not clear to me how I can create the Link Table. I added the autonumber to the source tables so now I have 4 tables that are not linked. But how do I create the Link Table?

Kind regards,
Diego

[IAMDV]

Please can you share the QV file with sample data? Please share it with synthetic keys... I'll try this one 🙂

[#8]

Hi Diego,

Reload your script using the debug and option with the tick box in Limited Load which you set to 1. This will load the script with 1 row of data, then share the script and I'll give you a quick sample of what is meant by the link table.

[#9]

Hi guys,

Attached is the file with a dataset limited to 10 rows.

Kind regards, Diego

[IAMDV]

I thought you would provide QV document with synthetic tables. Anyway I will give you concatenation explanation...

You can use concatenation (As a new column) in your dimension table and also add the same concatenation in the fact tables. You don't have to use the concatenated column to filter the data. You can still use the orginal columns to filter the data. While you filter this data on non-concatenated columns you still have the relationship to the linked tables through your concatenated columns.

I am sorry if I am not clear in my explanation as english is not my first language!

Good luck!

Cheers - DV