## 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

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

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

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

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

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

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

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

Hello,

OUTFIL NODETAIL,
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

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