I have input CSV file having multiple primary and secondary key value fields. I need to sort it on Primary and then secondary key with trailer record inserted after each primary key change. I am able to handle one primary key using simple OUTFIL and IFTRAIL functions.
Also, the date from input CSV file is not always in same format. It can be M/D/YYYY or MM/D/YYYY or M/DD/YYYY as well as MM/DD/YYYY. I am currently handling varying position of '/' for possible positions they would appear. Is there any function to handle all four formats of date and give output in MMDDYYYY?
987654321$$$$1000023457100108272021 NAME1 -> when 'False' indicator, one character after date and before Name field is blank.
987654321$$$$1000023456100208022021 NAME2
T987654321$$$$$$$000000002$$$$$$$$000000002003 -> Trailer record for same Primary key and space value for indicator - starting with 'T',Primary Key,spaces,number of records,spaces,sum of amount
987654320$$$$1000023454200208272021 NAME4
987654320$$$$1000023453200308272021 NAME5
T987654320$$$$$$$000000002$$$$$$$$000000004005 -> Trailer record for same Primary key and space value for indicator - starting with 'T',Primary Key,spaces,number of records,spaces,sum of amount
987654320$$$$1000023455200110272021VNAME3 -> when 'True ' indicator, one character after date and before Name field is 'V'.
T987654320$$$$$$$000000001$$$$$$$$000000002003V -> Trailer record for same Primary key and 'V' value for indicator - starting with 'T',Primary Key,spaces,number of records,spaces,sum of amount,indiactor 'V'
2) if the data fields are aligned (to the same positions within records) then it doesn't matter: are they comma-separated, or not. It's all is absolutely not clear from your samples.
3) Major question: did you try to do something by yourself? Where are the results of your attempts?
Please use code tags going forward. Please use SEARCH button to find similar problems discussed and solution provided. Come up with what you tried and one may help further to get it fixed.
ibmmainframes.com/about59559.html
Joined: 15 Aug 2015 Posts: 607 Location: Bamberg, Germany
How comes that,
Code:
987654320$$$$1000023455200110272021VNAME3 -> when 'True ' indicator, one character after date and before Name field is 'V'.
T987654320$$$$$$$000000001$$$$$$$$000000002003V -> Trailer record for same Primary key and 'V' value for indicator - starting with 'T',Primary Key,spaces,number of records,spaces,sum of amount,indiactor 'V'
an input of 2001 (amount) gives a sum of 2003 for just one record?
Besides the funny part of it, the sample input can be processed with less than 20 lines of code to achieve the desired result.
SORT JCL -
Sort step1 - All IFTHEN-INOUT sortcard lines are not mentioned. only first line for 01 is mentioned. For 02 thru 09, similar sort card lines are present. Also in the OUTREC, time field is omitted as well as expanded date is handled
Joined: 15 Aug 2015 Posts: 607 Location: Bamberg, Germany
prashantshinde wrote:
SORT JCL -
Sort step1 - All IFTHEN-INOUT sortcard lines are not mentioned. only first line for 01 is mentioned. For 02 thru 09, similar sort card lines are present. Also in the OUTREC, time field is omitted as well as expanded date is handled
Learn about PARSE and CHANGE from DFSORT Manuals. FINDREP/IFTRAIL is NOT needed here at all. Also two separate DSN that have to be merged, NOT needed. Save the Planet, avoid unnecessary CPU cycles!
Also your amount fields seem to not have negatives, right?
You need to LEARN all these tricks separately, and by yourself, to be able to do your own job. Otherwise it looks like a homeless beggar on a street.
If you don't know how to do each specific operation, then ask about it. Requesting your whole job to be done by others from start to finish - it is unashamed, and frustrating.
I would simply finish this task by writing up COBOL program which would be easy to maintain for you and others at your site for any future changes in these rules.
Joined: 15 Aug 2015 Posts: 607 Location: Bamberg, Germany
Rohit Umarjikar wrote:
I would simply finish this task by writing up COBOL program which would be easy to maintain for you and others at your site for any future changes in these rules.
It requires, as I have written, less than 20 lines in DFSORT. No need to write a program for that.
I tried PARSE to format CSV records. It removed comma correctly but with different date formats, how can I get one digit day or month converted to two digit?
Input -
I have used ':' from time field as when date is in MM/DD/YYYY format there is no space left between date and time.
Secondly, I tried using SECTIONS to control break after change in Primary Key. I gave Trailer record with SUM of counts/amounts but results are not as expected. It produced Trailer record after every change in Primary key. Not like one trailer record for all primary key records from the entire file.
SORT
I tried PARSE to format CSV records. It removed comma correctly but with different date formats, how can I get one digit day or month converted to two digit?
Input -
I have used ':' from time field as when date is in MM/DD/YYYY format there is no space left between date and time.
Secondly, I tried using SECTIONS to control break after change in Primary Key. I gave Trailer record with SUM of counts/amounts but results are not as expected. It produced Trailer record after every change in Primary key. Not like one trailer record for all primary key records from the entire file.
SORT
I would simply finish this task by writing up COBOL program which would be easy to maintain for you and others at your site for any future changes in these rules.
It requires, as I have written, less than 20 lines in DFSORT. No need to write a program for that.
I have already shared the link to use via DFSORT which has similar problem and the approach can be grabbed with little twists. My point was it is sometimes easier to save time and maintain quite easily if one writes the program considering future changes and skills people possesses. Let us not argue on this to divert from the original post but thanks for the views.
Thank you Joerg
Date formatting is resolved now! Using sections, I am able to get Trailer record after each block of primary key and value in column 42 (originally False or True value). The only problem remained is I am getting two different files based on value in 42nd byte. So, in one file different primary key records are present but they all have same value on 42nd byte. Now while merging the files, records of same primary key from both files should come together.
Input File -
I am thinking of adding sequence number based on primary key value and then combine it together based on the same sequence number. Is there any other way to solve this issue? Also, I tried to above SORT without having two output files but not able to do that.
.jpg)
