View previous topic :: View next topic
|
Author |
Message |
kalidasvs
New User
Joined: 08 Feb 2018 Posts: 11 Location: US
|
|
|
|
In my file I have following records:
AAA10
AAA10
AAA10
AAA12
AAA12
AAA11
AAA11
BBB20
BBB20
BBB21
BBB21
BBB22
BBB22
I need the output as follows:
AAA12
AAA12
BBB22
BBB22
What SORT parameters I can use to get the desired output? |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3048 Location: NYC,USA
|
|
|
|
lol. just use INCLUDE COND = ( "whatever you want") |
|
Back to top |
|
|
kalidasvs
New User
Joined: 08 Feb 2018 Posts: 11 Location: US
|
|
|
|
But I have about 70,000 records in my input file. AAA and BBB are 12 digit numbers. |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3048 Location: NYC,USA
|
|
|
|
Quote: |
But I have about 70,000 records in my input file. AAA and BBB are 12 digit numbers. |
Please you need to understand the rules first about what you want exactly.
You need to present the real scenario examples not AAA and BBB or give us piece meal information.
whatever you want as output- tell us how to identify them
or whatever you don't want as output- tell us how to identify them |
|
Back to top |
|
|
kalidasvs
New User
Joined: 08 Feb 2018 Posts: 11 Location: US
|
|
|
|
My file has total of 70000 records and record length is about 1000 bytes. Document number is a key field which is 14 digits. The last two digits are usually 00 (two zeros). Each document number could have multiple records. In some situations, there could be an adjustment to the document. In this case the last two digits could be 01, 02 and so on.
So if a document has two adjustments, the document numbers could be as below. I'm showing space preceding the last two digits for display purpose only.
128365290305 00
128365290305 00
128365290305 01
128365290305 01
128365290305 02
128365290305 02
When I use the below Sort, I only get one record for '128365290305 02'. But I need both.
SORT FIELDS=(1,12,CH,D)
SUM FIELDS=NONE
Hope I provided enough details. |
|
Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008 Posts: 2010 Location: USA
|
|
|
|
The first step (as usually!) - you need to understand the logic, or algorithm, or method, or whatever: WHAT AND HOW YOU NEED TO DO TO GET THE DESIRED RESULT.
The second step is, coding the already known algorithm for any desired tool - either SORT utilities, or whatever else.
The computer experts graduated approx. after year 2000 have never heard about the first step. They need some "buttons to click" just to get the result without thinking about it.
* * * * * * * *
Two SORT-related operations are required.
1) Get the list of MAX(adjustment_number) for each unique document_ID
2) Select (via JOIN operation) only those records from the original file with both matching fields of the list created above, e.g. document_ID, and max_adjustment_number
So, step #1 is done.
I suggest the author to complete at least step #2 by himself. |
|
Back to top |
|
|
kalidasvs
New User
Joined: 08 Feb 2018 Posts: 11 Location: US
|
|
|
|
Well I started my career with Mainframe in mid-90s but not everyone gets to explore further into some subjects. Thanks for the suggestion to do a join to get the result but I wanted to know if it's achievable through Syncsort. Thanks again. |
|
Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
|
|
|
|
'Join' is a process within Syncsort.
And if you wanted a Syncsort answer why post in DFsort? (Moving topic). |
|
Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008 Posts: 2010 Location: USA
|
|
|
|
kalidasvs wrote: |
Well I started my career with Mainframe in mid-90s but not everyone gets to explore further into some subjects. Thanks for the suggestion to do a join to get the result but I wanted to know if it's achievable through Syncsort. Thanks again. |
Main point is: designing an algorithm is not related neither to computer platform, nor to specific programming language, nor to particular utility, etc.
It must be a part of design (in simple cases maybe in one's mind only) before any coding begins. |
|
Back to top |
|
|
kalidasvs
New User
Joined: 08 Feb 2018 Posts: 11 Location: US
|
|
|
|
Nic Clouston wrote: |
'Join' is a process within Syncsort.
And if you wanted a Syncsort answer why post in DFsort? (Moving topic). |
Sure, this topic need to be moved to Syncsort. |
|
Back to top |
|
|
kalidasvs
New User
Joined: 08 Feb 2018 Posts: 11 Location: US
|
|
|
|
sergeyken wrote: |
kalidasvs wrote: |
Well I started my career with Mainframe in mid-90s but not everyone gets to explore further into some subjects. Thanks for the suggestion to do a join to get the result but I wanted to know if it's achievable through Syncsort. Thanks again. |
Main point is: designing an algorithm is not related neither to computer platform, nor to specific programming language, nor to particular utility, etc.
It must be a part of design (in simple cases maybe in one's mind only) before any coding begins. |
I have other approach, where I create a VSAM file with document number of highest adjustments. Then a COBOL program extracts the records. This is not for production, but for off side data extract. |
|
Back to top |
|
|
kalidasvs
New User
Joined: 08 Feb 2018 Posts: 11 Location: US
|
|
|
|
I want to thank everyone for your time, I would like to close this thread. |
|
Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
|
|
|
|
kalidasvs wrote: |
have other approach, where I create a VSAM file with document number of highest adjustments. Then a COBOL program extracts the records. This is not for production, but for off side data extract. |
I don't have Syncsort. But if you're not concerned about the output order, you could consider something like this (assumed your original sample input data provided in the SORTIN DD).
Code: |
//SYSIN DD *
SORT FIELDS=(1,5,CH,D)
OUTREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,5),PUSH=(81:SEQ=8)),
IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,3),PUSH=(89:SEQ=8))
OUTFIL INCLUDE=(81,8,ZD,EQ,89,8,ZD),BUILD=(1,80) |
|
|
Back to top |
|
|
kalidasvs
New User
Joined: 08 Feb 2018 Posts: 11 Location: US
|
|
|
|
Arun Raj wrote: |
kalidasvs wrote: |
have other approach, where I create a VSAM file with document number of highest adjustments. Then a COBOL program extracts the records. This is not for production, but for off side data extract. |
I don't have Syncsort. But if you're not concerned about the output order, you could consider something like this (assumed your original sample input).
Code: |
//SYSIN DD *
SORT FIELDS=(1,5,CH,D)
OUTREC IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,5),PUSH=(81:SEQ=8)),
IFTHEN=(WHEN=GROUP,KEYBEGIN=(1,3),PUSH=(89:SEQ=8))
OUTFIL INCLUDE=(81,8,ZD,EQ,89,8,ZD),BUILD=(1,80) |
|
Thanks, I'll review your solution! I did try one method (suggested in this thread by user sergeyken) which worked on a set of small data. I'm yet to try on full data. Here File-1 is file of unique keys of Document number with highest adjustment. File-2 is the actual file.
//SYSIN DD *
JOINKEYS FILE=F1,FIELDS=(1,8,A)
JOINKEYS FILE=F2,FIELDS=(1,8,A)
REFORMAT FIELDS=(F2:1,20)
SORT FIELDS=COPY
END |
|
Back to top |
|
|
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015 Posts: 1222 Location: Bamberg, Germany
|
|
|
|
You could just sort by the ID ascending, adjustment descending. When the key ID changes PUSH the record and compare all the other records against the pushed value. Include only equal ones, simple as that.
Using JOIN is more than overkill and with your solution needs an extra step to separate the highest adjustments for each key. |
|
Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008 Posts: 2010 Location: USA
|
|
|
|
Joerg.Findeisen wrote: |
You could just sort by the ID ascending, adjustment descending. When the key ID changes PUSH the record and compare all the other records against the pushed value. Include only equal ones, simple as that.
Using JOIN is more than overkill and with your solution needs an extra step to separate the highest adjustments for each key. |
Yes, both solutions (either with PUSH, or with JOIN) use the same logical algorithm implemented in two different ways (and there are more of them). Both types of code have been presented in previous postings. |
|
Back to top |
|
|
|