I have divided the requirement into 3 parts. RQ#1, RQ#2 and RQ#3.
This thread is attached with the 2 files named "Input.txt" and "Merge Records - Example.xls".
Input.txt is the actual input in text format, whose columns are of fixed length as shown below.
Merge Records - Example.xls has 4 worksheets namely "Input", "RQ1 Output", "RQ2 Output" and "RQ3 Output".
The input file has some sample records whose structure is as follows: The file is attached in the form of notepad.
RQ#1: Sort the records in a user specific order for COL1.
Required order of COL1 is ** EVALUATIONE, POPULAR, CONTRIBUTION, TOSS and others if any **
RQ#2: Add COL11 together and concatenate COL12 values if all other COL's (COL1 to COL10) are identical.
While concatenating the trailing spaces for COL12 need to be elimited and COL12 length should be increased from 50 to 200.
RQ#3: Add a new row for every set of COL1, COL2 and COL3 and give a name to the set as 'TOTAL : ' SUM(COL11)
Also add an empty row after every TOTAL. This is for better readability of the report.
The string 'TOTAL' starts at position 110 and the SUM(COUNT) starts from position at 121.
What we want is sample input which accurately reflects the possibilities of your data, not all your data. And the expected output for that input, and the output you have received from what you have attempted, and the Control Cards which generated that output.
I'm sorry for the inconvenience caused with the insufficient information. I prepared the excel sheet with the example for each RQ specified above, but you removed them and suggested to provide only the sample input data.
RQ#2: Concatenation of COL12 means,
Let's consider the records from the input file
EVALUATIONE INCIRCLE RECEIVED AUMT AEN 4664 4664 TP6 20130306002074A
EVALUATIONE INCIRCLE RECEIVED AUMT AEN 4664 4664 TP6 20130306000478T
These two records looks same all the way from COL1 to COL10, but the COL12 has different values in them. Now, I like to merge those records in the following way.
EVALUATIONE INCIRCLE RECEIVED AUMT AEN 4664 4664 TP6 20130306002552A,T
Note: Values in COL12 'A' and 'T' are concatenated together with ',' as a seperator by removing the trailing spaces. Besides this the COL11 values 2074 and 478 are added and resulted 2552 in the merged record.
Well, you can leave everything in spreadsheets and full copies of your file, provide no descriptions of mappings from input to output, and do whatever you actually want. You'll just vastly reduce your chance of any useful replies. Up to you.
In addition to the other unanswered questions, how many records can be consolidated in the way you have described.
What are the RECFM and LRECL of your files? Is your final output for printing?
RECFM of both the input and output files are FB.
LRECL of Input file is 177
LRECL of Output file is 327.
how many records can be consolidated in the way you have described.
Yes, the actul length of COL12 is 50 characters. But, in reality the actual data in that column will be varies from 1 to 25 characters, rest of them are just spaces. So, to accommodate the concatenated values the length of the COL12 in the output file will be increased from 50 to 200.
In my example, I just mentioned only 2 identical records with different COL12 values. But In pracice, there might be more than two records, we need to consolidate/merge them into a single record.
I used DFSORT for your requirement. You can search for equivalent SYNCSORT command to achieve the result using SYNCSORT.
I really appreciate your enthusiasm to help but you need to understand DFSORT a little bit more to post solutions on behalf of DFSORT.
INREC is processed even before SORT happens, so your Job will NOT work if the data is NOT sorted. You already have the data sorted on the key (1 thru 127) and hence your job worked even though you are using INREC to number the duplicates. Jumble up the data and see how the job fails.
Well, there's still no answer to the "outsorting" for the first part of the requirement.
If you look at OUTFIL SECTIONS that should be able to give you the summed value of COL 11 (you'll need NODETAIL). You can have multiple "breaks", so you can do your COL1-3 total as well (it should come first).
The "consolidation" you can do with WHEN=GROUP, and a sequence number. Then a GROUP for for different sequence numbers to put the data into extended areas of the record, then what Sai has provided to "squeeze" the results appropriately.
Here is an example of using the GROUP to consolidate data. There are others. Search for GROUP and RECORDS, for instance.