Complex sort logic



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

Complex sort logic

Postby Manas » Sat Dec 10, 2016 5:33 pm

All,

I have a requirement which I googled and tried my best to solve. still I am in mid-way.

I have a input file with date field and two more packed decimal fields. Then I have to separate out records with date field GE to 01/01/2016.
In the output file I want all matching records and one more trailer record below with 4 fields.
1. Count of records matching date criteria
2. Sum of 1st packed decimal field
3. Sum of 2nd packed decimal field
4. (Sum of 1st field ×100)/sum of 2nd field.

What I have done as of now:
I have successfully created an output file with all matching records and one trailer record with first 3 fields.I am not able to calculate and populate 4th field in my output file using sort.

Can anyone please advise on this?

it would be a great help. Thanks for your assistance in advance.

Regards,
Manas
Manas
 
Posts: 4
Joined: Sat Dec 10, 2016 5:19 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Complex sort logic

Postby BillyBoyo » Sat Dec 10, 2016 5:39 pm

You need to show your code, some representative sample input, expected output and what you get from your code.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: Complex sort logic

Postby Manas » Sun Dec 11, 2016 12:30 pm

This is the sort card I have used.
SORT FIELDS=COPY                                                                
OUTFIL FILES=01,                                                  
INCLUDE=(69,10,CH,GE,C'2016/01/01',AND,                            
         79,10,CH,EQ,C'          '),                              
REMOVECC,                                                          
TRAILER1=(1:'T',7:'XXXXXXXXX',                              
        32:TOT=(100,10,PD,EDIT=(STTTTTTTTTTTTTTT.TT),SIGNS=(,-,,)),
        64:TOT=(110,10,PD,EDIT=(STTTTTTTTTTTTTTT.TT),SIGNS=(,-,,)),
        86:'RECORD COUNT=',100:COUNT=(M10,LENGTH=10))              
OUTFIL FILES=02,                                                  
OMIT=(69,10,CH,GE,C'2016/01/01',AND,                              
      79,10,CH,EQ,C'          ')


I got count of records, sum of 1st field, sum of 2nd field in trailer record.

Regards,
Manas

Code'd
Manas
 
Posts: 4
Joined: Sat Dec 10, 2016 5:19 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Complex sort logic

Postby BillyBoyo » Sun Dec 11, 2016 3:36 pm

Well, one out of four, and that one not formatted...

You can't do calculations, or indeed any manipulations beyond what are defined in the documetation, on TRAILERn (or HEADERn).

Why are your values so huge? 19 digits?

How many records in your file?
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: Complex sort logic

Postby Manas » Sun Dec 11, 2016 11:38 pm

Is there any way to calculate 4th one(percentage) in sort card ?
Manas
 
Posts: 4
Joined: Sat Dec 10, 2016 5:19 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Complex sort logic

Postby BillyBoyo » Mon Dec 12, 2016 12:22 am

Yes, but if you don't give any information...
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: Complex sort logic

Postby Manas » Mon Dec 12, 2016 12:34 am

yes, there may be millions of records in that file, so values are so huge. Is there anything else you need ?
Manas
 
Posts: 4
Joined: Sat Dec 10, 2016 5:19 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Complex sort logic

Postby BillyBoyo » Mon Dec 12, 2016 1:55 am

Three basic approaches: don't do it (let someone/something else calculate it); calculate it when the file is next read (you don't want to go reading a huge file just to calculate one field); calculate it per record and TOT/TOTAL those, which needs care not to lose accuracy, and is a lot of work for one figure.

You could also consider creating the trailer on a separate OUTFIL, and treating the two data sets (date + trailer) as a logical one using data set concatenation next time it is read.

You could also consider a SORT EXIT to do the calculation (it would have to sum the values itself).

In short, it is a lot of resources to do it, vs not doing it at all (rely on a calculator) or doing it "next time" when the figures already exist.
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