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.
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
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.
CONTRIBUTION INCIRCLE RECEIVED ALTER MT N/A 6899 20130306000150ALTER
CONTRIBUTION INCIRCLE RECEIVED ALTER PAN N/A 4664 20130306001265ALTER
CONTRIBUTION OUTCIRCLE SENT ALTER MT N/A 6899 20130306000150SMS
CONTRIBUTION OUTCIRCLE SENT ALTER PAN N/A 4664 20130306001265ALTER
EVALUATIONE INCIRCLE RECEIVED AUMT AEN N/A 4664 TP6 20130306000010T
EVALUATIONE INCIRCLE RECEIVED AUMT AEN 2430 N/A TP6 20130306000061A
EVALUATIONE INCIRCLE RECEIVED AUMT AEN 4664 4664 TP6 20130306002074A
EVALUATIONE INCIRCLE RECEIVED AUMT AEN 4664 4664 TP6 20130306000478T
EVALUATIONE OUTCIRCLE NOT SENT EVENT AEN 4664 N/A TP6 20130306000022E
EVALUATIONE OUTCIRCLE NOT SENT SCHEDULED AEN 2430 2430 TP6 20130306000009S
EVALUATIONE OUTCIRCLE SENT AUMT AEN 4664 4664 20130306000002A
EVALUATIONE OUTCIRCLE SENT AUMT AEN 4664 4664 TP6 20130306000461A
EVALUATIONE OUTCIRCLE SENT SCHEDULED AEN 4664 4664 TP6 20130306000702S
GENERIC LOG MSGSERV OUTCIRCLE CEBODLWR N/A CEBODLWR20130306000173LOGGING EXECUTION STAT
GENERIC LOG MSGSERV OUTCIRCLE CEBODLWR N/A CEBODLWR20130307000005LOGGING EXECUTION STAT
POPULAR INCIRCLE RECEIVED FOCUS PFC N/A 4664 20130305000006FOCUS
POPULAR INCIRCLE RECEIVED FOCUS PFC N/A 4664 20130306000015FOCUS
POPULAR OUTCIRCLE SENT ALTER PAN N/A 4664 20130306001266ALTER
POPULAR OUTCIRCLE SENT FOCUS PFN N/A 7826 20130306000049FOCUS
POPULAR OUTCIRCLE SENT FOCUS PFN N/A 7826 20130306000022RWF
TOSS OUTCIRCLE I AUMT 4700 20130305084208N/A
TOSS OUTCIRCLE I AUMT 4700 20130306012519N/A
TOSS OUTCIRCLE N AUMT 3543 N/A CH 0000000020130305999999VSK.QATLAS.MAINT.TOPR.CONS.PUBSUB
TOSS OUTCIRCLE N AUMT 3543 N/A CH 9999999920130305999999TP6.RWF.OUT
TOSS OUTCIRCLE N AUST 3543 N/A CH 9999999920130305019553TP6.RWF.OUT
TOSS OUTCIRCLE N AUST 3543 N/A CH 9999999920130306003740TP6.RWF.OUT
TOSS OUTCIRCLE N EVBR 2430 2430 CH 20130306045274N/A
TOSS OUTCIRCLE N EVBR 2430 2430 CH 20130307004418N/A
TOSS OUTCIRCLE Y EVBR N/A 4664 4664 CH TOPRMOBL20130305000012TSS.G9999.TP6.EVENTS.P70Q
TOSS OUTCIRCLE Y EVBR N/A 4664 4664 CH TOPRMOBL20130305000012TSS.G9999.TP6.EVENTS.XFER.P70Q
Output File looks like:
Code:
EVALUATIONE INCIRCLE RECEIVED AUMT AEN N/A 4664 TP6 20130306000010T
EVALUATIONE INCIRCLE RECEIVED AUMT AEN 2430 N/A TP6 20130306000061A
EVALUATIONE INCIRCLE RECEIVED AUMT AEN 4664 4664 TP6 20130306002552A,T
TOTAL : 2623
EVALUATIONE OUTCIRCLE NOT SENT EVENT AEN 4664 N/A TP6 20130306000022E
EVALUATIONE OUTCIRCLE NOT SENT SCHEDULED AEN 2430 2430 TP6 20130306000009S
TOTAL : 31
EVALUATIONE OUTCIRCLE SENT AUMT AEN 4664 4664 20130306000002A
EVALUATIONE OUTCIRCLE SENT AUMT AEN 4664 4664 TP6 20130306000461A
EVALUATIONE OUTCIRCLE SENT SCHEDULED AEN 4664 4664 TP6 20130306000702S
TOTAL : 1165
POPULAR INCIRCLE RECEIVED FOCUS PFC N/A 4664 20130305000006FOCUS
POPULAR INCIRCLE RECEIVED FOCUS PFC N/A 4664 20130306000015FOCUS
TOTAL : 21
POPULAR OUTCIRCLE SENT ALTER PAN N/A 4664 20130306001266ALTER
POPULAR OUTCIRCLE SENT FOCUS PFN N/A 7826 20130306000071FOCUS,RWF
TOTAL : 1337
CONTRIBUTION INCIRCLE RECEIVED ALTER MT N/A 6899 20130306000150ALTER
CONTRIBUTION INCIRCLE RECEIVED ALTER PAN N/A 4664 20130306001265ALTER
TOTAL : 1415
CONTRIBUTION OUTCIRCLE SENT ALTER MT N/A 6899 20130306000150SMS
CONTRIBUTION OUTCIRCLE SENT ALTER PAN N/A 4664 20130306001265ALTER
TOTAL : 1415
TOSS OUTCIRCLE I AUMT 4700 20130305084208N/A
TOSS OUTCIRCLE I AUMT 4700 20130306012519N/A
TOTAL : 96727
TOSS OUTCIRCLE N AUMT 3543 N/A CH 0000000020130305199998VSK.QATLAS.MAINT.TOPR.CONS.PUBSUB,TP6.RWF.OUT
TOSS OUTCIRCLE N AUST 3543 N/A CH 9999999920130305019553TP6.RWF.OUT
TOSS OUTCIRCLE N AUST 3543 N/A CH 9999999920130306003740TP6.RWF.OUT
TOSS OUTCIRCLE N EVBR 2430 2430 CH 20130306045274N/A
TOSS OUTCIRCLE N EVBR 2430 2430 CH 20130307004418N/A
TOTAL : 272983
TOSS OUTCIRCLE Y EVBR N/A 4664 4664 CH TOPRMOBL20130305000024TSS.G9999.TP6.EVENTS.P70Q,TSS.G9999.TP6.EVENTS.XFER.P70Q
TOTAL : 24
GENERIC LOG MSGSERV OUTCIRCLE CEBODLWR N/A CEBODLWR20130306000173LOGGING EXECUTION STAT
GENERIC LOG MSGSERV OUTCIRCLE CEBODLWR N/A CEBODLWR20130307000005LOGGING EXECUTION STAT
TOTAL : 178
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
Code:
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.
Code:
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.
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
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.
Quote:
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.
EVALUATIONE INCIRCLE RECEIVED AUMT AEN N/A 4664 TP6 20130306000010T
EVALUATIONE INCIRCLE RECEIVED AUMT AEN 2430 N/A TP6 20130306000061A
EVALUATIONE INCIRCLE RECEIVED AUMT AEN 4664 4664 TP6 20130306002552A,T
TOTAL :0002623
EVALUATIONE OUTCIRCLE NOT SENT EVENT AEN 4664 N/A TP6 20130306000022E
EVALUATIONE OUTCIRCLE NOT SENT SCHEDULED AEN 2430 2430 TP6 20130306000009S
TOTAL :0000031
EVALUATIONE OUTCIRCLE SENT AUMT AEN 4664 4664 20130306000002A
EVALUATIONE OUTCIRCLE SENT AUMT AEN 4664 4664 TP6 20130306000461A
EVALUATIONE OUTCIRCLE SENT SCHEDULED AEN 4664 4664 TP6 20130306000702S
TOTAL :0001165
POPULAR INCIRCLE RECEIVED FOCUS PFC N/A 4664 20130305000006FOCUS
POPULAR INCIRCLE RECEIVED FOCUS PFC N/A 4664 20130306000015FOCUS
TOTAL :0000021
POPULAR OUTCIRCLE SENT ALTER PAN N/A 4664 20130306001266ALTER
POPULAR OUTCIRCLE SENT FOCUS PFN N/A 7826 20130306000071FOCUS,RWF
TOTAL :0001337
CONTRIBUTION INCIRCLE RECEIVED ALTER MT N/A 6899 20130306000150ALTER
CONTRIBUTION INCIRCLE RECEIVED ALTER PAN N/A 4664 20130306001265ALTER
TOTAL :0001415
CONTRIBUTION OUTCIRCLE SENT ALTER MT N/A 6899 20130306000150SMS
CONTRIBUTION OUTCIRCLE SENT ALTER PAN N/A 4664 20130306001265ALTER
TOTAL :0001415
TOSS OUTCIRCLE I AUMT 4700 20130305084208N/A
TOSS OUTCIRCLE I AUMT 4700 20130306012519N/A
TOTAL :0096727
TOSS OUTCIRCLE N AUMT 3543 N/A CH 0000000020130305999999VSK.QATLAS.MAINT.TOPR.CONS.PUBSUB
TOSS OUTCIRCLE N AUMT 3543 N/A CH 9999999920130305999999TP6.RWF.OUT
TOSS OUTCIRCLE N AUST 3543 N/A CH 9999999920130305019553TP6.RWF.OUT
TOSS OUTCIRCLE N AUST 3543 N/A CH 9999999920130306003740TP6.RWF.OUT
TOSS OUTCIRCLE N EVBR 2430 2430 CH 20130306045274N/A
TOSS OUTCIRCLE N EVBR 2430 2430 CH 20130307004418N/A
TOTAL :2072983
TOSS OUTCIRCLE Y EVBR N/A 4664 4664 CH TOPRMOBL20130305000024TSS.G9999.TP6.EVENTS.P70Q,TSS.G9999.TP6.EVENTS.XFER.P70Q
TOTAL :0000024
Joined: 07 Dec 2007 Posts: 2205 Location: San Jose
saiprasadh wrote:
Venkata Ravi,
I used DFSORT for your requirement. You can search for equivalent SYNCSORT command to achieve the result using SYNCSORT.
Saiprasadh,
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.
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
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.
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
venkata.ravi,
Did you just get tired of me asking questions, so decided to ignore any more? If so, you'd best go with -3nadh's three-step solution (I assume you don't feel inclined to work on Sai's solution either). If your data is not in the order required, you could always add another SORT step.
The requirement for the outsorting may dictate the solution for whole thing (do you want it "dynamic", or does it only change rarely so can be done by code updates?). If you are still not keen to answer that, then it makes no nevermind to me.
Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
How about asking around your place of work for the manuals? If your place is licensed then it has manuals. They can probably order more as well if the manuals are 'lost'.
Hi trinadh,
Your code is fantastic. The resultant file format looks good to me except 2 minor changes.
1. While concatenating COL12 values, we are totaling COL11 values. Here, there is a possibility for the COL11 gets overflow and the actul value gets truncated. I would like to increase COL11 from 6 digits to 7 digits.
Ex:
Input
Code:
COL11 COL12
-----------
999991 ABC
023123 DEF
Output
Code:
COL11 COL12
-----------
023114 ABC, DEF <<-- Here the value gets truncated
2. COL12 values are getting concatenated even though they are identical. In this case only COL11 values should be added together and to leave COL12 as it is.
Ex:
Input
Code:
COL11 COL12
-----------
001201 ABC
023123 ABC
024567 DEF
000023 DEF
000013 PQR
Output
Code:
COL11 COL12
-----------
024324 ABC
024603 DEF,PQR <<-- DEF should not repeat here
Hi Bill,
I am not tired with your questions.
The outsorting order is fixed. Trinadh has covered it in the first step. It's fine. As Sai's solution is based on DFSORT, I couldn't test it with my inputs.
Joined: 07 Dec 2007 Posts: 2205 Location: San Jose
bodatrinadh wrote:
Hi Skolusu,
I saw the comments which you replied to saiprasad...
But the input given by TS is a sorted one..And i assumed the same.
Yes, You are right. If the data is jumbled, my sort code won't work. It needs little modification.
bodatrinadh,
Apart from the SORTED vs UNSORTED output, there is one more issue with the job. As OP wanted the sort sequence to be "Required order of COL1 is ** EVALUATIONE, POPULAR, CONTRIBUTION, TOSS and others if any ** " You are only considering the first byte to determine the Sequence, if COL1 has the following data
Code:
E
EA
EB
EC
P1
P2
P3
....
You will have incorrect results. You need to validate all the 20 bytes and add the sorting indicator.
You are only considering E, P, C, T, G as the only valid alphabets that would get the sorting sequence. If you have data other than those values byte 178 would be a space and they would get sorted First before numbers 1,2,3,4,5
Your shop may have OPTION EQUALS as default, and you are getting the results in the right order you have , however if NOEQUALS is the default, then your test results will be unpredictable.
