topic Re: Calculated Field in Load Script takes much time!!! in QlikView

Calculated Field in Load Script takes much time!!!

dmohanty — Mon, 16 Feb 2015 19:52:32 GMT

Hi All,

I have to create a calculated field (a3 * b1) / b2 as c1 from the below script. But the issue is that each table is having more than 10 Million records and the calculation in taking a huge time.

Any other approaches to do this?

My script is like below:

I have a scenario like this:

A: ////12 million records

Load

a1,

a2,

from A.qvd;

Left join

Load //// 10 million records

a1,

b1,

from B.qvd;

A_New:

Load

(a3 * b1) / b2 as c1 ///// This creation takes a huge time because of millions of records.

resident A;

Drop Table A;

Re: Calculated Field in Load Script takes much time!!!

Colin-Albert — Mon, 16 Feb 2015 20:25:08 GMT

Do you need to recalculate the sum across the whole dataset each time you load?

Can you do the sum once and save a QVD with the values calculated and then just add new rows to the table.

Search for incremental load for ideas on this.

Another approach could be to use a mapping load.

From your expression (a3 * b1) / b2 is the same as a3 * (b1 / b2)

You could calculate the ratio b1 / b2 in a mapping table.

Then the script does not need any joins.

BCalcMap:

Mapping load

a1,

(b1 / b2) as ratio

from B.qvd;

A_New:

load

a1, a2, a3,

( a3 * applymap('BCalcMap', a1) ) as c1

from A.qvd ;