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.
COL11 COL12
-----------
012422 ABC,DEF <<-- Count for all the records is coming but, the COL12 value (PQR) for 3rd identical record is missing. Can we make it dynamic, so that it can handle around 10 identical records.
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
Code:
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 if all other COL's (COL1 to COL10) are identical.
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.
The above is very easy and can be done in one step.
INREC to prepare an additional field for the "outsort".
SORT on the "outsort" first, then the rest of the data to the amount.
Use OUTFIL with NODETAIL, with SECTIONS, with two sets of description for your two different control breaks. Use TOT to get the total.
That leaves the "concatenation" of COL 12. Work on the above whilst getting 100% clear on what you want for the "concatenation".
You now, "of course", want to "consolidate" the COL 12 of up to 10 records?
You haven't given much of an example of what might genuinely exist in the existing 50 bytes, where you say probably 1-25 bytes are used.
Can there be "embedded" blanks? Leading blanks? Or just trailing blanks?
Code:
A, B, C, D, E, A
A, B, C, D, E
With the former, you know what each record had, and you know that the total was consolidated from six records. With the latter, you have no clue either how many records, or what their original values were, other than the first element. Is that what you really want?
What about a COL 12 which is blank? How do you want that to be?
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
Looking at -3nadh's code, it is only dealing with one instance of COL 12 for multiple records. If you understood the Control Cards you would know that, and not ask about what to change for that.
Bear in mind that unless your data already contains the entire groups (ie when sorted, no additional records will be added to a group, or to put it another ways, no groups are "consolidated" through a sort taking place) then this technique requires an additional SORT, which your client or your company are paying for the resources for.
Here are the COL12 looks like
Length - 50 bytes
It can never be blank.
It can never have leading spaces, but can have embedded and/or trailing spaces. Trailing spaces need to be truncated.
Similiar COL12 values need not to consider, but their corresponding COL11 should be summed up.
Quote:
Code:
A, B, C, D, E, A
A, B, C, D, E
Yes, COL11 values for the 6 records should be summed up, but the COL12 values should not be repeated.
Hi Bill,
I'm able to "outsort" with the control card provided by trinadh. And the Total and sub-totals are also working fine with the sort provided by trinadh.
With INREC, prepare a field appended to your FB which contains D, G, J, M for your four "outsorted" values respectively and which otherwise contains X.
The reason for the "gaps" in the letters is to allow for easy amendments.
Then SORT on the new field and then the rest of the key that you want, so that you get your "outsort" and then within that outsort you get the required order.
Then use OUTFIL with SECTIONS and TRAILER3.
Code:
OUTFIL REMOVECC,NODETAIL,
SECTIONS=(p,m, start and length of your key for the TOTAL
TRAILER3=(c:C'TEXT YOU WANT',TOT=(p,m,ZD),/),
p,m, start and length of your sub-total
TRAILER3=(p,m, start and length of data before sub-tot
TOT=(p,m,ZD),
p,m start and length of data before sub-tot))
I've not even really tried to get the syntax right, you've got to do some work yourself.
Then you just need the extended field for the summed values, and the "consolidation", but get the above working first.
Hi Bill,
Below are the 2 pieces of codes I have amended to the outsort using INREC in a single step.
This code returns sub totals for each row. But the actual row reflects empty, Actual data is missing but the sub totals for each totals are coming in order.
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
You need two "totals" for the tasks. One is when the first three columns change (from memory) and here you want just a total followed by a blank line.
The second is that you want a summary value for the first 10 columns and you want that total to be "embedded" in the line with the 10 columns and the to-be-done-yet Column 12.
That, or close to it, on its own should give the summary line for the details.
They can be combined, something like this:
Code:
OUTFIL REMOVECC,NODETAIL,
SECTIONS=(1,length-of-first-three-columns-on-output,
TRAILER3=(122:C'TOTAL : ',TOT=(122,6,ZD),/),
1,121,
TRAILER3=(1,121,TOT=(122,6,ZD),128,length of remainder of line))
Remember you want the 6,ZD field to be "longer" to allow for the TOT. That plus the "consolidation" of COL 12 will be next, once you get the OUTFIL working and show all the Control Cards you have so far.
Again, I have not run the above myself, so you fix the bits that go wrong, rather than complain.
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
Are you saying this is some type of problem? If yes, be clear about it. Look at the size of the thread. This should not take so much effort.
You didn't previously mention you wanted to "total" if COL1-10 were equal AND COL12 was equal. You specified completely different processing for COL12.
Is this a change?
When I wrote my code, I used an EDIT for the TOT there. Have you tried that?
Remember, last thing you said was you wanted the (up to) 10 values for COL12 consolidated on the one line, which will be an extension of the line by 450 bytes.
Also, again, remember that you want an extra position for COL11, so that the total is less likely to overflow. The start position of COL12 (consolidated or otherwise) will have to change by one to be able to do that.
COL11 should be summed up if COL1 to COL10 are equal, Before computing the totals, we need to pair up all the different COL12 values. With the above sort card, Totals can be computed if COL1 to COL10 and COL12 values are equal.
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
Presumably you are sorting on COL12 then, which you didn't mention.
Presumably also you no longer need the "consolidation" of COL12?
That's good.
So you just need to extend the COL11 for the TOT so it doesn't overflow so easily. I'd append C'0',startCOL11,lengthCOL11 to the end of the record and TOT on that.
I'm still working on consolidation of COL12 values, which is the last step in the requirement.
On the other hand, I have just sent the sample reports to users and requested their feedback on the output file (this does not cover consolidation of COL12). I'm waiting for their response on this.