topic Re: Preceding Load & Resident Load in QlikView

Preceding Load & Resident Load

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

What is the difference between preceding load & resident load.

when we have to used preceding load?

Thank you.

Re: Preceding Load & Resident Load

Anonymous — Thu, 11 May 2017 05:47:03 GMT

------------------------------------------------------Preceding load ----------------------------------------------------------

1. The preceding load can be used to define multiple calculations and transformations within on e load script2 The 2.preceding load takes input from a previous load statement

3. Preceding load is invoked by checking “Preceding load” while selecting load statement

4. Using a preceding load you don’t need to perform the same calculation in several times5. We can do aggregations and calculations using Preceding load

Preceding Load Sample Script:

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);

------------------------------------------------------Resident load ---------------------------------------------------------

Resident load can be used to load from a previously loaded table.
In Resident load calculation or transformation can be handled
Resident load can be used to perfume calculation on existing fields and tables also
This requirement arises when you want to create a table deriving data from already existing table in the same script.
Aggregation will not work while loading on base table,in this scenario we have to use resident table .
Aggregation will work on already loaded data,we have use resident load for aggregation

Sample Script for reference

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;

Simple :Creating # Temp table in sqlserver in temporary basis and at the end after calculation drop the # Temp

Re: Preceding Load & Resident Load

Anil_Babu_Samineni — Thu, 11 May 2017 05:53:02 GMT

Excellent Blog

Preceding Load

Re: Preceding Load & Resident Load

Anonymous — Thu, 11 May 2017 06:07:44 GMT

Hi,

Generally, preceding load is a simplified function that loads faster than resident load, this is done by having the same calculation for many places.

Do have a look at this detailed document posted by Henric Crostrom:

Preceding Load

Let me know if this helps!

Regards,

Gaston

Re: Preceding Load & Resident Load

Anonymous — Thu, 11 May 2017 06:12:03 GMT

In most cases, the same result can be achieved by using a preceding LOAD instead, that is, a LOAD statement that loads from the LOAD or SELECT statement below, without specifying a source qualifier such as From or Resident that you would normally do. A preceding LOAD is generally the faster option, but there are some cases where you need to use a ResidentLOAD instead:

If you want to use the Order_by clause to sort the records before processing the LOAD statement.
If you want to use any of the following prefixes, in which cases preceding LOAD is not supported:
- Crosstable
- Join
- Intervalmatch

IS THIS RIGHT?

Re: Preceding Load & Resident Load

eliran — Thu, 11 May 2017 06:24:08 GMT

Hi Prachi,

The following examples you mentioned are right, in those cases you have to use resident.

But this whole post is a bit outdated, Since the new versions of Qlik (last couple of years), preceding load is SLOWER than RESIDENT.

Please check Rob's post from a year ago to further understand the issue

http://qlikviewcookbook.com/2016/04/the-cost-of-preceding-load/

BR,

Eliran.

Re: Preceding Load & Resident Load

Anonymous — Thu, 11 May 2017 08:02:39 GMT

Please go through this well explained with example

Resident Load:

In some cases we need to reload the same table more than once in a single script execution to

reprocess the same data after the first read in order to make it adequate for our data model. Since the

data is being stored in RAM after each query during the script execution, we can use that RAM-stored

data instead of going directly to the original data source. This is accomplished via a Resident Load.

Resident keyword is used to reference the data in RAM model, that is, all the tables that have

been previously read in the preceding queries of the same script.

Example:

Orders:

SELECT

OrderID,

Customer,

Sales

FROM Orders;

CustomerFirstInvoiceDate: // Reloading the Orders table with Group By to get the first invoice date

LOAD

Customer,

Min(Date) AS CustomerStartDate

RESIDENT Orders

GROUP BY Customer;

Precedent Load:

The preceding LOAD feature allows you to load a table in one pass, but still define several

successive transformations. Basically, it is a LOAD statement that loads from the LOAD or SELECT

statement below, without specifying a source qualifier such as From or Resident that you would

normally do.

You can stack any number of LOAD statements this way. The statement at the bottom will be

evaluated first, then the statement above, and so on until the top statement has been evaluated. You

can achieve the same result using Resident, but in most cases a preceding LOAD will be faster.

Another advantage of preceding load is that you can keep a calculation in one place, and reuse it in LOAD statements placed above.

Example 1: Transforming data loaded by a SELECT statement

If you load data from a database using a SELECT statement, you cannot use Qlikview functions to

interpret data in the SELECT statement. The solution is to add a LOAD statement, where you perform

data transformation, above the SELECT statement.

In this example we interpret a date stored as a string using the Qlikview function Date# in a

LOAD statement, using the previous SELECT statement as source.

Orders:

LOAD *,

Date(Date#(OrderDate, ’YYYYMMDD’), ‘MM/DD/YYYY’) as OrderDate;

SQL SELECT OrderID

OrderDate ,

Amount

FROM Orders;

Example 2: Simplifying your script by reusing calculations

In this example we use a calculation more than once in the script

Data:

LOAD *,

Age( FromDate + IterNo() – 1, BirthDate ) as Age,

Date( FromDate + IterNo() – 1 ) as ReferenceDate

Resident Policies

While IterNo() <= ToDate - FromDate + 1 ;

By introducing the calculation in a first pass, we can reuse it in the Age function in a preceding

load

Data:

LOAD ...,

ReferenceDate,

Age( ReferenceDate, BirthDate ) as Age;

LOAD *,

Date( FromDate + IterNo() – 1 ) as ReferenceDate

Resident Policies

While IterNo() <= ToDate - FromDate + 1 ;

Re: Preceding Load & Resident Load

Anonymous — Thu, 11 May 2017 14:14:33 GMT

guys i am getting confused.

Re: Preceding Load & Resident Load

devarasu07 — Thu, 11 May 2017 14:38:26 GMT

Resident Load:

It allows you to create a new logical table using qlikview based on existing table (which loaded in memory)

Syntax

TabeA:

Load ...

From ....;

[New Table]:

Load

....

Resident TabeA;

Preceding Load:

Its contains more than one load statements that execute from the Bottom up.

--> It allows you to created additional ETL transfermation using transformed field from the previous load statements.

let say, Calculate margin using two fields from the previous load statement: sales and LandedCost

example,

Fact:

Load *,

((Sales-LandedCost)/Sales) as Margin; // preceding load using previous load statement field

Load * Inline

Product,Sales,LandedCost

A,210,100

B,310,150

C,500,400

];

Hope this helps you

Regards,

Deva