Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Synthetic table creation - how to avoid?

I am generating a calendar and then joining all my other tables to this calendar on the basis of date and time from this calendar.

How can i avoid synthetic tables?

Thanks!

1 Solution

Accepted Solutions
MVP
MVP

Re: Synthetic table creation - how to avoid?

So your calendar has both a date AND a time?  In your case, you could add a timestamp to the calendar and replace the date and time in all the other tables with a timestamp.  Then your link would only be on a single field, so there would be no synthetic key created.

The synthetic key, though, essentially models the same thing.  The synthetic key here functionally IS a timestamp - QlikView just hasn't been told that's what it is, so it's probably doing an autonumber or hash of some sort across the date and time.  I'd expect similar performance in charts either way, though actual testing could prove me wrong of course.  If you're more comfortable without the synthetic key (most people are), then the timestamp approach shouldn't cause you any problems.  It'll also just look cleaner when you look at your data model.

7 Replies
erich_shiino
Honored Contributor

Synthetic table creation - how to avoid?

You get a syn key when there is more than one field in common between two tables.

There are some posts that can guide you with similar problems and the concept:

http://community.qlik.com/message/58479#58479

http://community.qlik.com/message/72145#72145

http://community.qlik.com/message/72145#72145

http://community.qlik.com/message/80125#80125

Hope this helps,

Erich

MVP
MVP

Re: Synthetic table creation - how to avoid?

So your calendar has both a date AND a time?  In your case, you could add a timestamp to the calendar and replace the date and time in all the other tables with a timestamp.  Then your link would only be on a single field, so there would be no synthetic key created.

The synthetic key, though, essentially models the same thing.  The synthetic key here functionally IS a timestamp - QlikView just hasn't been told that's what it is, so it's probably doing an autonumber or hash of some sort across the date and time.  I'd expect similar performance in charts either way, though actual testing could prove me wrong of course.  If you're more comfortable without the synthetic key (most people are), then the timestamp approach shouldn't cause you any problems.  It'll also just look cleaner when you look at your data model.

Not applicable

Re: Synthetic table creation - how to avoid?

hi i currently have 3 tables which are joined through a syn key...

assuming the tables are table1, table2 and table3 and the syn key is fieldA and fieldB....

if i remove fieldA from the key, does it mean that if i select the fieldA from table1.fieldA using a list box, i will not be able to get data from table2? will there be any difference if i select use table2.fieldA or table3.fieldA?

Not applicable

Synthetic table creation - how to avoid?

Hey,

its alright, i managed to clean up the synthetic tables by joining on only 1 field! thanks so much!

MVP
MVP

Re: Synthetic table creation - how to avoid?

AXON SG wrote:

hi i currently have 3 tables which are joined through a syn key...

assuming the tables are table1, table2 and table3 and the syn key is fieldA and fieldB....

if i remove fieldA from the key, does it mean that if i select the fieldA from table1.fieldA using a list box, i will not be able to get data from table2? will there be any difference if i select use table2.fieldA or table3.fieldA?

So you have this?

table1: fieldA, fieldB, other fields
table2: fieldA, fieldB, other fields
table3: fieldA, fieldB, other fields

Now, when you say "if I remove fieldA from the key", what do you mean?  Removing it from one or more of the tables?  Assuming the tables should be keyed by both fieldA and fieldB as shown, then removing fieldA from one or more of the tables will break the data model.

For the above, the right way to "fix" it (if you consider it broken) is to combine all three tables into one.  They all have the same key, so in a sense, they're all the same table.

Perhaps all this will make more sense if we replace these abstract table and field names with actual table and field names and data.

Units Sold:
Customer, Product, Units Sold
Andy, Pencils, 400
Bob, Pencils, 250
Carla, Pens, 200

-----------------------------

Revenue:
Customer, Product, Revenue
Andy, Pencils, 600
Bob, Pencils, 500
Carla, Pens, 200

-----------------------------

Cost:
Customer, Product, Cost
Andy, Pencils, 500
Bob, Pencils, 300
Carla, Pens, 100

These tables are easily joined, and almost certainly should be:

Sales:

Customer, Product, Units Sold, Revenue, Cost
Andy, Pencils, 400, 600, 500
Bob, Pencils, 250, 500, 300
Carla, Pens, 200, 200, 100

However, even if they aren't joined, and you allow the synthetic key to form, if you build a chart with customer and product as dimensions, and have expressions summing the other fields, you'll see exactly what is shown in the Sales table, even though the sales table doesn't exist.  The data model stores equivalent information.

Now, you suggested removing fieldA from the key, but you still mention referring to table1.fieldA.  So let's assume you mean removing it from tables 2 and 3.  That's equivalent to removing Customer from the Revenue and Cost tables.  Now what do we have?

Units Sold:
Customer, Product, Units Sold
Andy, Pencils, 400
Bob, Pencils, 250
Carla, Pens, 200

-----------------------------

Revenue:
Product, Revenue
Pencils, 600
Pencils, 500
Pens, 200

-----------------------------

Cost:
Product, Cost
Pencils, 500
Pencils, 300
Pens, 100

And what happens if we build a chart?  Well, we no longer associate customer with revenue or cost, so the total revenue and total cost will be associated with only the product.  Anyone buying that product will get the full revenue and cost from everyone buying the product.  In other words:

Customer, Product, sum("Units Sold"), sum(Revenue), sum(Cost)
Andy, Pencils, 400, 600+500=1100, 500+300=800
Bob, Pencils, 250, 600+500=1100, 500+300=800
Carla, Pens, 200, 200, 100

And that is, quite simply, wrong.  Removing Customer from any of the tables breaks the data model.

Answering one of your questions directly, "does it mean that if i select the fieldA from table1.fieldA using a list box, i will not be able to get data from table2?"  No, it means that you'll get TOO MUCH data from table2.  But I'm still not sure I've understood what you mean by removing fieldA from the key, so perhaps I've gone off on a lecture on the wrong subject.

Not applicable

Re: Synthetic table creation - how to avoid?

what i meant about removing fieldA from the syn key is that i exclude fieldA from the unqualify statement..

lets assume fieldA is model and fieldB is location

locations can have common models or they can have models unique to a few locations or a single location

lets say i remove location from the syn key, meaning excluding location from the unqualify statement

so location would now be table1.location, table2.location and table3.location instead of just location

if i use table1.location as a list box selection, will i still be getting the correct data/relationships from table2 and table3?

MVP
MVP

Re: Synthetic table creation - how to avoid?

Ah!  Sorry for my confusion.  I really should have been able to figure that out from your use of table1.fieldA, which indicates that fieldA is qualified, and therefore wouldn't be in your synthetic key.  It makes perfect sense in hindsight.

If fieldA is qualified, your fieldAs will not be linked, and therefore selecting one will pull too much data from the other tables.  It's exactly the same problem I explained in the previous post, even if I didn't interpret what you were doing correctly.