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

Postby mainframe_novice » Tue Apr 27, 2010 1:39 am

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

 

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

Postby dick scherrer » Tue Apr 27, 2010 1:42 am

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.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

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

Postby mainframe_novice » Tue Apr 27, 2010 1:49 am

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

Postby Frank Yaeger » Tue Apr 27, 2010 2:22 am

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
User avatar
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

Postby mainframe_novice » Tue Apr 27, 2010 2:38 am

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

Postby Frank Yaeger » Tue Apr 27, 2010 3:32 am

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
User avatar
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

Postby dick scherrer » Tue Apr 27, 2010 3:46 am

Hello,

SALES-NUM is actually a date in original file .
And why was this not initially mentioned?

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.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

Re: Find Most current positive with DFSORT/ICETOOL

Postby Frank Yaeger » Tue Apr 27, 2010 5:11 am

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
User avatar
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

Postby mainframe_novice » Wed Apr 28, 2010 4:03 am

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

Postby Frank Yaeger » Wed Apr 28, 2010 4:59 am

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
User avatar
Frank Yaeger
Global moderator
 
Posts: 1080
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 14 times


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post