Sum fields for comparing two fields



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

Sum fields for comparing two fields

Postby Sanjana » Wed Feb 01, 2017 3:29 pm

Hello i have an input file like below:
AB1 j
AB1 j
AHA j
AHA p
AHA j
AHA j
AHA j
AHA j
AHA j
AHB j
AHB p
AJR p
AJR j
AJR j
AJR j
AJR j

my output needs to look like below:

dept j-count p-count

AB1 2 0
AHA 6 1
AHB 1 1
AJR 1 4


While i can create the layout using header1,my doubt is if i can use sum fields to achieve the result? If yes,then can anyone tell me how i can compare two fields using sum.
Thank You.
Sanjana
 
Posts: 11
Joined: Sat Dec 10, 2016 11:42 am
Has thanked: 0 time
Been thanked: 0 time

Re: Sum fields for comparing two fields

Postby Sanjana » Wed Feb 01, 2017 3:45 pm

sorry,this is my sample output:

dept j-count p-count

AB1 2 0
AHA 6 1
AHB 1 1
AJR 4 1
Sanjana
 
Posts: 11
Joined: Sat Dec 10, 2016 11:42 am
Has thanked: 0 time
Been thanked: 0 time

Re: Sum fields for comparing two fields

Postby BillyBoyo » Wed Feb 01, 2017 3:52 pm

You would use OUTFIL reporting features, REMOVECC, NODETAIL and SECTIONS with TRAILER3 and TOT/TOTAL.

Use INREC to create two fields which you set to either 0 or 1 depending on whether a p or a j. (so if a j, p-field will be zero, j-field will be one, and vice versa). Those are the two fields you use TOT/TOTAL on.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: Sum fields for comparing two fields

Postby Sanjana » Thu Feb 02, 2017 11:48 am

in my sample input i have given only 4 unique values but in reality there are hundreds of values.
i could easily use:

    //SYSIN DD *                                                          
          INREC IFTHEN=(WHEN=(1,3,CH,EQ,C'AB1',AND,5,1,CH,EQ,C'J'),  
                       BUILD=(1:C'AB1',5:C'J-COUNT))                        

Can someone tell me an easier way to do it
Sanjana
 
Posts: 11
Joined: Sat Dec 10, 2016 11:42 am
Has thanked: 0 time
Been thanked: 0 time

Re: Sum fields for comparing two fields

Postby Aki88 » Thu Feb 02, 2017 12:09 pm

Hello,

What BillyBoyo meant was an INREC similar to below code. 4 unique values or more, is impertinent here; the values that matter are the 'p' and 'j' combinations.

Please note that I've taken liberty with the OVERLAY columns:


 INREC IFTHEN=(WHEN=(5,1,CH,EQ,C'p'),    
              OVERLAY=(10:C'1',12:C'0')),
       IFTHEN=(WHEN=(5,1,CH,EQ,C'j'),    
              OVERLAY=(10:C'0',12:C'1'))
 


I'll leave the SECTIONS code for you to explore, it is relatively simple. Just go by what was posted and refer the DFSORT programming guide, you'll have everything you need to work it out. All the heavy-lifting of control break for various items will be taken care of by DFSORT itself.
Aki88
 
Posts: 381
Joined: Tue Jan 28, 2014 1:52 pm
Has thanked: 33 times
Been thanked: 36 times

Re: Sum fields for comparing two fields

Postby Sanjana » Mon Feb 06, 2017 4:49 pm

Using the above code i can get the total j count and p count. But i want the counts based on the dept name. How is that possible?
Sanjana
 
Posts: 11
Joined: Sat Dec 10, 2016 11:42 am
Has thanked: 0 time
Been thanked: 0 time

Re: Sum fields for comparing two fields

Postby Aki88 » Mon Feb 06, 2017 4:59 pm

Hello,

Sanjana wrote:Using the above code i can get the total j count and p count. But i want the counts based on the dept name. How is that possible?


I hope you read my complete post, relevant portions are highlighted:

Aki88 wrote:Hello,

What BillyBoyo meant was an INREC similar to below code. 4 unique values or more, is impertinent here; the values that matter are the 'p' and 'j' combinations.

Please note that I've taken liberty with the OVERLAY columns:


 INREC IFTHEN=(WHEN=(5,1,CH,EQ,C'p'),    
              OVERLAY=(10:C'1',12:C'0')),
       IFTHEN=(WHEN=(5,1,CH,EQ,C'j'),    
              OVERLAY=(10:C'0',12:C'1'))
 


I'll leave the SECTIONS code for you to explore, it is relatively simple. Just go by what was posted and refer the DFSORT programming guide, you'll have everything you need to work it out. All the heavy-lifting of control break for various items will be taken care of by DFSORT itself.


In simple words, you'll have to couple the above code with SECTIONS to get the final result.
Aki88
 
Posts: 381
Joined: Tue Jan 28, 2014 1:52 pm
Has thanked: 33 times
Been thanked: 36 times

Re: Sum fields for comparing two fields

Postby Sanjana » Mon Feb 13, 2017 7:19 pm

Hello,

OUTFIL NODETAIL,                                        
HEADER2=(1:'REPORT 1',/,                
         1:'DEPT',6:'J-COUNT',14:'P-COUNT'),
SECTIONS=(1,5,                                          
TRAILER3=(                                              
          IFTHEN=(WHEN=(5,1,CH,EQ,C'P'),                
            1,5,6:TOT=(10,1,BI,EDIT=(IIIIT))),            
          IFTHEN=(WHEN=(5,1,CH,EQ,C'J'),                
            1,5,14:TOT=(12,1,BI,EDIT=(IIIIT))))),          
                 
 

Could someone point out to me the error in outfil syntax pls. Am i going in the right direction?
Sanjana
 
Posts: 11
Joined: Sat Dec 10, 2016 11:42 am
Has thanked: 0 time
Been thanked: 0 time

Re: Sum fields for comparing two fields

Postby BillyBoyo » Tue Feb 14, 2017 1:21 am

Well, you can only continue (filling up the line and continuing is a fool's game) after a comma or a colon. You can't leave that opening bracket like that.

You can only use IFTHEN within INREC, OUTREC and OUTFIL. You can't use them within TRAILER3 or anywhere else.
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