7 Replies Latest reply: Jul 1, 2011 1:14 PM by John Witherspoon RSS

    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!

        • Synthetic table creation - how to avoid?
          Erich Shiino

          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

          • Re: Synthetic table creation - how to avoid?
            John Witherspoon

            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.

              • 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?

                  • Re: Synthetic table creation - how to avoid?
                    John Witherspoon

                    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.

                      • 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?

                          • Re: Synthetic table creation - how to avoid?
                            John Witherspoon

                            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.

                      • 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!