Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.


Re: Should We Stop Worrying and Love the Synthetic Key?

I think most of us have been doing ETL for years with QlikView, and actually found colleagues using other tools moving some developments to Qlik because of its ETL capabilities.

But as you mention below it's been said already, ETL is not the strength of the QlikView script. The functions are there to be used, and you can preaggregate as much as you want, or, rather, as much as you can. For me, the reason of using a function over another or JOINing in Qlik or somewhere else is basically performance.

While conceptually is perfectly fine, given your environment and requirements might not, and you will need to do those, or some of those ETL steps out of QlikView.

Classic examples to use other tools, or use the data source capabilities could be amount of data to be loaded and transformed, schedule to reload that data, hierarchies that need to be flattened, complexity in the front end send to back end (e.g.: preaggregating), and so on.

As for the "QlikView is easy to use", well, after so many years using it as a partner, demonstrating it as employee and now consuming and developing it as customer, all I have to say is: easy thing will be easy, complex things will be difficult. Loading from a spreadsheet and doing a dozen charts with Count() or Sum() -while not straightforward-, it's definitely easy to do and provides lots of additional options for visualization.

It applies to basically any other "user friendly" technology out there. For example, it's easy to use a smartphone to do easy tasks, either it be Android or iOS. It takes a lot of time to be fully aware of all the settings available and make use of them.

Along those lines, data modelling from disparate data sources and writing complex expressions for the measures of the charts is more difficult, as I would expect it to be. It requires more skills, more thought and more time. And at some point, you will most probably find a limitation you cannot overcome with QlikView alone, which is also OK: QlikView cannot do anything (although I could agree that it has been sometimes positioned as one-size-fits-all, when it's not).

The synthetic key tables, for me, is the perfect example. Do they work? Yes. Do they perform? As far as I can test it, they do. Are they a solution to data modelling issues? Definitely not.

For what it's worth, do I use them? I don't, even if it means spending some more time in the script editor. Not that much because they don't do what I want or because they do not perform, rather because I find clearer to follow a composite key and I expect the next developer to understand what I was trying to do as well. That's me and my experience, others may find more usable the opposite.

As for bugs or unexpected results, there are, there will be and the sooner you report them and the easier you make for Qlik Support to reproduce the issue, the more likely you are to get a workaround, patch and eventually a fix.


Re: Should We Stop Worrying and Love the Synthetic Key?

Everyone may have different ideas. I agree with you most of the views, but I am afraid of any unknown. I will never allow anything that is not clear to appear in my product. You may find this kind of "work style" from all over the world, such as Germany or Japan.

Based on the tests I've done before, if I can't guarantee that my data source (especially the fields that might be identified as connection keys) doesn't have a null value, then I won't allow any nested synthetic keys to be generated -- I'll let my program maximize data compatibility.

It's also because of this conditionality that I personally can't go any further to love the synthetic key - it gives me some concern and makes me feel troubled.



Re: Should We Stop Worrying and Love the Synthetic Key?

I feel you. When we started, probably because our more relational background, synthetic key tables where unexpected and took us some time to get accustomed to association vs. relation (even if theoretically is similar).

That's why most of us decided that, to get full control on our data models, big or small, easy or difficult, best was to do ourselves and don't let QlikView do for us, following the coworker example by John.

To address those null values you can find in the data model, there are ways to make sure that, when loaded into QlikView, they have some value that can be later used to create those composite keys. JOINs, ApplyMaps(), If(Len()), etc. depending on the type of null (e.g.: blank or real absence) can help you achieve this.

All in all, I think it's perfectly OK to avoid synthetic keys, and is the de facto approach for most of us. Practically speaking, the trade off is very little, if it exists at all, and will create a better readable code for you and anyone else working with you on those QlikView apps.


Re: Should We Stop Worrying and Love the Synthetic Key?

While I've not used this, another way to handle nulls is with NullAsValue. You tell it which fields you want it to apply to, and for those specific fields, nulls will link to nulls as if they were values.



Re: Should We Stop Worrying and Love the Synthetic Key?

As you said, when I want to match null values, this is an alternative. But when I don't want null values to match, I can't do this, and I also can't to use synthetic keys.

At the same time, I will also worry that my tests and induction are not enough. I'm just doing a simple exclusion. I'm not sure that there will be no accidents as long as there is no null value in synthetic keys.


Re: Should We Stop Worrying and Love the Synthetic Key?

Hi MA:

I'd like to answer the next question for the test, but, I don't know what to answer…

Could you please tell the answer?

Thank you.



Re: Should We Stop Worrying and Love the Synthetic Key?

I seem to have found some benefits about automatically generated Synthetic Keys. The premise is that we need to give up our love for some of the computer hardware resources.

We often hear complaints that they take up terrible hardware resources and lead to server crashes. In fact, they never "waste" these resources. They have the amount of information that is worth the resources they occupy.

Imagine a scenario where our tables need a combination key to associate, but at the same time our front-end logic refers to each sub-column of the combination key separately.


Synthetic Keys not only generates composite keys, but also preserves the complete information of all sub-columns. When your front end references these sub-columns, these information becomes particularly important.

When you really need this information, you will find that your manual modification can easily lead to errors and loss of necessary information. And you'll also find that your clumsy modifications won't save more resources for the computer.

Looking back at the previous complaints, the fundamental reason is that they do not need all the information contained in Synthetic Keys. Their needs may be very simple - they just want to associate those tables correctly.

So I would like to state that the waste of hardware resources is not Synthetic Keys itself, but you are using a dragon sword to kill chickens.


BTW, I'm still worried about Synthetic Keys' behavior towards null values. This is something I can't let go of, although Synthetic Keys seem to have the above advantages.

I hate the new version of the community, which makes it difficult for me to format text and doesn't allow me to upload QVW files.