Load Data From Previously Loaded Data – QlikView Resident & Preceding
By far, we have seen how to load data from files and from databases. However, there is one more way of loading data i.e. how to load data from previously loaded data table in QlikView. In this tutorial, we will learn the reason for which data is loaded from previously loaded or already existing tables. Moreover, we will learn what is QlikView Resident Load and Preceding load in QlikView.
So, let’s begin to load data from previously loaded data in QlikView.
2. How QlikView Load Data From Previously Loaded Data?
You know that when you load data from a database, it is done from a SELECT statement. And when we load data from external files, it is done by the LOAD statement. There is one more catch to it i.e. when SELECT statement is used, no calculations can perform on the data being loaded from the database. You cannot add any expressions within the SELECT statement because data will fetch as it is, and no functions are applicable to it.
In such a scenario, to apply expressions on the loaded data, it needs to load again in the script. We can load it in two ways, either by using a Resident prefix with LOAD commonly called Resident Load or by using Preceding LOAD in a LOAD statement.
3. QlikView Resident Load
The prefix Resident use to load data from previously loaded data table in the same script. Data can transform, and mathematical operations can perform through resident loading. QlikView Resident load takes fields and tables from an already loaded table within the same script. An example of a QlikView Resident load is,
[Official name of Country] FROM
[Preceding Load\Country_population.csv] (txt, codepage is 1252, embedded labels, delimiter is ‘,’, msq);
sum([Population(mio)])/1565 as percentage_Population
resident Country_population group by Country;
drop table Country_population;
As you can see in this example, a table named ‘Country_population’ load first into the script than a new table name as ‘Calculated_Table’ create which calls a field ‘Country’ from the previous table, then Sum function apply on another field and at last the previous table is called using QlikView Resident Load.
4. Preceding Load in QlikView
Preceding load in QlikView loads data from the table present below the preceding load statement. This method of load data from previously loaded data is pretty useful. The QlikView Preceding statement does not need a location qualifier like From or Resident which indicates towards a data source. It has a fixed pattern i.e. evaluating the bottom-most statement first, then the one above it, then the one above and so on. One use of preceding load in QlikView is that you can place a load statement defining some calculations to perform on data at the bottom and then load data tables. By doing so, the script will evaluate the calculations first and apply it on the data in the table.
Let us see an example of the Preceding load in QlikView.
OfficeCountry,sum(Amount) as Amt
group by OfficeCountry;
(txt, codepage is 1252, embedded labels, delimiter is ‘\t’, msq);
As is clear from this example, that the statement on the top is the Preceding statement which applies a function on the field from the table loaded in the statement below. During script execution, the statement at the bottom loaded first loading the table and then the statement above it will evaluate and Sum on the field values of the field Amount will calculate.
A preceding load in QlikView is a faster method to use than QlikView Resident load as it does not require a location qualifier. Also, using the Preceding load, you can apply multiple calculations and transformations through stacking up LOAD statements.
So, this was all about How QlikView Load Data From Previously Loaded Data. Hope you like our explanation.
In this tutorial, we discussed two ways in which we can load data from a previously loaded data table. The two ways are using predicates QlikView Resident Load and Preceding Load in QlikView in the LOAD and SELECT statements. Still, have a doubt feel free to ask in the comment box.
Related Topic – QlikView Script Statements