topic Re: Calculation on Derived field in QlikView

Calculation on Derived field

Thu, 20 Nov 2014 09:49:41 GMT

Hi All,

While loading I am deriving two fields based on some calculations. lets say

Load *,

Col1 + Col2 = Col5,

Col3 + Col4 = Col6

Resident Temp;

But in the above code only I want to derive two more fields based on Col5 and Col6

Like :

If (Col5 > Col6 , Col5+ Col6, Col5- Col6) as Col7,

Col5 * Col6 as Col8.

So complete code should be like

Load *,

Col1 + Col2 = Col5,

Col3 + Col4 = Col6.

If (Col5 > Col6 , Col5+ Col6, Col5- Col6) as Col7,

Col5 * Col6 as Col8

Resident Temp;

How can I achieve? Any Help?

Re: Calculation on Derived field

tresB — Thu, 20 Nov 2014 09:56:12 GMT

Try preceding load like:

Final:

Load

If (Col5 > Col6 , Col5+ Col6, Col5- Col6) as Col7,

Col5 * Col6 as Col8 ; // preceding load

Load *,

Col1 + Col2 as Col5,

Col3 + Col4 as Col6

Resident Temp;

Or, you can simply do it using simple math replacing Col5 by (Col1+Col2), .....

Re: Calculation on Derived field

Anonymous — Thu, 20 Nov 2014 10:06:23 GMT

you can try below code

Load *,

Col1 + Col2 = Col5,

Col3 + Col4 = Col6.

If ((Col1 + Col2) > (Col3 + Col4), (Col1 + Col2)+ (Col3 + Col4), (Col1 + Col2) - (Col3 + Col4)) as Col7,

Col5 * Col6 as Col8

Resident Temp;

Re: Calculation on Derived field

Thu, 20 Nov 2014 10:18:21 GMT

HI Tresesco,

What will be difference between the below two:

1st Method is (Your suggestion)

Final:

Load

If (Col5 > Col6 , Col5+ Col6, Col5- Col6) as Col7,

Col5 * Col6 as Col8 ; // preceding load

Load *,

Col1 + Col2 as Col5,

Col3 + Col4 as Col6

Resident Temp;

2nd Method is :

Temp:

Load *,

Col1 + Col2 as Col5,

Col3 + Col4 as Col6

Resident Temp;

Final:

Load * ,

If (Col5 > Col6 , Col5+ Col6, Col5- Col6) as Col7,

Col5 * Col6 as Col8

Resident Temp;

Drop Table Temp..

Will there be any difference in terms of performance or anything?

Re: Calculation on Derived field

tresB — Thu, 20 Nov 2014 10:25:55 GMT

The second approach would be costlier(in terms of performance) than the first one, because the second one would make running a second pass through the same table. For better understanding, read this blog post: preceding-load