Posted: Fri Nov 18, 2016 9:34 am Post subject: How to perform a formula calculation
Hello,
I have a rather simple task to attain, but I wasn't able to complete everything I wanted.
I have a simple file in input and have to create a new output creating some new fields according to some criteria I will (try) describe below.
As you can desume from SORT, INPUT has 3 signed numerical fields, starting from position 40 with these pictures:
field 1 cols 40,18 PIC S9(15)V9(3)
field 2 cols 58,17 PIC S9(15)V9(2)
field 3 cols 75,17 PIC S9(15)V9(2)
I wanted to report these fields in OUTPUT as they are in input, with the same pictures. I know I've messed a bit with EDIT, but result right now is acceptable.
As you can see from SYSIN for some fields I need a simple multiply by 100 (always with sign), but I also would need to write in OUTPUT another field that must be calculated according the following formula:
IF "REDUCED" (or column 9 of INPUT = 'D') (((field 1 * 0,01187)/100)/12)*11)
ELSE (field 3 /12)*11)
This new OUTPUT field must be written as PIC S9(15)V9(3)
Now if you output definition has only 3 decimal digits you are going to end up with zeros. Is that what you want?
Also it would be helpful if you can paste some sample data for Field-1/2/3 and the expected result from the formulas.
Btw there is no formula related to Field-2 ? do you just need to copy it to output? _________________ Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Thanks for your attention and answer Kolusu,
I will try to clarify everything answering your questions in order.
1. I don't want to end up with zeroes, but my new output calculated field should have (if possible) 3 decimal digits.
2. Field 1 is usually a big value, and is the value I have calculated in my above sort ((40,18,ZD,MUL,+1),EDIT(SIII.III.III.III.TTT,TTT),SIGNS=(,-)). Field 3 is not a big value and must be the value I calculated in my above sort ((75,17,ZD,DIV,+100),EDIT(SII.III.III.III.III.TTT,TT),SIGNS=(,-)).
These are some real values I have, both cases:
If 39:1 = 'D' (Field 1 is the calculated value (40,18,ZD,MUL,+1),EDIT(SIII.III.III.III.TTT,TTT),SIGNS=(,-))
Field 1 = 46.481,11 Then (46481,11*0,01187/100/12*11 = 5,058)
Field 1 = 77.468,53 Then (77468,53*0,01187/100/12*11 = 8,429)
Field 1 = 108.455,95 Then (108455,95*0,01187/100/12*11 = 11,801)
Field 1 = 4.620.000,00 Then (4620000*0,01187/100/12*11 = 502,695)
If 39:1 = 'N' (Field 3 is the calculated value (75,17,ZD,DIV,+100),EDIT(SII.III.III.III.III.TTT,TT),SIGNS=(,-))
Field 3 = 51,88 Then (51,88/12*11 = 47,557)
Field 3 = 43,23 Then (43,23/12*11 = 39,628)
Field 3 = 37,95 Then (37,95/12*11 = 34,788)
Field 3 = 17,86 Then (17,86/12*11 = 16,372)
This new calculated field must be reported in output after the last field I am actualy reporting, so after 75,17 position (so probably 91,18), with picture PIC S9(15)V9(3), preferably.
3. Field 2 is indeed not affected from any formula, and must be reported in output just multiplied by +100
I see I have forgotten to mention a thing. In my previous examples I always mentioned the constant 11, but that is not a constant. I run this JCL once every three months (4 times a year), and that value must be decremented accordingly the related quarter, so it will be 11, 8, 5, and 2.
I did not mention it before because in the input file there is nothing that can be used to identify the quarter I am running, so I simply guessed that I will have to edit the jcl every time. But if that can be avoided I will be even more grateful, you know
I did my best to clarify things, but, please let me know if you need to know anything else.
Thanks again for your attention.
This new calculated field must be reported in output after the last field I am actualy reporting, so after 75,17 position (so probably 91,18), with picture PIC S9(15)V9(3), preferably.
I just verified that this new field should be placed between Field 2 and Field 3, sorry.
Quote:
3. Field 2 is indeed not affected from any formula, and must be reported in output just multiplied by +100
Joined: 26 Nov 2002 Posts: 12358 Topics: 75 Location: San Jose
Posted: Mon Nov 21, 2016 12:07 pm Post subject:
Fab wrote:
If 39:1 = 'D' (Field 1 is the calculated value (40,18,ZD,MUL,+1),EDIT(SIII.III.III.III.TTT,TTT),SIGNS=(,-))
Field 1 = 46.481,11 Then (46481,11*0,01187/100/12*11 = 5,058)
Fab,
You seem to have rounded up the values. DFSORT deals with integers only and I end up with the final result as 5.049 as we only consider 3 decimal places. If you do need to round up then we have to increase the precision.
when you divide a number by another number and want to retain the decimal value then first multiply by the no: of digits and then use divide.
Code:
ex: 21/100 = 0 since fractional part is lost
ex: (21* 100)/100 with edit mask (tt.tt) = 00.21
In your case we needed 3 decimals, so we multiply with +10000 and then later on divide to get 3 decimals.
Fab wrote:
I see I have forgotten to mention a thing. In my previous examples I always mentioned the constant 11, but that is not a constant. I run this JCL once every three months (4 times a year), and that value must be decremented accordingly the related quarter, so it will be 11, 8, 5, and 2.
I did not mention it before because in the input file there is nothing that can be used to identify the quarter I am running, so I simply guessed that I will have to edit the jcl every time. But if that can be avoided I will be even more grateful, you know
It is quite easy to determine the quarter you are running in. DFSORT has LASTDAYQ which lets you determine the last day of the quarter. So we determine the lastday of the quarter using the current date when the job ran.
Fab wrote:
This new calculated field must be reported in output after the last field I am actualy reporting, so after 75,17 position (so probably 91,18), with picture PIC S9(15)V9(3), preferably.
well you are expanding the final output with delimiters and hence the calculated field will not be at position 91. It will be at position 104.
Use the following DFSORT control cards which will give you the desired results
Thanks so much Kolusu,
I just tried your code and it works perfectly, but I see something that must be refined by me.
Values of my yesterday post was calculated/taken from EXCEL, probably they were automatically rounded there depending on how may dec I have specified in cell format.
Quote:
DFSORT deals with integers only and I end up with the final result as 5.049 as we only consider 3 decimal places
understood, so what I need to do to increase precision? increase the number of decimals? because I see that I would need more precision expecially on the calculated numbers.
data from my old sort was like this:
2100363000;
87.152,10;
006,27; <-- acceptable precision, but if I can increase it would be better
012,55; <-- acceptable precision, but if I can increase it would be better
with your new code I have:
2100363000; <-- this is just as input, no calculated so it's ok
87.152,10; <-- this is 40,18, it's ok
000,62; <-- not enough precision
001,25; <-- not enough precision
001,724; <-- this is the new introduced field calculated by formula, this one needs the more precision I can get (previously it was calculated every time by hand in EXCEL appllying the formula I posted here, my goal was to understand if the process ca be completely automated in host)
why with (58,17,ZD,DIV,+100),EDIT(SII.III.III.III.III.TTT,TT),SIGNS=(,-)
I have 6,27
while with 58,17,SFF,DIV,+100, EDIT=(SI.III.III.TTT,TT), SIGNS=(,-),
I have 000,62?
To have the same value in output as before I have to divide by 10 instead that by 100, is this because of the different EDIT mask or because of the SFF?
I changed this piece of code to accomodate my "quarter" needs (month 03 means 1st quarter, so c'11' is appropriate). I see that these fields reside outside maximum 128 width. So I understand these are just there to permit the identification of the right quarter and so the right numerical coefficient. But I would like to understand these two value:
Quote:
140:130
150:144
does that meant that the 1st field begin at position 130 til 140, and the other one from 144 to 150?
Not so clear how it works.
Joined: 26 Nov 2002 Posts: 12358 Topics: 75 Location: San Jose
Posted: Tue Nov 22, 2016 11:56 am Post subject:
Fab wrote:
understood, so what I need to do to increase precision? increase the number of decimals? because I see that I would need more precision expecially on the calculated numbers.
You can increase the precision by multiplying with the number of decimal digits you want.
for ex: Field 1 = 46.481,11 Then (46481,11*0,01187/100/12*11 = 5,058)
So if you really want to have rounded based on the decimals, then you need another IFTHEN to check decimals and round it
we have 5.057,525.
I check the the byte 3rd byte from right i.e 5 in 525 at the end. So if it is Greater than or equal to 5, I add +1000 to it and then it would be become 5.058,525. Now all you have to do it simply divide it by 1000 to have 5,058
To have the same value in output as before I have to divide by 10 instead that by 100, is this because of the different EDIT mask or because of the SFF?
It is because of the edit mask. So you need to determine what kind of edit mask you want.
Fab wrote:
I changed this piece of code to accomodate my "quarter" needs (month 03 means 1st quarter, so c'11' is appropriate). I see that these fields reside outside maximum 128 width. So I understand these are just there to permit the identification of the right quarter and so the right numerical coefficient. But I would like to understand these two value
Since I used IFOUTLEN=128, anything beyond that will not show up in your output. Basically I created 3 temp fields
1. At position 130 I put the Current date as CCYYMMDD.
2. Now using that current date at position 130 , I calculated the lastday of the quarter using LASTDAYQ function and put that value in position 140 which will be of the format CCYYMMDD
3. Now I validate month (MM) field of the last day function at 140. The month(MM) is at position 144. Using a Change command I add in the desired constant and put that value at 150.
We have all the information we needed and we perform our mathematical formula using the value at position 150. And IFOUTLEN=128 gets rid off the temp fields.
If you really want to understand how the job works, change the Value of IFOUTLEN=160. This will create an output file of 160 bytes in length.
Once you understand the job, you can put back the original output length. _________________ Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
After few tests I see there is something that I loose and this is not so good to me:
My sort output should match a report that is created reading the same input that I read in sort. In the example of my previous post on that report I have:
Code:
334,89
669,79
I know that these fields must be written with 2 dec, so now, as you suggested me in your last post, I will have to apply the 2nd ifthen to round values. This way I will have:
Code:
3,35
6,70
which I suppose is the most correct value I can give. Many thanks again for your suggestions.
I reduced MUL,+10000000 to MUL,+10000, because I had too big numbers, ie:
919,550
91.399,000
and so on, while now I have (with "old" code, not the one you suggested to round up numbers):
000,918
091,398
which I think are more realistic (for these numbers I have any report evidence to compare to, so I can just desume and try to calculate with EXCEL to make a comparison.
Second question is, how can I insert your code to round up numbers after these two calculation? (you can see I commented the ZD to use again SFF, but I can find it working only in this way)
I tried, but always do something wrong, always get sort abends. Most probably I am still not completely confident with your code to modify it independently. Thanks in advance.
Here you have 000 and 438 and since 0 and 4 are less than 5 we did not apply the edit mask and hence it is left as is.
You need another IFTHEN to have the edit mask on for the condition when the rounding is NOT needed.
Also I apologize for the over sight of handling negative input values. If your input is a negative value and during rounding if you are adding +1000 it will produce incorrect results.
Code:
-46.481,11 Then (-46481,11*0,01187/100/12*11 = -5.057,525 add +1000 = -5.056
-5.056 is an incorrect value. So you need another IFTHEN to handle subtract 1000 instead of adding to get the right value.
That handles the possible values for formula for FIELD-1 and I guess you need similar IFTHEN statements for your Field-3 formula
fab wrote:
Second question is, how can I insert your code to round up numbers after these two calculation? (you can see I commented the ZD to use again SFF, but I can find it working only in this way)
I am not sure as to whether you want to divide by 10/100/1000. Either way it depends on how many decimal digits you want and whether you want to perform the rounding or it.
I am guessing that you need to perform the same level of rounding for FIELD-3 calculation too. So just repeat the same something like this
Joined: 26 Nov 2002 Posts: 12358 Topics: 75 Location: San Jose
Posted: Wed Nov 23, 2016 10:54 am Post subject:
Fab,
On second thoughts I have decided to eliminate all the Repetitive IFTHEN statements and perform with a single IFTHEN.
Basically we have 2 formula's to calculate and then apply the edit mask based on the indicator.
So we by default calculate the 2 formula values and store as temp variables and then checking the indicator we overlay one of the temp variable with the other. By doing so we have 1 temp variable that we need to apply the edit mask.
Right after we determine the quarter constant, we also calculate the formula values.
At position 160 I calculate the Field-1 formula and save it as 24 byte zd field. (indicator = reduced)
At position 190 I calculate the Field-3 formula and save it as 24 byte zd field. ( indicator = new or something else)
Now I have a IFTHEN statement to Check if it Not equal to Reduced and then simply overlay the contents at 190 onto position 160.
By doing this we have single value that we need to validate to apply the rounding and apply the edit mask.
Now the decimals are at position 181 that will validate and round it and apply the edit mask.
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