## Find Most current positive with DFSORT/ICETOOL

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

### Find Most current positive with DFSORT/ICETOOL

Is the following possible through DFSORT/ICETOOL ?

Input
------------------------------------------------------
SALES-NUM     | SALESMAN-ID       |  PROFIT
------------------------------------------------------
1             |    1              |+500
2             |    1              |+300
3             |    1              |-700
1             |    2              |-200
2             |    2              |-300
1             |    3              |-200
2             |    3              |+400

Output
-----------------------------------------------------
SALES-NUM     |  SALESMAN-ID     |PROFIT
-----------------------------------------------------
2             | 1                | 300
2             | 2                |0
2             |3                 | 400

What it does is
- Fetches one row for each salesman
- Profit should be most current positive numeric or zero .
mainframe_novice

Posts: 35
Joined: Tue Apr 27, 2010 1:29 am
Has thanked: 0 time
Been thanked: 0 time

### Re: Find Most current positive with DFSORT/ICETOOL/SYNCSORT

Hello and welcome to the forum,

What does "current" mean?

The sort doesn't "fetch" - it reads all of the records and acts on them depending on the code. . .
Hope this helps,
d.sch.

dick scherrer
Global moderator

Posts: 6308
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 90 times

### Re: Find Most current positive with DFSORT/ICETOOL/SYNCSORT

Thanks !

'Most current' refers to the largest sales-num for each sales represantative .

What I need is the output should display the profit for each salesman for the largest sales-num .
But if the profit associated with largest sales-num is negative then look for any positive profit for that salesman .
If no positive profit found display 0 .
mainframe_novice

Posts: 35
Joined: Tue Apr 27, 2010 1:29 am
Has thanked: 0 time
Been thanked: 0 time

### Re: Find Most current positive with DFSORT/ICETOOL

There's a lot of missing/confusing information here.

What is the starting position, length and format of each field?

Does the Sales-Num field really only have 1 digit? If not, what does it really look like?

Does the Salesman-Id field really only have 1 digit? If not, what does it really look like?

But if the profit associated with largest sales-num is negative then look for any positive profit for that salesman .

Well, Sales-Num 1 for Sales-Id 1 has +500 profit, so that's "any positive profit" but you show the +300 profit instead. Is the rule really that you want a positive profit for the highest Sales-Num or something else? Explain your rules more completely.

Also, give a more extensive example of input and expected output with real/more values.
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort

Frank Yaeger
Global moderator

Posts: 1080
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 14 times

### Re: Find Most current positive with DFSORT/ICETOOL

SALES-NUM is actually a date in original file .So let's call it Sales-Date .
Salesman-Id is 6 char long .
profit is 11,2 which can be positive or negative .
File is FB .
Output should show the the most current positive profit for each saleman or zero if none of the profits is positive .

Input
------------------------------------------------------
SALES-DATE | SALESMAN-ID | PROFIT
------------------------------------------------------
091231 | BXC101 |+500
100131 | BXC101 |+300
100228 | BXC101 |-700
100131 | BXC102 |-200
100228 | BXC102 |-300
100131 | BXC103 |-200
100228 | BXC103 |+400

Output
------------------------------------------------------
SALES-DATE | SALESMAN-ID | PROFIT
------------------------------------------------------
100131 | BXC101 |+300
100228 | BXC102 |0
100228 | BXC103 |+400

Date Format for Sales- Date is yymmdd
mainframe_novice

Posts: 35
Joined: Tue Apr 27, 2010 1:29 am
Has thanked: 0 time
Been thanked: 0 time

### Re: Find Most current positive with DFSORT/ICETOOL

Well, you answered some of my questions.

What is the starting position, length and format of each field?

profit is 11,2 which can be positive or negative .

How can +500 fit into 2 bytes - is it a packed decimal (PD) field or a fixed-point (FI) field or what?

Also, if the first two fields are each 6 bytes long, now can the third field start at position 11?

Again, what is the starting position, length and format of EACH field?

Well, Sales-Num 1 for Sales-Id 1 has +500 profit, so that's "any positive profit" but you show the +300 profit instead. Is the rule really that you want a positive profit for the highest Sales-Num or something else? Explain your rules more completely.

???

Also, give a more extensive example of input and expected output with real/more values.

???

I'm happy to help, but I don't see any reason why I should have to guess at what you want.
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort

Frank Yaeger
Global moderator

Posts: 1080
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 14 times

### Re: Find Most current positive with DFSORT/ICETOOL

Hello,

SALES-NUM is actually a date in original file .

Indeed, why was the entire topic done so far off the mark?

Just because there are people who are happy to help, is no reason to be so incomplete/inconsistent. If you are unwilling to put the least amount of effort into posting what you want, why should others waste time they could use far more productively (i.e. doing their own work, helping others, etc)?
Hope this helps,
d.sch.

dick scherrer
Global moderator

Posts: 6308
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 90 times

### Re: Find Most current positive with DFSORT/ICETOOL

mainframe_novice,

Since it isn't clear what your fields actually look like, I'll show you how to do this with my own definition for the fields. Hopefully, you can adapt it to your real definition of the fields. Let's say the input fields are:

091231         | BXC101            |+500
100131         | BXC101            |+300
100228         | BXC101            |-700
100131         | BXC102            |-200
100228         | BXC102            |-300
100131         | BXC103            |-200
100228         | BXC103            |+400

Here's a DFSORT/ICETOOL job that will give the results you asked for:

//S1    EXEC  PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG  DD SYSOUT=*
//IN DD DSN=...  input file (FB/40)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//T2 DD DSN=&&T2,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//OUT DD DSN=...  output file (FB/40)
//TOOLIN DD *
SELECT FROM(IN) TO(T1) ON(18,6,CH) FIRST USING(CTL1)
SELECT FROM(IN) TO(T2) ON(18,6,CH) FIRST USING(CTL2)
COPY JKFROM TO(OUT) USING(CTL3)
//CTL1CNTL DD *
SORT FIELDS=(18,6,CH,A,1,6,CH,D)
/*
//CTL2CNTL DD *
OMIT COND=(37,4,SFF,LE,0)
SORT FIELDS=(18,6,CH,A,1,6,CH,D)
/*
//CTL3CNTL DD *
JOINKEYS F1=T1,FIELDS=(18,6,A),SORTED
JOINKEYS F2=T2,FIELDS=(18,6,A),SORTED
JOIN UNPAIRED,F1
REFORMAT FIELDS=(?,F2:1,40,F1:1,36)
OUTFIL FNAMES=OUT,INCLUDE=(1,1,SS,EQ,C'B1'),
IFTHEN=(WHEN=(1,1,CH,EQ,C'B'),BUILD=(2,40)),
IFTHEN=(WHEN=NONE,BUILD=(42,36,C'+000'))
/*

OUT would have:

100131         | BXC101            |+300
100228         | BXC102            |+000
100228         | BXC103            |+400

Note that you will need the Nov, 2009 DFSORT PTF to use JOINKEYS. If you don't have this PTF installed, ask your System Programmer to install it. For more details on this PTF, see:

http://www.ibm.com/support/docview.wss? ... g3T7000174
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort

Frank Yaeger
Global moderator

Posts: 1080
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 14 times

### Re: Find Most current positive with DFSORT/ICETOOL

Thanks Frank .Appreciate your help !
It worked .
mainframe_novice

Posts: 35
Joined: Tue Apr 27, 2010 1:29 am
Has thanked: 0 time
Been thanked: 0 time

### Re: Find Most current positive with DFSORT/ICETOOL

Excellent. Thanks for the feedback.
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort

Frank Yaeger
Global moderator

Posts: 1080
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 14 times