DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
This topic has been marked "Resolved."
Author Message
vgupta88
Participant



Joined: 01 Aug 2013
Posts: 14
Location: Mumbai
Points: 149

Post Posted: Sat Jun 17, 2017 8:16 am Reply with quote    Back to top    

DataStage® Release: 9x
Job Type: Parallel
OS: Unix
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



Joined: 01 Aug 2013
Posts: 14
Location: Mumbai
Points: 149

Post Posted: Mon Jun 19, 2017 12:17 am Reply with quote    Back to top    

Can someone please help me with this?

_________________
Vaibhav
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 41909
Location: Denver, CO
Points: 215066

Post Posted: Mon Jun 19, 2017 7:20 am Reply with quote    Back to top    

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

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

_________________
-craig

"I was a king once, then they made me a bird. Then Mother Church came along and turned us all into saints, trolls and fairies. General Mills did the rest." - Mad Sweeny
Rate this response:  
Not yet rated
UCDI



Group memberships:
Premium Members

Joined: 21 Mar 2016
Posts: 206

Points: 2170

Post Posted: Mon Jun 19, 2017 7:53 am Reply with quote    Back to top    

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.
Rate this response:  
Not yet rated
vgupta88
Participant



Joined: 01 Aug 2013
Posts: 14
Location: Mumbai
Points: 149

Post Posted: Tue Jun 20, 2017 4:36 am Reply with quote    Back to top    

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
Rate this response:  
Not yet rated
JRodriguez



Group memberships:
Premium Members

Joined: 19 Nov 2005
Posts: 372
Location: New York City
Points: 3962

Post Posted: Tue Jun 20, 2017 5:46 am Reply with quote    Back to top    

....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
Rate this response:  
Not yet rated
UCDI



Group memberships:
Premium Members

Joined: 21 Mar 2016
Posts: 206

Points: 2170

Post Posted: Tue Jun 20, 2017 2:24 pm Reply with quote    Back to top    

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.
Rate this response:  
Not yet rated
vgupta88
Participant



Joined: 01 Aug 2013
Posts: 14
Location: Mumbai
Points: 149

Post Posted: Wed Jun 21, 2017 3:56 am Reply with quote    Back to top    

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
Rate this response:  
Not yet rated
UCDI



Group memberships:
Premium Members

Joined: 21 Mar 2016
Posts: 206

Points: 2170

Post Posted: Wed Jun 21, 2017 12:26 pm Reply with quote    Back to top    

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
Rate this response:  
Not yet rated
vgupta88
Participant



Joined: 01 Aug 2013
Posts: 14
Location: Mumbai
Points: 149

Post Posted: Thu Jun 22, 2017 1:36 am Reply with quote    Back to top    

I have my datatype defined as decimal(38,10) only.. so that is not being treated as Integer.

_________________
Vaibhav
Rate this response:  
Not yet rated
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 53912
Location: Sydney, Australia
Points: 292552

Post Posted: Thu Jun 22, 2017 4:53 am Reply with quote    Back to top    

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.

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Canberra, Sydney and Melbourne
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours