How to use Power (Pwr) function

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

vgupta88
Participant
Posts: 32
Joined: Thu Aug 01, 2013 12:26 am
Location: Mumbai

How to use Power (Pwr) function

Post by vgupta88 »

Hi All,

I am trying to use power function (pwr)in stage variable.. However, I am unable to achieve my result set in my parallel job.
Below is the expression that I need to use in my derivation. Just for your information, below derivation works properly fine when I run the formula in Excel sheet with some values:

((1+DSLink.Percent/100)^(DSLink.Period/12) -1)

I tried using below derivation in stage variable but was unsuccessful and it is not giving me correct results:

pwr((1+DSLink.Percent/100),(DSLink.Period/12)) -1

To be specific, I want the below expression to be calculated:
((1+(-2)/100)^(1/12)-1)*100

Can someone please help me out.

Thanks!
Vaibhav
vgupta88
Participant
Posts: 32
Joined: Thu Aug 01, 2013 12:26 am
Location: Mumbai

Post by vgupta88 »

Can someone please help me with this?
Vaibhav
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Never used that function, so don't know if there are any restrictions to using it but your examples don't seem to match. :?

Where you say "To be specific" there is a "*100" on the RHS that isn't anywhere else.
-craig

"You can never have too many knives" -- Logan Nine Fingers
UCDI
Premium Member
Premium Member
Posts: 383
Joined: Mon Mar 21, 2016 2:00 pm

Post by UCDI »

A negative value cannot be raised to a power that is not represented by an integer. If it is, the result of the function is PWR(-X,Y) and an error message is displayed.

I can't tell if you are having this issue or not.
vgupta88
Participant
Posts: 32
Joined: Thu Aug 01, 2013 12:26 am
Location: Mumbai

Post by vgupta88 »

Hi Craig,
Please ignore "*100" on the RHS.. that was just to show case one example that I actually needed..


Hi UCDI,
It's not a negative number.. if you manually resolve that value, it'll end up being:

((98/100)^(1/12))-1

Pls note the parenthesis in the above example.

Thanks!
Vaibhav
JRodriguez
Premium Member
Premium Member
Posts: 425
Joined: Sat Nov 19, 2005 9:26 am
Location: New York City
Contact:

Post by JRodriguez »

....my two cents
-Check the data type, the function expect dfloats
-Check the order of precedence in the math operations imposed by the parentheses...as per your example is a bit off
- Hardcode a couple of sample to.check that the function works as expected
Good luck
Julio Rodriguez
ETL Developer by choice

"Sure we have lots of reasons for being rude - But no excuses
UCDI
Premium Member
Premium Member
Posts: 383
Joined: Mon Mar 21, 2016 2:00 pm

Post by UCDI »

what is the answer you expect, and what do you get? This can be an example, but the closer you get to your actual code values and needs the better we can see what isnt right.

I also suspect () ordering but I need the expected and actual to see what you did wrong.
vgupta88
Participant
Posts: 32
Joined: Thu Aug 01, 2013 12:26 am
Location: Mumbai

Post by vgupta88 »

Hi Rodriguez,
I verified the datatypes and they are correctly set. Similarly, the parenthesis imposed are correct.

Hi UCDI,
If I consider the hard coded example as ((1+(-2)/100)^(1/12)-1), expected result is -0.001682143.
However, in DS, when I use the PWR function, I am always getting value as 1 (be it any hard coded combination). Maybe in my case, PWR function is not working well when considering fractional values.

To keep my work going, I implemented the work around. I calculated both the fractional values in separate stage variables i.e.
sv1: (-2)/100
sv2: (1/12)
sv3: (pwr(1+sv1,sv2)-1)

If I am using the above way, I am getting my correct results.

Thanks!
Vaibhav
UCDI
Premium Member
Premium Member
Posts: 383
Joined: Mon Mar 21, 2016 2:00 pm

Post by UCDI »

The C in me wonders what you get if you put in decimals to ensure nothing in the equation is accidentally treated as an integer.

((1.0+(-2.0)/100.0)^(1.0/12.0)-1.0)

my suspicion is that

1/12 = 0 (integer division)
so x^0 = 1
and
1 + 1 -1= 1
vgupta88
Participant
Posts: 32
Joined: Thu Aug 01, 2013 12:26 am
Location: Mumbai

Post by vgupta88 »

I have my datatype defined as decimal(38,10) only.. so that is not being treated as Integer.
Vaibhav
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

How do you define the data type of a constant in an expression, vgupta88? This is a technique with which I am not familiar, despite almost 50 years of working with computers! But I'm always happy to learn.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
vgupta88
Participant
Posts: 32
Joined: Thu Aug 01, 2013 12:26 am
Location: Mumbai

Post by vgupta88 »

Hi Ray,
Those constant values were just to illustrate my example. However, my expression has a datatype defined as decimal(38,10):

pwr((1+DSLink.Percent/100),(DSLink.Period/12)) -1

Thanks!
Vaibhav
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Yes, but 1,12 and 100 are constants. Integers.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
vgupta88
Participant
Posts: 32
Joined: Thu Aug 01, 2013 12:26 am
Location: Mumbai

Post by vgupta88 »

Yes. That is a part of formula that needs to be applied in the expression.
Vaibhav
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

1.0, 12.0 and 100.0, on the other hand, are Decimal numbers.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Post Reply