How The Synthetic Keys Are Generated In Qlikview
Mixing ValueLoop, ValueList and Parametarised Variables to create the mother of all synthetic dimensions
- Qlik Synthetic Key
- How The Synthetic Keys Are Generated In Qlikview 1
- How The Synthetic Keys Are Generated In Qlikview Office
- How The Synthetic Keys Are Generated In Qlikview History
- How The Synthetic Keys Are Generated In Qlikview One
A Synthetic Key is QlikView's solution to create an artificial key when there is ambiguity about which key to use between two tables. This situation arises when two tables have two or more fields in common. Synthetic keys in Qlikview: ‘Synthetic keys occur when two or more tables have two or more fields in common.’ However, when we have more than one common field between two or more tables, QlikView creates “SYNTHETIC KEYS” and “SYNTHETIC TABLE”.
I’ve seen quite a few posts recently about the use of QlikView’s Dollar Sign Expansion using parameters and the use of the ValueLoop() and ValueList() functions. I thought, for my first post I would demonstrate a real world example combining all of these techniques using a synthetic dimension.
Recently for a project I was working on, I needed to find a way of grouping data from two alternate date states into a single table. The date periods would always be in comparable buckets (ie, weeks or months) but the application made it possible to compare any date range with any date range. This flexibility ruled out standard set analysis flags for comparing dates as it was not possible to pre flag associated ranges as demonstrated in Richard Pearce’s excellent Date Comparison Calendar.
Creating a Synthetic Dimension
The approach I took to solve this was to use a synthetic dimension. A synthetic dimension is nothing more than one that didn’t exist in the data when it was originally loaded. QlikView gives us two functions to play with to create synthetic dimensions. The ValueList() and the ValueLoop(). On the surface, these are 2 straightforward functions but don’t be fooled; they are both powerful functions in their own right but their usage and flexibility is not widely appreciated. To create the dimensions for this table, I started with the ValueList() function. In its most basic form, it can be used as such:
Which will give you a chart with 3 dimensions called Month 1, Month 2 and Month 3. However, we can take the creation of the synthetic dimension a step further by nesting a ValueLoop() within the ValueList(). This way, the synthetic dimension can be generated dynamically, based on our data:
Qlik Synthetic Key
Note, we’ve wrapped our ValueLoop() in a concat() function. This has the effect of flattening the output of the loop into a text string, ready to be used within our ValueList(). This code in turn is saved in a variable called mySynthDimension which we can call from within the ValueList() when we are ready to use it.
So, hopefully you are still with me. If you’ve gotten this far, you’re more than half way as we use the same technique with the ValueLoop() and ValueList() to create the expression. Stay tuned, the really cool stuff happens next.
Creating the expressions
The chart type we use for this table display is the Straight Table with the Horizontal option checked under the Presentation tab. This in effect pivots the table so our date rows are presented across the top (Month 1, Month 2 etc) as columns and our expressions are shown as rows. In order to show the figures as we want them in the table, we need to create two expressions. One for the buckets in the top chart and one for the buckets in the bottom.
The buckets are in fact dates, and it just so happens that we have a date serial in our calendar dimension. A date serial is just a sequential list of numbers corresponding to each of your dates in order. This is handy as it allows you to add 1 to a date to get the next day/week/month/whatever and not worry about cumbersome date arithmetic. To that end, our expression would look a bit like this:
These expressions take the lowest possible date for the two date states (the Current and Prior period) and display them in the table. This won’t work quite yet though for two reasons. Firstly, it’s only ever going to show the value for the lowest selected date and secondly, seeing as we have built a synthetic dimension, we need a way of telling QlikView how to bucket these expressions into the dimensions we created.
Assigning the totals to the correct synthetic dimension
The documentation for ValueList() suggests a very cumbersome way of assigning your metrics to the correct dimension. Basically, it has you rewriting the code for your synthetic dimension multiple times to test for each scenario. In our example if would look a bit like this:
…which is a bit cumbersome I have to admit. However, there is a better way that makes your code a whole lot more manageable and opens up a world of possibilities. Enter the well loved and practically inseparable Pick(Match()) functions:
As you see, the code is significantly more compact and readable. This technique introduces the RowNo() function which returns effectively returns the row number or dimension bucket the code is in. The pick(match()) combo is also a lot more performant than doing a nested IF() so use it wherever you can.
Making it dynamic, the Parameterised Dollar Expansion
Now we have a dimension and a couple of expressions we can use, it’s time to introduce the dollar expansion with parameters. In this way, we can create the closest thing to a client side function that Qlik allows. So, lets take the expressions we created earlier and put them into a couple of variables:
How The Synthetic Keys Are Generated In Qlikview 1
Generate public key from wife. Note the inclusion of the $1 in the code. This allows us then to pass data into our variable as it is executed. So we can do something like this:
Movavi Video Editor 20.2.0 Activation Key UpdatedIn addition to the above sentence, the Movavi Video Editor Activation Key supports most formats, for example, MP3, MOV, JPG, MP4, AVI, MPEG, BMP, ASF, PNG, WAV, MKV, DVA, DAV. Movavi Video Editor Crack brings new effects and variations from various texts along with Wood, Waterhouse, Whistler, Van Gogh, Vermeer, Turner, Define, Spread, Streak, Mosaic, and Parts. Additionally, it allows you to view quick slideshows, 3-D snapshots, mixed-media information (media playback), video and sound documents.In addition, Movavi Video Editor Keygen supports a variety of udio formats so you can upload a video or voice over for your movies and edit or equal your audio documents from the software. After editing the video, you can directly upload multimedia files, including audio, video, recording, photographs and TV clips. Despite its excellent functionality, it is a software program that can be used to enhance your video quality.Compared to other video editing software, the Movavi Video Editor Full Version Crack Mac is especially easy to apply to novices, take your many thoughts, and give yourself some time to get a video like Hollywood without much effort. 
If we now reintroduce our ValueLoop() and ValueList() functions we can build a completely dynamic expression which responds actively to user selections:
When the code runs, it produces something like this:
…which finally matches the calculations to the correct synthetic dimension.
How The Synthetic Keys Are Generated In Qlikview Office
A short note on performance
How The Synthetic Keys Are Generated In Qlikview History
This is a succinct and easy to maintain solution to a specific problem and in my tests, it produced more than adequate performance. However, because the expression effectively changes for each dimension bucket it calculates against, it will not be as fast as working with a standard dimension and expression, it may however, get you out of a tight spot though.