Calculation on Derived field

[#1]

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?

[tresesco · Accepted solution]

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), .....

[Anonymous]

HI

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;

[#4]

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?

[tresesco]

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