Page 1 of 1

Complex sort logic

Posted: 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.

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

Regards,
Manas

Re: Complex sort logic

Posted: 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.

Re: Complex sort logic

Posted: 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

Re: Complex sort logic

Posted: 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?

Re: Complex sort logic

Posted: Sun Dec 11, 2016 11:38 pm
Is there any way to calculate 4th one(percentage) in sort card ?

Re: Complex sort logic

Posted: Mon Dec 12, 2016 12:22 am
Yes, but if you don't give any information...

Re: Complex sort logic

Posted: 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 ?

Re: Complex sort logic

Posted: 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.