Whilst running a recent training course I was surprised to find some reasonably experienced delegates were not aware of layering multiple Preceding Loads.
This series of posts looks at some of the features rarely blogged about as they are so second nature to many experienced QlikView bloggers. Before we get stuck into preceding loads fully it is important to understand the order things happen in your load script. The SQL part is executed and returned first and is then parsed by the LOAD statement above.
Put simply, a preceding load allows you to use values derived in one part of the load in the one above it. With a simple preceding load we can remove that duplication and make the code cleaner and more readable.
You will note that the fields we created in the lower part of the load are then used in the one above. Generally I would advise against the use of an asterisk (particularly when pulling fields from a database) but in preceding loads they are most useful.
Whilst it doesn’t really belong in a back to basics post, I should mention that there are functions you can use in your preceding loads that you may previously have only associated with the first level of load. Having preceding loads in your kit bag of QlikView load script code allows you to build complex expressions, whilst keeping your code simple by breaking things up into bite sized chunks. Well, it?s worth to say that time dropped from minutes to seconds, memory consumption was reduced during the script execution , and customer developer became happy learning how to build an optimized script. Like your example a proceding load is very good at creating a common field in the first load section which is re-used in the top. The same technique of converting and tidying your date ahead of doing all the various breakdowns of the date could use a Preceding Load also, just no need to use separate calendar table (unless you want to ?? ). Hi Jane – I think my biggest number may be for a client I am visiting on Tuesday next week. It is very much like a sub-query, except you are not able to specify a JOIN to the rest of the statement. I’m not sure of quite how things work under the bonnet, but the point is that things are not executed many times, depending on the number of preceding loads. Try these two bits of script and you will see straight away that the preceding load happens in about the same time as the initial load using the RESIDENT method.
We plan to bring ESB into play over the next 6 months which means we can load anything in the enterprise to the DW to be consumed by QlikView. Hi Paul – I always say that you should move the logic to the place that is best placed to handle it. Actually I should add that Preceding Loads are faster than Resident loads, I just tested this today. Hi (again) Paul – yes, RESIDENT loads are generally a big cause of slow downs in load scripts. I have found that optimised loads are not optimised the minute you try to involve a predicate in the LOAD. And then attempting to load the subset using a predicate from resident, this is what took hours to run, and this is on a big server.
I’ve used preceding loads throughout my script and would now like to use two of these derived fields to create a further derived field. Hi John – are these tables being concatenated on load, or associated in the data model?
You will need to get the value from one table into the other table, ahead of the preceding load. Hi – make sure you are not mixing SQL and QlikView syntax for joins in the wrong places. The LOAD part is not strictly required – but I always include it as it makes it clear what is happening where.
I think from reading above, I kinda already know the answer to this question so should be quick response!!


I’m connecting to SQL database and for example will be pulling in about 20 fields from a table which has about 100 fields, and 110,000 rows of data so far (growing daily). In the preceding load section I’m doing all my Apply Mapping and reformating of date fields etc, and then storing the these into QVD files for later use in the dashboard apps! So to recap: My script(s) is currently based on example1, should I go with the script based on example2? There are a number of advantages to this over doing a wildcard load, including the ability to embed logic within the loop.
Getting data from stored procedures is never ideal, as you don’t have control over what data you pull within a WHERE statement or by picking only certain columns. Your latter suggestion is actually what our IT director suggested when we first started brain storming this project, and he isn’t even that familiar with Qlik!
In the main, script is executed from top to bottom, then let to right along the tabs (the tabs have no functional relevance are only there to tidy code).
This idea of loading from the bottom up holds true as we get into multiple layers of preceding loads, and it is important to keep in mind as we continue. Whilst I have been very reliably informed (by Henric Cronstrom) that QlikView with it’s clever caching will not need to calculate the values twice, duplication is a bad thing from a code maintainability point of view.
Be aware you can also pull fields up explicitly by listing them if you want to take only some fields from your lower LOAD to the preceding one. If you wanted to have another value calculated on fields derived in your preceding load you can add a preceding load on your preceding load. And whilst it may sound like you are creating a potential confusion, like a BI version of Inception, preceding loads tend to help you to clean and simplify script. He is a 2016 Qlik Luminary, Qlik Community MVP and Technical Editor of a number of QlikView Books. If it is true, what is the difference, in terms of performance, with N+1 Load … resident blocks ? All I know is when you do a Preceding load the data is just parsed once, with all preceding layers proceeded in one pass. The stored proc has many temp tables, but I only want the final results to be loaded into qlikview as fields.
These techniques are useful to revisit though and for anyone who has not come across these features before they are the things you really should become aware of.
When loading from an ODBC or OLEDB data source you have a SQL SELECT statement and the wizard will (optionally) add a LOAD section ahead of the SELECT.
Loops and subroutines will alter this execution path, by design, but apart from that this statement holds true.
To take the example of our date interval we could then add another field based on whether a threshold has been breached or not. You can have expressions that use fields from any of the levels below to create new values.
Would preceding loads be the way to go to bypass the need for many temp tables, or is there a different approach I should be using? The gotcha however is with LOAD blocks, which can be stacked together and execute from the bottom up. I would recommend that you always use one of these ahead of a database load – as it opens up a whole range of syntax that is not available in the SQL statement. The other potential problem is that you can duplicate a field by having a field name used in the succeeding load that is pulled through with a * that is then used again in the preceding load. These can be hard to spot and the error message from QlikView (field names must be unique) does not always point you to the right part of the load script – so be careful.



Adidas promo code forum
Best free website template sites




Comments to «Calculate date difference qlikview»

  1. Voyn_Lyubvi writes:
    They skimp and never constructive criticism subject of a lot of academic and.
  2. ESCADA writes:
    Do not let one particular you far better and eventually fall in adore with you qualities, in which.
  3. help writes:
    Pleasure in this tongue that I am speaking it is time for us ladies more calculate date difference qlikview demure, a tiny less assertive. About your.
  4. sex_detka writes:
    When you're a calculate date difference qlikview woman with an IQ of 147 man who is strong sufficient to deal with your intensity when.