Page 1 of 1

Sum fields for comparing two fields

PostPosted: Wed Feb 01, 2017 3:29 pm
by Sanjana
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.

Re: Sum fields for comparing two fields

PostPosted: Wed Feb 01, 2017 3:45 pm
by Sanjana
sorry,this is my sample output:

dept j-count p-count

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

Re: Sum fields for comparing two fields

PostPosted: Wed Feb 01, 2017 3:52 pm
by BillyBoyo
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.

Re: Sum fields for comparing two fields

PostPosted: Thu Feb 02, 2017 11:48 am
by Sanjana
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

Re: Sum fields for comparing two fields

PostPosted: Thu Feb 02, 2017 12:09 pm
by Aki88
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.

Re: Sum fields for comparing two fields

PostPosted: Mon Feb 06, 2017 4:49 pm
by Sanjana
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?

Re: Sum fields for comparing two fields

PostPosted: Mon Feb 06, 2017 4:59 pm
by Aki88
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.

Re: Sum fields for comparing two fields

PostPosted: Mon Feb 13, 2017 7:19 pm
by Sanjana
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?

Re: Sum fields for comparing two fields

PostPosted: Tue Feb 14, 2017 1:21 am
by BillyBoyo
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.