Kolosu,
first of all many thanks for all your support, very much appreciated.
I tried your new code, while the code is with no doubts more efficient, formula calculations did not give always the expected results, it doesn't work everytime the code must round the number, here is the evidence:
When I have at 160
Code:
000000000000000000003830
you can see that at column 181 we have an 8 so > 5, and the whole number is > +0, ok, but the formula produces this:
Code:
000,004;
which is not what we expected. 3830 + 1000 = 4830 / 1000 is 4,830 and I guess that the edit mask did the remaining work to give out the 000,004. I'm just guessing here, had not time to make some changes and try again. I will do it as soon as I can.
Joined: 26 Nov 2002 Posts: 12358 Topics: 75 Location: San Jose
Posted: Thu Nov 24, 2016 9:20 am Post subject:
Fab wrote:
which is not what we expected. 3830 + 1000 = 4830 / 1000 is 4,830 and I guess that the edit mask did the remaining work to give out the 000,004. I'm just guessing here, had not time to make some changes and try again. I will do it as soon as I can.
Fab,
I am not sure as to why you think it is wrong. As I explained in my very first post in this topic, if the amount field you starting with is a small number you would end up with zeroes. Check this post.
In reality your calculated values is 0.003830 So even if you apply the rounding it is still gonna end up as 0.004 as you wanted only 3 decimals.
Check what the beginning value of the field is and then calculate the formula in excel for that number and compare it against the results. _________________ Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Kolusu,
just checked in EXCEL (have no report to check for this calculated field, so I can only check in EXCEL and try to give an output number near to that, the more near the number will be to the EXCEL number, the best we applied the formula). In input I have:
Code:
77.468,53
So formula is:
Code:
=77468,53*0,01187/100/12*5
(here I'm using *5 because these data is from the last quarter I have elaborated, so September)
This formula gives:
Code:
3,83146438
Now I said I would like to stop at 3 dec, so what I was expecting was 3,831 which would be a great result to me. I apologize for my bad english, I'm doing my best tho
So you need to run my control cards as is. _________________ Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
the other fields are the following in the code I've attached above. You can see results are (strangely), different from yours. Also the calculated formula (the last column) is also reporting a strange values, but only when round up occurs. Following is a little sample of what I have in EXCEL:
EXCEL fields are not in the same order as my host output, 3rd column is what we calculate with formula and we have that field like the last one.
What I must change in your code is:
- need to change "REDUCED" and "NEW" strings to my native language
- need to chenge this code to reflect the right coefficient I need (1st quarter = 11, 2nd = 8, and so on...), so modified code would look like this:
- considering these data I am comparing are from September and we are now in the last quarter, we would have to change again this code to have the right coefficient I need to compare (just a temp modification of course):
Now we can easily compare these values with EXCEL values (that apply coefficient 05 in formulas).
Also I would need to adjust formula calculation because, as you can see, results are not the same like EXCEL, and would like to round up the two intermediate values (those we divide by + 100). That's all I guess, sorry for delay in answer I was out of my workplace.
So remember that when you are using FIELD-3 you might NOT get the desired precision as I was only considering 3 decimals.
2. Please ensure that your excel and mainframe data are in the same order and also the flags are a 1 to 1 match.
Ideally I would start with just 1 Or 2 records and then compare it against the excel output and start investigating from there.
If you still want me to diagnose the results, I would need to see the complete sysout messages so that I can verify if you have any installation defaults set differently.
Kolusu,
I apology for delay in answer and final thanks for you kind support. Just to explain myself, I needed to translate "reduced" and "new" into my native language (Italian), that is "decurtate" and "nuove". Also I decided to increase the number of decimals of the formula field and do not make any round about that. Formula calculation is good as it is right now, your code works flawless and output file is perfect. So I just wanted to say thank you once more.
Joined: 26 Nov 2002 Posts: 12358 Topics: 75 Location: San Jose
Posted: Thu Dec 15, 2016 11:17 am Post subject:
Fab,
Glad to hear you got job working the way you wanted. _________________ Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
All times are GMT - 5 Hours Goto page Previous1, 2
Page 2 of 2
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