SORT to add trailer record on change of key + Date



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

SORT to add trailer record on change of key + Date

Postby Prashant_2021 » Fri Aug 27, 2021 11:04 pm

My input file contains records having account number, time stamp, amount, an indicator etc. This file is probably taken from excel and sent to mainframe. Hence it has comma separated values. Along with it, the date value varies in different formats like M/D/YYYY or MM/D/YYYY or M/DD/YYYY or MM/DD/YYYY. Amount is having decimal point in it.
Primary Key - 1 -9 bytes, Secondary Key - 11-20 , Date - 22 -41, Indicator - 43-47,Amount - variable length upto 15 digits including decimal,Name - 50 characters
1 2 3 4 5 6
123456789012345678901234567890123456789012345678901234567890

987654321,1000023457,8/27/2021 12:00:00 AM,False,10.01,NAME1
987654321,1000023456,8/2/2021 12:00:00 AM,False,10.02,NAME2
987654320,1000023455,10/27/202112:00:00 AM,True ,20.01,NAME3
987654320,1000023454,8/27/2021 12:00:00 AM,False,20.02,NAME4
987654320,1000023453,8/27/2021 12:00:00 AM,False,20.03,NAME5

Desired output is -
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'

Value of key field is not known, neither how many different types of keys (and their records) would present on the file. But in output, all records of same primary key with False indicator grouped together followed by Trailer record for them and all records of same primary key with True indicator grouped together with separate Trailer record for them.

I am able to add trailer record (with indicator value True and False) in one file for one primary key. But cannot find solution for multiple primary keys. Also, date formatting is done based on different types of date format and hence change of '/' position but it required multiple (27 IFTHEN conditions). Is there simple way to reformat different date formats to one standard date format?
Please advise.
Prashant_2021
 
Posts: 1
Joined: Fri Aug 27, 2021 8:15 pm
Has thanked: 0 time
Been thanked: 0 time

Re: SORT to add trailer record on change of key + Date

Postby sergeyken » Sat Aug 28, 2021 11:15 pm

Lesson #1: this type of data is known as CSV (Comma Separated Values)

Lesson #2: learn about PARSE parameters of SORT control statements

Lesson #3: learn about WHEN=GROUP parameters of SORT control statements

Lesson #4: learn to resolve the issues one by one, not all the problems together
Javas and Pythons come and go, but JCL and SORT stay forever.
User avatar
sergeyken
 
Posts: 408
Joined: Wed Jul 24, 2019 10:12 pm
Has thanked: 6 times
Been thanked: 40 times


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post