3 Replies Latest reply: May 23, 2012 10:10 AM by Miguel Angel Baeyens de Arce RSS

    First QV - $Syn?

      I've got 4 stored procedures giving me tables I want to link up in my QV document.


      With three tables eveyything is working fine, but when I include the 4th I get $Syn messages and the QV stops, saying 2MB exceeded. I've attached a snip of the table view, could someone tell me what the problem is?  The tables are basically Company -> which contains many despatch groups -> which contain many campaigns -> which contain many stockcodes.   Thanks

        • Re: First QV - $Syn?
          Miguel Angel Baeyens de Arce



          Welcome to the QlikCommunity and the QlikView world. A synthetic table means that two or more tables have two or more fields named alike. Just rename the fields and keep the names only in those fields that are key between tables.


          Hope that helps.



            • First QV - $Syn?

              The fields with the same names are key fields.  For example, company code appears in all 4 tables.Is the synthetic table a normal QV behaviour or should I be aiming to change things so the synthetic table is not created?

                • Re: First QV - $Syn?
                  Miguel Angel Baeyens de Arce



                  There are some that have tested synthetic keys and say they perform even better than a composite field concatenating all relevant fields. But the thing is that I've never seen them in a Production environment and that in my experience, what usually happens is what is happening to you: the computer runs out of memory.


                  I would suggest that you should avoid synthetic keys not because they are an error, in your case, actually, is the expected behavior and it's not a data modelling problem, rather than a performance / product issue when dealing with synthetic keys. As opposed to SQL RDBM, QlikView joins tables automatically when two tables have one field that has the same name.


                  Leaving fields as they are pulled from the data source is not usually -by default- a good idea, let's say you have a Customer table with a field named ID and a different table Invoices with a field named ID, QlikView then will join both tables by ID, but in this case, the ID of a customer has nothing to do with an invoice ID.


                  Back going back to your question: you should control what you load, even when the composite keys are created on purpose, do that in the script using Autonumber() functions (there are some more) based on the concatenation of the fields that create the unique key.


                  Hope all the above makes sense.



                  EDIT: I forgot to add the link to the most interesting thread on this matter started and curated by John Witherspoon (and most of the replies): "Should We Stop Worrying and Love the Synthetic Key?".