Loading

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?

Related Posts




blog comments powered by Disqus

There's 0 Comment So Far

Share your thoughts, leave a comment!