I have counted the records which are having 1st & 3rd fields are same . But i couldn't do it for the records which doesn't have 3rd records at the same column because of 2nd field decimal part . So for that i need to add trailing zeros in second field so that all the 3rd fields come at the same column .
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
OK. Your data is already in order, so we don't want to use SORT and SUM (waste of resources).
So use MERGE and SUM. You can use MERGE with a single input file (SORTIN01 instead of SORTIN) and then use SUM.
This allows you to use INCLUDE=/OMIT= in OUTFIL and do the entire processing in one step.
You will need to rearrange your input data, because the second part of your key is not in a fixed position and because you need a value of one on the record to get a count of the keys out of the SUM.
Variable-position data means PARSE. If your input records are fixed-length, extend at the end of the record for the second part of the key and the count of one (the count field needs to be big enough to hold the maximum possible value of the count for a group of records.
So:
INREC with two IFTHEN=(WHEN=INIT to PARSE (add new field) and have a count of one (new field).
MERGE FIELDS=(original-major-key,new-minor-key)
SUM FIELDS=(your-new-value-of-one)
(1,1,CH) (9,1,CH) VALUE COUNT
A , 1
A A 2
A B 1
B B 1
B C 1
C A 1
C C 1
Now i have to put condition on VALUE COUNT if it is greater than 1 then only print the following record in output file . Also i need to add trailing 0 into the decimal part of second field so that i can get the third field at the same column and put the exact column in ON clause.[/img][/i]
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
If you want to use ICETOOL (which is fine) look at SELECT, not OCCUR.
You'll need a USING(xxxx) and in that xxxxCNTL file you'll need an INREC with the PARSE. You won't need the count-of-one because SELECT is doing what you want.
If using SELECT, remember to turn off the SORT (SORT FLELDS=NONE or OPTION COPY).
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
Your new topic seems to be connected to this, and has been locked.
According to your sample output, you don't even need the field with decimals. Find the third field using PARSE, you can even overwrite the decimal value (according to your output) with the second part of the key from the PARSE.
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
I've outlined one with, with the MERGE and SUM of a generated field containing the value one.
Meanwhile, you started moving towards ICETOOL. If you use a generated sequence number from WHEN=GROUP (where the group is the same as your key) in a USING(xxxx) with the SELECT of the last record of a key, you should then be able to use OUTFIL OMIT=/INCLUDE= for your selection.
K Bill , I will try to do with WHEN=GROUP , Could you please tell me from where i can get the details about WHEN=GROUP since i have never used it before.
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
Lots of examples here, there is a DFSORT Getting Started Guide Application Programming Guide, both available for free use, plus when you get the hang of it look at the Smart DFSORT tricks publication.
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
You can have multiple functions on INREC, OUTREC and OUTFIL by specifying multiple IFTHEN=(WHEN=. In your case, add IFTHEN=(WHEN=INIT to do your BUILD, before your WHEN=GROUP.
From the second post of Bill I see that, He asked for data to be in order record to record and you said it is sorted, then why did in the above example it is not sorted based on 1st and 3rd field?