Load Data From Previously Loaded Data – QlikView Resident & Preceding

1. Objective

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.

Load Data From Previously Loaded Data - QlikView Resident & Preceding

Load Data From Previously Loaded Data – QlikView Resident & Preceding

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.

Do you know What are QVD Files and how to create them?

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,

Country_population:
LOAD Country,
Capital,
[Area(km.sq)],
[Population(mio)],
[Pop. Growth],
Currency,
Inflation,
[Official name of Country] FROM
[Preceding Load\Country_population.csv] (txt, codepage is 1252, embedded labels, delimiter is ‘,’, msq);

Calculated_Table:
load
Country,
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.

Follow this link to know How to apply QlikView Layout Theme?

QlikView Quiz

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.

Preseding_Sample:
load
OfficeCountry,sum(Amount) as Amt
group by OfficeCountry;

Office_Loc:
Directory;
LOAD Office,
OfficeAddress,
OfficePostalCode,
OfficeCity,
OfficeStateProvince,
OfficePhone,
OfficeFax,
OfficeCountry,
Amount
FROM
SampleData.txt
(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.

5. Conclusion

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

Reference

Leave a Reply

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.