Sorting within a RECORD of a file

[Keith]

A file has record(s) ; within a record are 8 group(s) of fields; each group begins with a date. I need to SORT the groups by the date from Oldest to Newest date, then output the RECORD with the SORTED ORDER.
I am thinking that I should go with a SORTUP function; but this is new territory for me.
Anyone tried anything close to this?

[jdmiceli]

You could use a Sort Stage, but I would think it is much easier to run a Before Job subroutine -> ExecSH and sort it at the OS level. It appears you are running Windows, but your installation should have included some Unix functionality. Use a sort command on the file, something like this:

Code: Select all

sort -t\| +0 -n filename > newfilename

The -t: specifies the delimiter (I assumed a pipe, hence the escape slash in front of it, for the example since you are dealing with datetimes), the +0 indicates the first field of each row and the -n says to do a numerid sort instead of lexical (dictionary sort -d).

This will sort things in ascending order. To reverse it add a -r after the -n. That should work off the top of my head. Your mileage may vary.

Hope that helps!

[rep]

You could use a Sort Stage

Datastage TX?

[jdmiceli]

Sorry - didn't notice the TX. I am thinking Server Edition.

Does TX have the ability to run scripts against a file? If so, do you have any control when it would run? One thing you could do is make it part of the scheduling process prior to processing the file(s) with TX. Use the same sort command from a shell script that is called by your scheduler of choice and then have your TX job process the properly ordered file.

I've never seen TX so I'm just throwing stuff against the wall to see if it sticks...

Good Luck!

[rep]

A file has record(s) ; within a record are 8 group(s) of fields; each group begins with a date. I need to SORT the groups by the date from Oldest to Newest date, then output the RECORD with the SORTED ORDER.
I am thinking that I should go with a SORTUP function; but this is new territory for me. :idea:
Anyone tried anything close to this? :)

I believe what you're going to have to do is build the type tree as a file that contains records, that each contain 8 iterations of the same group, each accounting for one of the eight groups that starts with a date. I don't believe you can sort separate groups of different fields, but don't quote me on that...unless I'm right.


Like, you say you have (using only 3 groups to save me some typing):


File
..Record(s)
....Group1
........Date field1
........Text field1
....Group2
.......Date field2
.......Text Field2
....Group3
.......Date field3
.......Text field3

And you want the output to build each record with the 3 groups sorted by date, meaning if

Date1:group1 = 1/3/2008
Date2:group2 = 1/1/2008
date3:group3 = 1/2/2008


then build the output record...

group2 - 1/1/2008
group3 - 1/2/2008
group1 - 1/3/2008

Correct?


If that be the case, you most likley want to build the type tree like:

File
...Record(s)
......Group (8:8)
..........Date field
..........Text field

Then Group=SORTUP(Date field:Group:Record)

I believe that will work, but you may want to build a simply type tree and map to confirm first. Are the groups all the same lenght/fields?


Also an fyi, from what my last boss preached, Datastage TX has it's upsides, and while it can sort, it was not built for it. We would force mainframe jobs to pre-sort files before we mapped them, because they do it so much more efficiently, and Datastage will suck up resources.

[rep]

Sorry - didn't notice the TX.

The eagerness to share your obvious experience far exceeds the insignificance cost it bore to show such willingness and should harbor no embarrassment my friend.


I honestly did not know you could sort via DOS batch files (the select of OS is Windows as per the original) until you mentioned it, but TX does allow for scheduling and executing batch files, while passing variables via the map.

[jdmiceli]

Actually the example I gave was in Unix format even though the base OS for Keith is Windoze. I was thinking that he had the tools that are on the install media for our versions. However, you could put Cygwin on the server and I believe that would give the functionality I described. Windoze has a SORT functionality, but it is column based (pronounced character) not delimited columns.

I probably wasn't much help this time unfortunately...

Hasta la toodles!

[Keith]

jdmiceli, many thanks for your suggestion on the sort.

REP : your description in the type tree really is 'bad to the bone' in other words ; 'appears to fit perfectly'. :D
Yes ! all the fields in each group have the same length. The design in the type tree is going to be pretty significant.

I am not exactly sure how the link is made on the key field ' Date field '

File
...Record(s)
......Group (8:8)
..........Date field
..........Text field


to the ' Date Field' in

File
..Record(s)
....Group1
........Date field1
........Text field1
....Group2
.......Date field2
.......Text Field2
....Group3
.......Date field3 .......Text field3


But I will give it my best shot !

thanks

[Keith]

I dont think there is a 'Magic Bullet' or function that can sort individual groups within a record. SORTUP appears to use one key [defined in the type tree ] and appears to ( ONLY ) sort records within a file.

The only way [ I think ] this is going to work, is :

1. Will have to split [from the master file] the individual groups into individual records, each having a loan number [ in order to sort them]

2. Sort the records [using one key Loan#+date] [each group has a date]

3. Then read the file [ that was sorted] ...at the same time....

4. Update the master file using a LOOKUP [loan number = key]

may the force be with me

[jvmerc]

Using sortup or sortdown should sort a group within a record should work if you want to change

x2,y,z,20071201:20071130:20061130:20080109:20070101,a3,b,c
x1,y,z,20071201:20071130:20061130:20080109:20070101,a2,b,c
x5,y,z,20071201:20071130:20061130:20080109:20070101,a0,b,c
x4,y,z,20071201:20071130:20061130:20080109:20070101,a1,b,c

to

x2,y,z,20061130:20070101:20071130:20071201:20080109,a3,b,c
x1,y,z,20061130:20070101:20071130:20071201:20080109,a2,b,c
x5,y,z,20061130:20070101:20071130:20071201:20080109,a0,b,c
x4,y,z,20061130:20070101:20071130:20071201:20080109,a1,b,c

Here is a tree for you...

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE TTMAKER SYSTEM "ttmaker60.dtd">
<?ANALYZE?><TTMAKER Version="6.0"><OPENTREE Filename="C:\appsprod\apps\examples\sort_group\TypeTree1.mtt"><GROUP SimpleTypeName="record" CategoryOrGroupParent="ROOT" OrderSubtypes="ASCENDING"><TypeSyntax><TERMINATOR><Literal IgnoreCase="NO"><Western CharSet="NATIVE"/>
<LiteralValue><nl></LiteralValue>
</Literal>
</TERMINATOR>
</TypeSyntax>
<Sequence partition="NO"><Implicit><Delimited location="INFIX"><DelimiterLiteral><Western CharSet="NATIVE"/>
<LiteralValue>,</LiteralValue>
</DelimiterLiteral>
</Delimited>
</Implicit>
<SequenceComponent><RelativeTypeName>txt1 elements</RelativeTypeName>
<Range Min="1" Max="1"/>
</SequenceComponent>
<SequenceComponent><RelativeTypeName>txt2 elements</RelativeTypeName>
<Range Min="1" Max="1"/>
</SequenceComponent>
<SequenceComponent><RelativeTypeName>txt3 elements</RelativeTypeName>
<Range Min="1" Max="1"/>
</SequenceComponent>
<SequenceComponent><RelativeTypeName>date_group</RelativeTypeName>
<Range Min="1" Max="1"/>
</SequenceComponent>
<SequenceComponent><RelativeTypeName>txt4 elements</RelativeTypeName>
<Range Min="1" Max="1"/>
</SequenceComponent>
<SequenceComponent><RelativeTypeName>txt5 elements</RelativeTypeName>
<Range Min="1" Max="1"/>
</SequenceComponent>
<SequenceComponent><RelativeTypeName>txt6 elements</RelativeTypeName>
<Range Min="1" Max="1"/>
</SequenceComponent>
</Sequence>
</GROUP>
</OPENTREE>
</TTMAKER>


here is the map

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE MMS SYSTEM "mms.dtd">
<MMS mapcount="2" path="C:\appsprod\apps\examples\sort_group\MapSourceFile1.mms">

<Map name="sort_group_1" InputCount="1" OutputCount="1" AuditCount="0" RemarkCount="0">
<MapSettings>
<MapAudit Switch="ON">
<BurstAudit>
<Data.OnBurstError SizeValidation="WrongSize"/>
<Execution.OnBurstError/>
</BurstAudit>
<SummaryAudit execution="OnError"/>
<SettingsAudit data="OnError" map="OnError"/>
<AuditLocation.File>
<Directory.Map/>
<FileName.Default action="Create"/>
</AuditLocation.File>
</MapAudit>
<MapTrace Switch="ON">
<ContentTrace.File Switch="ON">
<TraceLocation.File>
<Directory.Map/>
<FileName.Default/>
</TraceLocation.File>
<InputContentTrace.ALL/>
<RulesTrace.ALL/>
<SummaryContentTrace.ON/>
</ContentTrace.File>
</MapTrace>
<WorkSpace.File PageSize="64" PageCount="8">
<Directory.Map/>
<WorkFilePrefix.MapName action="Delete"/>
</WorkSpace.File>
<Century.Current/>
<Validation.Standard/>
<Retry Switch="OFF" MaxAttempts="0" Interval="0"/>
<Warnings.Every action="warn"/>
</MapSettings>

<Input>
<Schema cardnumber="1"
cardname="in1"
typetree="TypeTree1.mtt"
type="file ROOT"/>
<SourceRule>
<FetchAs>
<Integral workarea="Create" fetchunit="S"/>
</FetchAs>
<GET>
<FILE_Source>
<FilePath>to_sort.txt</FilePath>
<FILE_Source.Transaction OnSuccess="Keep" OnFailure="Rollback" Scope="Map"/>
<Retry Switch="OFF" MaxAttempts="0" Interval="0"/>
<DocumentVerification>Never</DocumentVerification>
</FILE_Source>
</GET>
<Backup Switch="OFF" When="Always">
<BackupLocation.File>
<Directory.Map/>
<FileName.Custom action="Create"></FileName.Custom>
</BackupLocation.File>
</Backup>
</SourceRule>
</Input>

<Output>
<Schema cardnumber="1"
cardname="out1"
typetree="TypeTree1.mtt"
type="file ROOT"/>
<TargetRule>
<PUT>
<FILE_Target>
<FilePath>sorted.txt</FilePath>
<FILE_Target.Transaction OnSuccess="Create" OnFailure="Rollback" Scope="Map"/>
<Retry Switch="OFF" MaxAttempts="0" Interval="0"/>
<DocumentVerification>Never</DocumentVerification>
</FILE_Target>
</PUT>
<Backup Switch="OFF" When="Always">
<BackupLocation.File>
<Directory.Map/>
<FileName.Custom action="Create"></FileName.Custom>
</BackupLocation.File>
</Backup>
</TargetRule>
<MapRule rulenumber="1">
<objectset>record:out1</objectset>
<objectrule>=copy_record(record:in1)</objectrule>
</MapRule>
</Output>
</Map>

<Map name="copy_record" InputCount="1" OutputCount="1" AuditCount="0" RemarkCount="0">
<MapSettings>
<MapAudit Switch="ON">
<BurstAudit>
<Data.OnBurstError SizeValidation="WrongSize"/>
<Execution.OnBurstError/>
</BurstAudit>
<SummaryAudit execution="OnError"/>
<SettingsAudit data="OnError" map="OnError"/>
<AuditLocation.File>
<Directory.Map/>
<FileName.Default action="Create"/>
</AuditLocation.File>
</MapAudit>
<MapTrace Switch="ON">
<ContentTrace.File Switch="ON">
<TraceLocation.File>
<Directory.Map/>
<FileName.Default/>
</TraceLocation.File>
<InputContentTrace.ALL/>
<RulesTrace.ALL/>
<SummaryContentTrace.ON/>
</ContentTrace.File>
</MapTrace>
<WorkSpace.File PageSize="64" PageCount="8">
<Directory.Map/>
<WorkFilePrefix.MapName action="Delete"/>
</WorkSpace.File>
<Century.Current/>
<Validation.Standard/>
<Retry Switch="OFF" MaxAttempts="0" Interval="0"/>
<Warnings.Every action="warn"/>
</MapSettings>

<Input>
<Schema cardnumber="1"
cardname="In1"
typetree="TypeTree1.mtt"
type="record ROOT"/>
<SourceRule>
<FetchAs>
<Integral workarea="Create" fetchunit="S"/>
</FetchAs>
<GET>
<FILE_Source>
<FILE_Source.Transaction OnSuccess="Keep" OnFailure="Rollback" Scope="Map"/>
<Retry Switch="OFF" MaxAttempts="0" Interval="0"/>
<DocumentVerification>Never</DocumentVerification>
</FILE_Source>
</GET>
<Backup Switch="OFF" When="Always">
<BackupLocation.File>
<Directory.Map/>
<FileName.Custom action="Create"></FileName.Custom>
</BackupLocation.File>
</Backup>
</SourceRule>
</Input>

<Output>
<Schema cardnumber="1"
cardname="Out"
typetree="TypeTree1.mtt"
type="record ROOT"/>
<TargetRule>
<PUT>
<FILE_Target>
<FILE_Target.Transaction OnSuccess="Create" OnFailure="Rollback" Scope="Map"/>
<Retry Switch="OFF" MaxAttempts="0" Interval="0"/>
<DocumentVerification>Never</DocumentVerification>
</FILE_Target>
</PUT>
<Backup Switch="OFF" When="Always">
<BackupLocation.File>
<Directory.Map/>
<FileName.Custom action="Create"></FileName.Custom>
</BackupLocation.File>
</Backup>
</TargetRule>
<MapRule rulenumber="1">
<objectset>txt1 elements:Out</objectset>
<objectrule>=txt1 elements:In1</objectrule>
</MapRule>
<MapRule rulenumber="2">
<objectset>txt2 elements:Out</objectset>
<objectrule>=txt2 elements:In1</objectrule>
</MapRule>
<MapRule rulenumber="3">
<objectset>txt3 elements:Out</objectset>
<objectrule>=txt3 elements:In1</objectrule>
</MapRule>
<MapRule rulenumber="4">
<objectset>txt4 elements:Out</objectset>
<objectrule>=txt4 elements:In1</objectrule>
</MapRule>
<MapRule rulenumber="5">
<objectset>txt5 elements:Out</objectset>
<objectrule>=txt5 elements:In1</objectrule>
</MapRule>
<MapRule rulenumber="6">
<objectset>txt6 elements:Out</objectset>
<objectrule>=txt6 elements:In1</objectrule>
</MapRule>
<MapRule rulenumber="7">
<objectset>date elements:date_group:Out</objectset>
<objectrule>=sortup( date elements:.:In1 )</objectrule>
</MapRule>
</Output>
</Map>

</MMS>

[Keith]

JVMERC,

many thanks, for the code / effort,

working on it,

Keith

[jvmerc]

Let us know how it goes.... My example was pretty simple.