Sum of same fields in same record



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

Sum of same fields in same record

Postby pgkkreddy1 » Mon Jun 01, 2015 10:40 pm

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.
pgkkreddy1
 
Posts: 24
Joined: Wed May 13, 2015 8:08 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Sum of same fields in same record

Postby pgkkreddy1 » Mon Jun 01, 2015 10:56 pm

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
pgkkreddy1
 
Posts: 24
Joined: Wed May 13, 2015 8:08 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Sum of same fields in same record

Postby BillyBoyo » Mon Jun 01, 2015 11:43 pm

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?
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: Sum of same fields in same record

Postby pgkkreddy1 » Tue Jun 02, 2015 5:57 am

Thanks Billy.sorry i will paste the data correctly..

no need to rounded it up..just i need 18.1833

Thank you
pgkkreddy1
 
Posts: 24
Joined: Wed May 13, 2015 8:08 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Sum of same fields in same record

Postby BillyBoyo » Tue Jun 02, 2015 12:12 pm

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.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: Sum of same fields in same record

Postby pgkkreddy1 » Tue Jun 02, 2015 6:41 pm

Thank you Billy i will do.
pgkkreddy1
 
Posts: 24
Joined: Wed May 13, 2015 8:08 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Sum of same fields in same record

Postby pgkkreddy1 » Wed Jun 03, 2015 11:40 am

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
pgkkreddy1
 
Posts: 24
Joined: Wed May 13, 2015 8:08 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Sum of same fields in same record

Postby BillyBoyo » Wed Jun 03, 2015 12:51 pm

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.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post