ICETOOL to get maximum, minimum value records

ICETOOL SELECT FIRST / LAST to get largest / smallest values within a set

Suppose we have a file with Branch name, Account number, Transaction amounts. like,

Branch  Account-num  Transaction-Amount
----+----1----+----2----+----3----+----4----+
Chennai   10011     00523
Chennai   10011     00010
Bangalore 10011     00056
Bangalore 10011     00670
Chennai   10012     00200
Chennai   10012     00235
Chennai   10012     00750
Bangalore 10012     00750
Bangalore 10012     00034

and, we want to get the maximum value transactions for every account. like,

Branch  Account-num  Transaction-Amount
----+----1----+----2----+----3----+----4----+
Bangalore 10011     00670
Bangalore 10012     00750
Chennai   10011     00523
Chennai   10012     00750

this can be done using ICETOOL as,

//TOOLIN DD *
 SELECT FROM(INFILE) TO(OUTFILE) ON(BRANCH) ON(ACCOUNT) -
 FIRST USING(CTL1)
/*
//CTL1CNTL DD *
 SORT FIELDS=(BRANCH,A,ACCOUNT,A,TRANS-AMT,D)
/*
//SYMNAMES DD *
 BRANCH,1,10,CH
 ACCOUNT,11,5,ZD
 TRANS-AMT,21,5,ZD
/*

In the above ICETOOL step, we are sorting the file in the order of Branch name (Ascending), Account number (Ascending), Transaction amount (Descending).

(If you want to select the smallest/minimum value transactions only, then sort the Transaction amount in Ascending order).

Then for each Branch and Account, we are selecting the first record only.

what are SYMNAMEs?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.