Page 1 of 1
Sum of same fields in same record
Posted:
Mon Jun 01, 2015 10:40 pm
by pgkkreddy1
Hi,
Good evening!
Please help me out regarding below data
INPUT:Record length 28
emp id field 1 ind1 field 2 ind2 field 3 ind3 field 4 ind4
1234 10.20 Y 30.21 Y N 13.00 Y
1235 10.20 Y 30.21 Y 12.3 Y 13.00 Y
1236 10.20 Y 30.21 Y 2.1 Y 13.00 Y
1237 N 30.21 Y 11.34 N 13.00 Y
I want output should be like below using sort.
emp id field 1 ind1 field 2 ind2 field 3 ind3 field 4 ind4 TOTqty tot ind avg
1234 10.20 Y 30.21 Y N 13.00 Y 53.41 3(y+y+y) 17.80
1235 10.20 Y 30.21 Y 12.3 Y 13.00 Y 65.71 4(y+y+y+y) 16.4275 (if possible rounded to .43)
1236 10.20 Y 30.21 Y 2.1 Y N 42.51 3(y+y+y) 14.17
1237 N 30.21 Y 11.34 N 13.00 Y 54.55 3(y+y+y) 18.18333(if possible rounded to .19)
Please help me out with syntax.
i used
SYSIN DD *
INREC OVERLAY=(29:5,5,add,11,5,add,18,5,add,24,5,ZD,TO=ZD,LENGTH=8
I AM NOT SURE FOR IND SUM AND AVERAGE.Please Help me out.
Thank you.
Re: Sum of same fields in same record
Posted:
Mon Jun 01, 2015 10:56 pm
by pgkkreddy1
Sorry.Data is not alligned properly
here is correct data format.
000100 empid field1 ind1 field2 ind2 field3 ind3 field4 ind4
000200 1234 10.20 Y 30.21 Y N 13.00 Y
000300 1235 10.20 Y 30.21 Y 12.3 Y 13.00 Y
000400 1236 10.20 Y 30.21 Y 2.1 Y 13.00 Y
000500 1237 N 30.21 Y 11.34 N 13.00 Y
I want output should be like below using sort.
emp id field 1 ind1 field 2 ind2 field 3 ind3 field 4 ind4 TOTqty totind avg
1234 10.20 Y 30.21 Y N 13.00 Y 53.41 3(y+y+y) 17.80
1235 10.20 Y 30.21 Y 12.3 Y 13.00 Y 65.71 4(y+y+y+y) 16.4275 (if possible rounded to16 .43)
1236 10.20 Y 30.21 Y 2.1 Y N 42.51 3(y+y+y) 14.17
1237 N 30.21 Y 11.34 N 13.00 Y 54.55 3(y+y+y) 18.18333(if possible rounded 18.19)
Thank you!
Code'd
Re: Sum of same fields in same record
Posted:
Mon Jun 01, 2015 11:43 pm
by BillyBoyo
What you need to do to line things up is to use the Code tags (highlight the lump of text, click on the Code button) and the Preview button, so you know what your post will look like before you use Submit.
Why would you want 18.18333 rounded to 18.19?
Re: Sum of same fields in same record
Posted:
Tue Jun 02, 2015 5:57 am
by pgkkreddy1
Thanks Billy.sorry i will paste the data correctly..
no need to rounded it up..just i need 18.1833
Thank you
Re: Sum of same fields in same record
Posted:
Tue Jun 02, 2015 12:12 pm
by BillyBoyo
DEFSORT supports the likely arithmetic functions, including addition and division.
How it works is documented, if you are new to DFSORT you can start with the Getting Started manual, and the Application Programming Guide will give you full information about syntax and operation of everything available.
How this will work for your case is to first define the "source" for the calculation, start-position,length,type (or a constant value). The type must be valid for arithmetic. Then the function you want applied to the source (ADD) and what you want applied to the source (the second field). DFSORT will keep track of the information about the result, so you can do another ADD, then a DIVide.
Once you have your final result, DFSORT will store it at the original position which would have been occupied by your first source field, but with the length that you specify, and the type you specify (if you do) or with default values (always an unclear way to do it). You have TO= and LENGTH= and EDIT= available, depending on what you want to do with the result.
DFSORT does not do rounding, if you want that, you have to do it yourself afterwards.
DFSORT does not know about decimal places. Everything is treated as whole-numbers. This won't matter unless you are dealing with fields with different numbers of decimal places, when you will have to adjust before, or after, depending on the function used.
Two problems that you have are that you want a count, and the number of fields to add are variable. I'd suggest you get the basic thing working first, then we can move on to addressing those issues.
Re: Sum of same fields in same record
Posted:
Tue Jun 02, 2015 6:41 pm
by pgkkreddy1
Thank you Billy i will do.
Re: Sum of same fields in same record
Posted:
Wed Jun 03, 2015 11:40 am
by pgkkreddy1
Hi ,
Good morning!
can you please help me out regarding below data.
My input is:
000001 EMPID FLD1 IND1 FLD2 IND2 FLD3 IND3 FIL4 IND4
000002 1231 10.20 1 30.21 1 13.00 1 53.41 1
000003 1232 0 30.21 1 09.33 1 08.11 1
000004 1233 12.33 1 11.33 1 09.33 1 0
000005 1234 12.33 1 11.33 1 0 08.11 1
000006 1235 12.33 1 0 09.33 1 1
I want output data should be like below
000001 EMPID FLD1 IND1 FLD2 IND2 FLD3 IND3 FIL4 IND4 FLDT INDT AVG
000002 1231 10.20 1 30.21 1 13.00 1 53.41 1 106.82 4 57.41
000003 1232 0 30.21 1 09.33 1 08.11 1 47.65 3 15.88
000004 1233 12.33 1 11.33 1 09.33 1 0 32.99 3 10.99
000005 1234 12.33 1 11.33 1 0 08.11 1 31.77 3 10.59
000006 1235 12.33 1 0 09.33 1 1 21.66 2 10.83
For adding the fld1+fld2+fld3+4 i used below syntax i got soc7 error.
these fields are declared as S9(10)V9(4) .
I used below synatax.
//SYSIN DD *
SORT FIELDS=(1,4,CH,A)
OUTREC OVERLAY=(50:6,5,ZD,ADD,14,5,ZD,ADD,22,5,ZD,ADD,
30,5,ZD,ZD,LENGTH=5)
Please help me out to resolve this[/code]
Thank you!
kk
Re: Sum of same fields in same record
Posted:
Wed Jun 03, 2015 12:51 pm
by BillyBoyo
Thanks for the formatting.
Sorry, I didn't notice the decimal-points earlier. Instead of ZD, use UFF which is Unsigned Free-Format. This will ignore any non-numerics in your field, so concatenating and right-justifying all digits so the data can be used as a number.