Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
stig1984
Creator II
Creator II

Removing one field from a synthetic table

I have the following data model which has a synthetic key.

Data Structure Diver.png

The synthetic key is working well for me and actually is providing the links that I need with the exception of the JOB_DATE field.

Due to the fact that I am using each of these fields as filters to provide comparison across goods in and sales I don't want to change names and the synth table has linked the rest of the data correctly - except that each filed is linked to job_date (date fields linked to the master calendar).

I tried to create a link table for JOB_DATE but this just gave me another table linked to the synth table.

Can I force JOB_DATE to form a separate synth table or is there a better solution.  I have read most of the post and questions on synth tables and linked tables but they do no seem to meet my requirements.

Script is attached.

Many thanks in advance for your help.

1 Solution

Accepted Solutions
agomes1971
Specialist II
Specialist II

Ok so you need to create one or several linked tables if you need.

Please see this

how to create link table in Qlikview?

HTH

André Gomes

View solution in original post

17 Replies
agomes1971
Specialist II
Specialist II

Hi,

please read this:

Synthetic keys  . How to remove them

HTH

André Gomes

vishsaggi
Champion III
Champion III

Try to rename all the fields (Brand, Consignline, ProductCategory, ProductDescription, size, suppliername, variety, producttype) in GoodsIn table (as seen in the above image) leaving Job_Date field as is and try again? Let me know if you still have synthetic keys!!!

Anil_Babu_Samineni

Try to avoid circum ref using qualify or un qualify

Or

Please use for noconcatenate for fact tables

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
stig1984
Creator II
Creator II
Author

Thanks for your help - however I need all those fields to be linked (for example to get data for a particular variety sold from the goods in table or view results by Supplier in Goods in and Sales tables).

Renaming these will get rid of the synthetic key but I lose the ability to link the data.

I have no issue with the synthetic key as such (although there might be more efficient ways to do it) - but I do need the JOB_DATE to either be in a separate synthetic or linked table.  As it stands the links are all correct but only if the SALES.JOB_DATE and GOODS_IN.JOB_DATE happen to be the same date.  If the JOB_DATE is renamed to remove it from the synthetic table the rest of it works perfectly, but I have then lost the link to the master calendar.

In my head what I would like to do is have two link or synthetic tables - one for JOB_DATE and one for the rest of the required links.

stig1984
Creator II
Creator II
Author

Thanks for your help - I've gone through a lot of these but have spotted some different ones.

I'll have a look at those as well.

agomes1971
Specialist II
Specialist II

Ok so you need to create one or several linked tables if you need.

Please see this

how to create link table in Qlikview?

HTH

André Gomes

stig1984
Creator II
Creator II
Author

Thanks.

If I use the qualify / unqualify won't I lose the required links which exist in that synthetic table.

agomes1971
Specialist II
Specialist II

No if you do it in the proper manner.

André Gomes

effinty2112
Master
Master

Hi Stephen,

To my eyes your data model is crying out for the Sales and the GoodsIn tables to be concatenated into a single table. If I was doing it I would call the new table Transactions and maybe add a new field, say Transaction Type and have this set to 'Sold' or a 'Received' for records originating in the Sales and GoodsIn table respectively.

Do it how ever you want but I really think that table concatenation is the way to go. Don't worry about this giving you a lot of nulls in your fact table, that won't matter.

Good luck

Andrew